sql-problem
am 03.05.2007 22:12:39 von Filips Benoit
Dear All,
To return the recordsset I wanted I had to use 3 view OR IS THERE A BETTER
SOLUTION ( 1 view, 1 SP ) ?????????
TABLE1= ORDER (ORD_ID, ORD_CLIENT_CODE, ORD_CREATION_DATE,
ORD_REQUESTED_QUANTITY, ORD_PHTI_ID ....)
TABLE2: INVOICE( INV_CODE, INV_INVT_ID, .....)
TABLE3: INVOICE_ORDER_PHASE_MAP( INVOPM_INV_ID,
INVOPM_OP_ORD_ID,INVOPM_OP_SEQ_NR ) mapping orders and invoices per phase
(seq_nr)
I want all order WHERE (ORD_PHTI_ID = 17) AND
(ORD_REQUESTED_DELIVERY_DATE > '2006-01-01')
and show the ORD_CLIENT_CODE, ORD_CREATION_DATE, ORD_REQUESTED_QUANTITY and
INV_CODE if the invoice is of type = 4 (Outgoing Invoice)
This are some rows of the resultset
a3 ORD_CLIENT_CODE ORD_CREATION_DATE ORD_REQUESTED_QUANTITY INV_CODE
INV_INVT_ID
PT_V06-0330_SV 12/07/2006 21 TMAL_IN_07_90222 4
PT_V06-0363_PT 22/08/2006 309 TMAL_IN_07_90223 4
PT_V06-0365_PT 24/08/2006 280 TMAL_IN_07_90223 4
PT_MAL_V06-0358_LaES 16/08/2006 592 TMAL_IN_07_90224 4
PT_V06-0497_FI 8/11/2006 60
PT_V07-0169_PL 16/03/2007 59
PT_V06-0655_ES 26/12/2006 54
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
THANKSĀ²,
Filip
Re: sql-problem
am 04.05.2007 00:16:26 von Ed Murphy
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'}
Re: sql-problem
am 04.05.2007 00:45:32 von Hugo Kornelis
On Thu, 03 May 2007 20:12:39 GMT, Filips Benoit wrote:
>Dear All,
>
>
>To return the recordsset I wanted I had to use 3 view OR IS THERE A BETTER
>SOLUTION ( 1 view, 1 SP ) ?????????
Hi Filips,
Below are some possibilities, but I couldn't test either of them. It's
much easier to post good answers if you use CREATE TABLE statements to
describe the table instead of just a column list, and INSERT statemenst
for the data instead of a printed table.
The easiest alternative is to just enclose the definitions of the first
two views in the third one:
SELECT a1.ORD_CLIENT_CODE, a1.ORD_CREATION_DATE,
a1.ORD_REQUESTED_QUANTITY, a2.INV_CODE, a2.INV_INVT_ID
FROM (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') AS a1
LEFT JOIN (SELECT iopm.INVOPM_OP_ORD_ID, i.INV_CODE, i.INV_INVT_ID
FROM dbo.INVOICE AS i
INNER JOIN dbo.INVOICE_ORDER_PHASE_MAP AS iopm
ON i.INV_ID = iopm.INVOPM_INV_ID
WHERE i.INV_INVT_ID = 4) AS a2
ON a1.ORD_ID = a2.INVOPM_OP_ORD_ID;
The second alternative is a quite straight derivation from the one
above. The unusual order of the joins (clarified with parentheses) makes
it a bit hard to grasp
SELECT o.ORD_CLIENT_CODE, o.ORD_CREATION_DATE,
o.ORD_REQUESTED_QUANTITY, a2.INV_CODE, a2.INV_INVT_ID
FROM dbo.ORDER AS o
LEFT JOIN ( dbo.INVOICE_ORDER_PHASE_MAP AS iopm
INNER JOIN dbo.INVOICE AS i
ON i.INV_ID = iopm.INVOPM_INV_ID)
ON iopm.INVOPM_OP_ORD_ID = o.ORD_ID
AND i.INV_INVT_ID = 4
WHERE o.ORD_PHTI_ID = 17
AND o.ORD_REQUESTED_DELIVERY_DATE > '2006-01-01';
The third alternative is a shot in the dark - depending on the exact
table structure, constraints, and your data, this might or might not be
result in the same output as the original query:
SELECT o.ORD_CLIENT_CODE, o.ORD_CREATION_DATE,
o.ORD_REQUESTED_QUANTITY, a2.INV_CODE, a2.INV_INVT_ID
FROM dbo.ORDER AS o
LEFT JOIN dbo.INVOICE_ORDER_PHASE_MAP AS iopm
ON iopm.INVOPM_OP_ORD_ID = o.ORD_ID
LEFT JOIN dbo.INVOICE AS i
ON i.INV_ID = iopm.INVOPM_INV_ID
AND i.INV_INVT_ID = 4
WHERE o.ORD_PHTI_ID = 17
AND o.ORD_REQUESTED_DELIVERY_DATE > '2006-01-01';
And finally, here's the fourth alternative that uses a standard trick to
change an awkward LEFT JOIN in a simple RIGHT JOIN:
SELECT o.ORD_CLIENT_CODE, o.ORD_CREATION_DATE,
o.ORD_REQUESTED_QUANTITY, a2.INV_CODE, a2.INV_INVT_ID
FROM dbo.INVOICE AS i
INNER JOIN dbo.INVOICE_ORDER_PHASE_MAP AS iopm
ON iopm.INVOPM_INV_ID = i.INV_ID
RIGHT JOIN dbo.ORDER AS o
ON o.ORD_ID = iopm.INVOPM_OP_ORD_ID
AND i.INV_INVT_ID = 4
WHERE o.ORD_PHTI_ID = 17
AND o.ORD_REQUESTED_DELIVERY_DATE > '2006-01-01';
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Re: sql-problem
am 04.05.2007 06:00:57 von Filips Benoit
THANKS,
SELECT dbo.[ORDER].ORD_CLIENT_CODE, dbo.[ORDER].ORD_CREATION_DATE,
dbo.[ORDER].ORD_REQUESTED_QUANTITY, 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 RIGHT OUTER JOIN
dbo.[ORDER] ON
dbo.INVOICE_ORDER_PHASE_MAP.INVOPM_OP_ORD_ID = dbo.[ORDER].ORD_ID AND
dbo.INVOICE.INV_INVT_ID = 4
WHERE (dbo.[ORDER].ORD_PHTI_ID = 17) AND
(dbo.[ORDER].ORD_REQUESTED_DELIVERY_DATE > '2006-01-01')
Forgot I have to use AND dbo.INVOICE.INV_INVT_ID = 4 in the join-part and
not in the where-part.
Filip
"Filips Benoit" wrote in message
news:Xmr_h.155906$Hn3.1097821@phobos.telenet-ops.be...
> Dear All,
>
>
> To return the recordsset I wanted I had to use 3 view OR IS THERE A BETTER
> SOLUTION ( 1 view, 1 SP ) ?????????
>
>
> TABLE1= ORDER (ORD_ID, ORD_CLIENT_CODE, ORD_CREATION_DATE,
> ORD_REQUESTED_QUANTITY, ORD_PHTI_ID ....)
> TABLE2: INVOICE( INV_CODE, INV_INVT_ID, .....)
> TABLE3: INVOICE_ORDER_PHASE_MAP( INVOPM_INV_ID,
> INVOPM_OP_ORD_ID,INVOPM_OP_SEQ_NR ) mapping orders and invoices per phase
> (seq_nr)
>
>
> I want all order WHERE (ORD_PHTI_ID = 17) AND
> (ORD_REQUESTED_DELIVERY_DATE > '2006-01-01')
> and show the ORD_CLIENT_CODE, ORD_CREATION_DATE, ORD_REQUESTED_QUANTITY
> and INV_CODE if the invoice is of type = 4 (Outgoing Invoice)
>
>
> This are some rows of the resultset
>
> a3 ORD_CLIENT_CODE ORD_CREATION_DATE ORD_REQUESTED_QUANTITY INV_CODE
> INV_INVT_ID
> PT_V06-0330_SV 12/07/2006 21 TMAL_IN_07_90222 4
> PT_V06-0363_PT 22/08/2006 309 TMAL_IN_07_90223 4
> PT_V06-0365_PT 24/08/2006 280 TMAL_IN_07_90223 4
> PT_MAL_V06-0358_LaES 16/08/2006 592 TMAL_IN_07_90224 4
> PT_V06-0497_FI 8/11/2006 60
>
>
> PT_V07-0169_PL 16/03/2007 59
>
>
> PT_V06-0655_ES 26/12/2006 54
>
>
>
>
> 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
>
>
> THANKSĀ²,
>
> Filip
>