CODEWITHSUNDEEP
sqldb2
sirocode
sirocode

Reputation: 7

DB2 Subquery Best Practice

I have a requirement to write a DB2 query that performs 9 subqueries to get status dates from another table. The query is running very slowly, because of all of the subqueries. I'm wondering if there's a more optimal way to do this. Any suggestions? SQL below:

SELECT BILL_NUMBER,

(SELECT MIN(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'ASSGN' and char(ORDER_ID) = BILL_NUMBER) as "FIRST ASSGN",

(SELECT MAX(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'ASSGN' and char(ORDER_ID) = BILL_NUMBER) as "LAST ASSGN", 

(SELECT MAX(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'D-CPICKED' and char(ORDER_ID) = BILL_NUMBER) as "LAST DC PICKED",

(SELECT MIN(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'CONTACTED' and char(ORDER_ID) = BILL_NUMBER) as "FIRST CONTACTED STATUS",

(SELECT MIN(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'UNLOADED' and char(ORDER_ID) = BILL_NUMBER) as "FIRST UNLOADED STATUS",

(SELECT MIN(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'CHECKIN' and char(ORDER_ID) = BILL_NUMBER) as "FIRST CHECKIN STATUS",

(SELECT MIN(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'LHDISP' and char(ORDER_ID) = BILL_NUMBER) as "FIRST LHDISP STATUS",

(SELECT MAX(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'LHDISP' and char(ORDER_ID) = BILL_NUMBER) as "LAST LHDISP STATUS",

(SELECT MAX(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'D-DISP' and char(ORDER_ID) = BILL_NUMBER) as "LAST DDISP STATUS"

FROM TMWIN.TLORDER
WHERE pick_up_by >= '8/1/2021' and pick_up_by <= '8/3/2021'

Upvotes: 0

Views: 214

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Use conditional aggregation:

SELECT BILL_NUMBER,
       MIN(CASE WHEN OS.STATUS_CODE = 'ASSGN' THEN OS.Changed END) as first_assign,
       MAX(CASE WHEN OS.STATUS_CODE = 'ASSGN' THEN OS.Changed END) as last_assign,
       . . 
FROM TMWIN.TLORDER O JOIN
     TMWIN.ODRSTAT OS
     ON CHAR(OS.ORDER_ID) = O.BILL_NUMBER
WHERE pick_up_by >= DATE '2021-08-01' AND
      pick_up_by <= DATE '2021-08-03'
GROUP BY O.BILL_NUMBER;

Upvotes: 2

Related Questions