Posted by Ed Murphy on 05/03/07 22:16

Filips Benoit wrote:

> To return the recordsset I wanted I had to use 3 view OR IS THERE A BETTER
> SOLUTION ( 1 view, 1 SP ) ?????????
[snip]
> View = 'a1'
>
> SELECT ORD_ID, ORD_CLIENT_CODE, ORD_CREATION_DATE,
> ORD_REQUESTED_QUANTITY
> FROM dbo. [ ORDER ]
> WHERE (ORD_PHTI_ID = 17) AND (ORD_REQUESTED_DELIVERY_DATE >
> '2006-01-01')
>
>
>
> View = 'a2'
>
> SELECT dbo.INVOICE_ORDER_PHASE_MAP.INVOPM_OP_ORD_ID,
> dbo.INVOICE.INV_CODE, dbo.INVOICE.INV_INVT_ID
> FROM dbo.INVOICE INNER JOIN
> dbo.INVOICE_ORDER_PHASE_MAP ON dbo.INVOICE.INV_ID =
> dbo.INVOICE_ORDER_PHASE_MAP.INVOPM_INV_ID
> WHERE (dbo.INVOICE.INV_INVT_ID = 4)
>
>
>
> View = 'a3' >> Resultset
>
> SELECT dbo.a1.ORD_CLIENT_CODE, dbo.a1.ORD_CREATION_DATE,
> dbo.a1.ORD_REQUESTED_QUANTITY, dbo.a2.INV_CODE, dbo.a2.INV_INVT_ID
> FROM dbo.a1 LEFT OUTER JOIN
> dbo.a2 ON dbo.a1.ORD_ID = dbo.a2.INVOPM_OP_ORD_ID

Try this:

select o.ORD_CLIENT_CODE,
o.ORD_CREATION_DATE,
o.ORD_REQUESTED_QUANTITY,
i.INV_CODE,
i.INV_INVT_ID
from ORDER o
left join INVOICE_ORDER_PHASE_MAP io
on o.ORD_ID = io.INVOPM_OP_ORD_ID
left join INVOICE i
on io.INVOPM_INV_ID = i.INV_ID and i.INV_INVT_ID = 4
where o.ORD_PHTI_ID = 17
and o.ORD_REQUESTED_DELIVERY_DATE > {d '2006-01-01'}

[Back to original message]