I have been struggling with this program for a long time. I have been getting much help from you guys, so thank you. I believe this is the final question for this program (hopefully).
Attached is my crappy diagram of the process.
1. get number from WrkRank (1, 2 or 3) and locate the CCSDSC
2. get CCDESC that matches the CCSDSC
3. find matching EMDID from EDMAST
4. locate corresponding EMFAST
5. find matching MBMSTS from EDMTAB
6. get corresponding MBCODE
the step I am missing is, "if there are any MBCODE that matches CCSDSC and CCTABL = 'STSCONTB' from ZCC, do not include. (in red)
The problem is that if I want to include the new condition, I have to put another condition in the where clause, BUT since I have to say CCTABL = 'STSCONTB' in the same cluase as CCTABL = 'DOCCONTB', it returns nothing because there are no records that can be both at the same time. I tried OR instead of AND but that is no good because I need it to fulfill both conditions not one OR the other. I have tried a HAVING method after doing some search, but that doesn't work either. I also heard you can create a whole new table that I can make comparisons to within the query but no luck finding anything online for this method.
Here is the HAVING method that I tried. returns nothing.
I did not include EMBOX and EMDAT as they would just follow the EMDID if the correct ones are returned. They are in the EDMAST table.
I hope I made it clear. If not PLEASE let me know so i can clarify...I need help TT.TT Thank you.
Attached is my crappy diagram of the process.
1. get number from WrkRank (1, 2 or 3) and locate the CCSDSC
2. get CCDESC that matches the CCSDSC
3. find matching EMDID from EDMAST
4. locate corresponding EMFAST
5. find matching MBMSTS from EDMTAB
6. get corresponding MBCODE
the step I am missing is, "if there are any MBCODE that matches CCSDSC and CCTABL = 'STSCONTB' from ZCC, do not include. (in red)
Code:
SELECT EMDID, EMBOX, EDMTAB.MBCODE, EMDAT FROM EDI4XXDTA/EDMAST as EDMAST INNER JOIN ZCC AS ZCC ON EDMAST.EMDID = CAST(ZCC.CCDESC AS CHAR(3)) INNER JOIN EDI4XXDTA/EDMTAB AS EDMTAB ON EDMAST.EMFAST = EDMTAB.MBMSTS WHERE (CAST(ZCC.CCSDSC AS Char(2)) = :Wrkrank AND ZCC.CCTABL = 'DOCCONTB') GROUP BY EMDID, EMBOX, MBCODE, EMDAT ORDER BY EMDID, EMBOX, MBCODE, EMDAT
The problem is that if I want to include the new condition, I have to put another condition in the where clause, BUT since I have to say CCTABL = 'STSCONTB' in the same cluase as CCTABL = 'DOCCONTB', it returns nothing because there are no records that can be both at the same time. I tried OR instead of AND but that is no good because I need it to fulfill both conditions not one OR the other. I have tried a HAVING method after doing some search, but that doesn't work either. I also heard you can create a whole new table that I can make comparisons to within the query but no luck finding anything online for this method.
Here is the HAVING method that I tried. returns nothing.
Code:
SELECT EMDID, EMBOX, EDMTAB.MBCODE, EMDAT FROM EDI4XXDTA/EDMAST as EDMAST INNER JOIN ZCC AS ZCC ON EDMAST.EMDID = CAST(ZCC.CCDESC AS CHAR(3)) INNER JOIN EDI4XXDTA/EDMTAB AS EDMTAB ON EDMAST.EMFAST = EDMTAB.MBMSTS WHERE (CAST(ZCC.CCSDSC AS Char(2)) = WrkRank AND ZCC.CCTABL = 'DOCCONTB') OR (EDMTAB.MBCODE NOT IN (SELECT CAST(CCSDSC AS CHAR(3)) FROM ZCC) AND ZCC.CCTABL = 'STSCONTB') GROUP BY EMDID, EMBOX, MBCODE, EMDAT HAVING COUNT(DISTINCT ZCC.CCTABL) = 2 ORDER BY EMDID, EMBOX, MBCODE, EMDAT
I did not include EMBOX and EMDAT as they would just follow the EMDID if the correct ones are returned. They are in the EDMAST table.
I hope I made it clear. If not PLEASE let me know so i can clarify...I need help TT.TT Thank you.
Comment