Is this the correct SQL for what I need? [JDE, DB2]

Question

Consider the following JD Edwards database tables:

Work Order Routing (actual name: F3112)
Work Order Time Transactions (actual name: F31122)

The tables are linked by a common field: document number (DOCO). There may be zero or more records in F31122 for a given record in F3112.

I am trying to write a query that returns records from F3112 that meet the following criteria:

There is at least one matching record in F31122 where F31122.WTHRW (hours worked) > 0.
F3112.WLOPST (status code) between 30 and 99 (inclusive).
F3112.WLSTRT (start date) >= a specified date (JDE Julian date).
F3112.WLCTS4 (amount - unaccounted direct labor) = 0 OR F3112.WLCTS9 (hours - unaccounted direct labor) = 0.

I have come up with the following SQL that seems to be getting what I want - but I'm not 100% sure.

SELECT DISTINCT F3112.WLDOCO AS DOC_NO,
                F3112.WLDCTO AS DOC_TYPE,
                F3112.WLOPST AS STATUS,
                F3112.WLCTS4 AS CTS4,
                F3112.WLCTS9 AS CTS9,
                F3112.WLSTRT AS START,
                F3112.WLSTRX AS COMPLETE
FROM   PROD2DTA.F3112 F3112
       INNER JOIN PROD2DTA.F31122 F31122
         ON F3112.WLDOCO = F31122.WTDOCO
            AND (SELECT COUNT(*)
                 FROM   PROD2DTA.F31122
                 WHERE  F31122.WTHRW <> 0) > 0
WHERE  F3112.WLOPST BETWEEN '30' AND '99'
       AND F3112.WLSTRT >= 121060
       AND ( F3112.WLCTS4 = 0
              OR F3112.WLCTS9 = 0 )

I don't need to return anything from the F31122 table. I just need to make sure the first bullet of the selection criteria is met.

I originially did not have the DISTINCT clause and was getting multiple hits. I assume this was a Cartesian product situation.

Does this make sense for what I'm trying to do? If not, please clue me in to what I need to do.

score 2 · Accepted Answer · answered Mar 17 '21 at 20:32

Your description seems like you want a correlated EXISTS condition. Don't use DISTINCT until you first try to fix whatever introduced duplicates in the first place.

SELECT          F3112.WLDOCO AS DOC_NO,
                F3112.WLDCTO AS DOC_TYPE,
                F3112.WLOPST AS STATUS,
                F3112.WLCTS4 AS CTS4,
                F3112.WLCTS9 AS CTS9,
                F3112.WLSTRT AS START,
                F3112.WLSTRX AS COMPLETE
FROM   PROD2DTA.F3112 F3112
       WHERE EXISTS (SELECT 1 FROM PROD2DTA.F31122 F31122
                   WHERE F3112.WLDOCO = F31122.WTDOCO
                   AND F31122.WTHRW > 0) 
       AND F3112.WLOPST BETWEEN '30' AND '99'
       AND F3112.WLSTRT >= 121060
       AND ( F3112.WLCTS4 = 0
              OR F3112.WLCTS9 = 0 )

Is this the correct SQL for what I need? [JDE, DB2]

1 Answers1