|
|
Posted by Filips Benoit on 05/03/07 20:12
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
Navigation:
[Reply to this message]
|