Any suggestions would be greatly appreciated. I need to have this in our next release by Monday. Thanks, Lisa
I am trying to accomplish a tricky merging of data. I have 4 tables - One master (TREQ), and 3 subtables (MOPCD, MOPCK, MOPBL). I currently have working a union statement that allows me to combine the information I need between the master and the first 2 subtables. I now need to ADD the third subtable to the query and am not sure how to proceed.
%-)
It appears to me that I cannot use another union so how can I get the results to include all rows from the master table that match my WHERE clause and link the correct M.ID from one of the three subtables depending on the MOPCD.
If T.MOPCD = 'BL' pull M.ID from MOPBL,
If T.MOPCD = 'CK' pull M.ID from MOPCK
otherwise pull M.ID from MOPCD
Here is my working query for reference:
SELECT T.ID, T.AMT, T.MOPCD, M.CDACCT
FROM TREQ T, MOPCD M
WHERE T.MOPID=M.ID AND ((T.ACT='DP' AND T.STAT='OKAY') OR (T.ACT IN ('RD','CD','PD') AND T.STAT='WAITING')) AND T.MOPCD != 'CK'
UNION
SELECT T.ID, T.AMT, T.MOPCD, M.CKACCT
FROM TREQ T, MOPCK M
WHERE T.MOPID=M.ID AND ((T.ACT='DP' AND T.STAT='OKAY') OR (T.ACT IN ('RD','CD','PD') AND T.STAT='WAITING')) AND T.MOPCD = 'CK'
I am trying to accomplish a tricky merging of data. I have 4 tables - One master (TREQ), and 3 subtables (MOPCD, MOPCK, MOPBL). I currently have working a union statement that allows me to combine the information I need between the master and the first 2 subtables. I now need to ADD the third subtable to the query and am not sure how to proceed.
%-)
It appears to me that I cannot use another union so how can I get the results to include all rows from the master table that match my WHERE clause and link the correct M.ID from one of the three subtables depending on the MOPCD.
If T.MOPCD = 'BL' pull M.ID from MOPBL,
If T.MOPCD = 'CK' pull M.ID from MOPCK
otherwise pull M.ID from MOPCD
Here is my working query for reference:
SELECT T.ID, T.AMT, T.MOPCD, M.CDACCT
FROM TREQ T, MOPCD M
WHERE T.MOPID=M.ID AND ((T.ACT='DP' AND T.STAT='OKAY') OR (T.ACT IN ('RD','CD','PD') AND T.STAT='WAITING')) AND T.MOPCD != 'CK'
UNION
SELECT T.ID, T.AMT, T.MOPCD, M.CKACCT
FROM TREQ T, MOPCK M
WHERE T.MOPID=M.ID AND ((T.ACT='DP' AND T.STAT='OKAY') OR (T.ACT IN ('RD','CD','PD') AND T.STAT='WAITING')) AND T.MOPCD = 'CK'