select distinct REQUEST.IHD1, REQUEST.IHD2, REQUEST.IHD3, REQUEST.IHD4, REQUEST.IHD5, RSWIPS.WIP, REQUEST.R_ID, REQUEST.RCV1, REQUEST.RCV2, REQUEST.RCV3, REQUEST.RCV4, REQUEST.RCV5,
REQUEST.PN1, REQUEST.PN2, REQUEST.PN3, REQUEST.PN4, REQUEST.PN5, REQUEST.PO1, REQUEST.PO2,REQUEST.PO3, REQUEST.PO4, REQUEST.PO5, P1.TD1, P2.TD2, P3.TD3, P4.TD4, P5.TD5
from RSWIPS, REQUEST,
(select distinct min(POS.TRANSACTION_DATE) as TD1, RSWIPS.WIP, REQUEST.R_ID, POS.PO_NUMBER, POS.PRODUCT_NAME, PO1,PN1,RCV1,IHD1 from REQUEST, POS, RSWIPS
where POS.PO_NUMBER=REQUEST.PO1 and POS.PRODUCT_NAME=REQUEST.PN1 and RSWIPS.R_ID=REQUEST.R_ID
group by REQUEST.R_ID, POS.PO_NUMBER, POS.PRODUCT_NAME, PO1, PN1,RCV1,IHD1, RSWIPS.WIP) P1,
(select distinct min(POS.TRANSACTION_DATE) as TD2, RSWIPS.WIP, REQUEST.R_ID, POS.PO_NUMBER, POS.PRODUCT_NAME, PO2,PN2,RCV2,IHD2 from REQUEST, POS, RSWIPS
where POS.PO_NUMBER=REQUEST.PO2 and POS.PRODUCT_NAME=REQUEST.PN2 and RSWIPS.R_ID=REQUEST.R_ID
group by REQUEST.R_ID, POS.PO_NUMBER, POS.PRODUCT_NAME, PO2, PN2,RCV2,IHD2, RSWIPS.WIP) P2,
(select distinct min(POS.TRANSACTION_DATE) as TD3, RSWIPS.WIP, REQUEST.R_ID, POS.PO_NUMBER, POS.PRODUCT_NAME, PO3,PN3,RCV3,IHD3 from REQUEST, POS, RSWIPS
where POS.PO_NUMBER=REQUEST.PO3 and POS.PRODUCT_NAME=REQUEST.PN3 and RSWIPS.R_ID=REQUEST.R_ID
group by REQUEST.R_ID, POS.PO_NUMBER, POS.PRODUCT_NAME, PO3, PN3,RCV3,IHD3, RSWIPS.WIP) P3,
(select distinct min(POS.TRANSACTION_DATE) as TD4, RSWIPS.WIP, REQUEST.R_ID, POS.PO_NUMBER, POS.PRODUCT_NAME, PO4,PN4,RCV4,IHD4 from REQUEST, POS, RSWIPS
where POS.PO_NUMBER=REQUEST.PO4 and POS.PRODUCT_NAME=REQUEST.PN4 and RSWIPS.R_ID=REQUEST.R_ID
group by REQUEST.R_ID, POS.PO_NUMBER, POS.PRODUCT_NAME, PO4, PN4,RCV4,IHD4, RSWIPS.WIP) P4,
(select distinct min(POS.TRANSACTION_DATE) as TD5, RSWIPS.WIP, REQUEST.R_ID, POS.PO_NUMBER, POS.PRODUCT_NAME, PO5,PN5,RCV5,IHD5 from REQUEST, POS, RSWIPS
where POS.PO_NUMBER=REQUEST.PO5 and POS.PRODUCT_NAME=REQUEST.PN5 and RSWIPS.R_ID=REQUEST.R_ID
group by REQUEST.R_ID, POS.PO_NUMBER, POS.PRODUCT_NAME, PO5, PN5,RCV5,IHD5, RSWIPS.WIP) P5
where REQUEST.R_ID=RSWIPS.R_ID and REQUEST.MFG=2 and REQUEST.MAPR=2
I'm trying to add the columns TD1, TD2, TD3, TD4, and TD5 to the main query. The TD columns are located within the table POS. The subqueries work fine and return the correct information. The subqueries can be joined together by a join on the PN1 and PO1 columns between the P1-5 and REQUEST tables. I'm having trouble writing the WHERE statement on this one because I do not wish to omit rows that don't show up in each of the five subqueries. The WHERE statement I have included is what I know has to be there, I'm just not sure how to join the P1-5 subqueries without ommitting rows. I've tried putting the subquery into the select statement but if I do that it just selects the minimum transaction date and I can't figure how to link it to the POs and PNs in the row.
Any help would be greatly appreciated. The table design is as follows:
REQUEST(R_ID, IHD1, IHD2, IHD3, IHD4, IHD5, RCV1, RCV2, RCV3, RCV4, RCV5, PN1, PN2, PN3, PN4, PN5, PO1, PO2, PO3, PO4, PO5
RSWIPS(R_ID, WIP)
POS(PO_NUMBER, PRODUCT_NAME, TRANSACTION_DATE)
A request has five different POs (1-5) and five different PNs (1-5). These match up with the PO_NUMBER and Product Name in the POS table.
Thanks,
-Steve