Oracle – Is there a way to rewrite my query to lower the number of times I’m querying a table

My modified query:

 SELECT SLDOCO AS ORDER_NO,
         SLDCTO AS ORDER_TYPE,
         SLAN8 AS CUSTOMER_NO,
         ABALPH AS CUSTOMER_NAME,
         SLLTTR AS LAST_STATUS,
         SLNXTR AS NEXT_STATUS,
         CASE SLTRDJ
            WHEN 0 THEN TO_DATE (TO_CHAR (1 + 1900000), 'YYYYDDD')
            ELSE TO_DATE (TO_CHAR (SLTRDJ + 1900000), 'YYYYDDD')
         END
            AS ORDER_DATE,
         CASE SLADDJ
            WHEN 0 THEN TO_DATE (TO_CHAR (1 + 1900000), 'YYYYDDD')
            ELSE TO_DATE (TO_CHAR (SLADDJ + 1900000), 'YYYYDDD')
         END
            AS SHIPPED_DATE,
         SLTDAY AS DAYTIME,
         SLUORG / 10000 AS ORDER_QTY,
         SLSOQS / 10000 AS SHIPPED_QTY
    FROM PRODDTA.F42199 INNER JOIN PRODDTA.F0101 ON SLAN8 = ABAN8
   WHERE     SLTRDJ = 119339
         AND SLLNID = 1000
         AND SLDOCO IN (19440270, 19759240, 19759237, 19440263, 19562535)
ORDER BY SLDOCO, SLNXTR

Data Model:

ORDER_NO ORDER_TYPE CUSTOMER_NO CUSTOMER_NAME LAST_ STATUS NEXT_ STATUS ORDER_ DATE SHIPPED_DATE DAYTIME ORDER_ QTY SHIPPED_ QTY 19440263 SO 295042 MERU DEPOT 520 540 12/5/2019 1/1/1900 140556 8 8 19440263 SO 295042 MERU DEPOT 540 580 12/5/2019 12/5/2019 140745 8 8 19440263 SO 295042 MERU DEPOT 580 582 12/5/2019 12/5/2019 140822 8 8 19440263 SO 295042 MERU DEPOT 582 620 12/5/2019 12/5/2019 140837 8 8 19440263 SO 295042 MERU DEPOT 620 999 12/5/2019 12/5/2019 90333 8 8

19440270 SO 295042 MERU DEPOT 520 540 12/5/2019 1/1/1900 144529 2 2 19440270 SO 295042 MERU DEPOT 540 580 12/5/2019 12/5/2019 144725 2 2 19440270 SO 295042 MERU DEPOT 580 582 12/5/2019 12/5/2019 144757 2 2 19440270 SO 295042 MERU DEPOT 582 620 12/5/2019 12/5/2019 144813 2 2 19440270 SO 295042 MERU DEPOT 620 999 12/5/2019 12/5/2019 90336 2 2

19562535 ST 295046 NAKURU DEPOT 520 540 12/5/2019 1/1/1900 202705 11 11 19562535 ST 295046 NAKURU DEPOT 540 560 12/5/2019 1/1/1900 203058 11 11 19562535 ST 295046 NAKURU DEPOT 560 580 12/5/2019 12/5/2019 233630 11 11 19562535 ST 295046 NAKURU DEPOT 580 582 12/5/2019 12/5/2019 234505 11 11 19562535 ST 295046 NAKURU DEPOT 582 620 12/5/2019 12/5/2019 234627 11 11

19759237 SV 200977 D HOLDINGS LTD 520 540 12/5/2019 1/1/1900 134200 12 12 19759237 SV 200977 D HOLDINGS LTD 540 560 12/5/2019 1/1/1900 134800 12 12 19759237 SV 200977 D HOLDINGS LTD 560 580 12/5/2019 12/5/2019 160704 12 12 19759237 SV 200977 D HOLDINGS LTD 580 582 12/5/2019 12/5/2019 160911 12 12 19759237 SV 200977 D HOLDINGS LTD 582 620 12/5/2019 12/5/2019 161034 12 12 19759237 SV 200977 D HOLDINGS LTD 620 999 12/5/2019 12/5/2019 90340 12 12

19759240 SV 200007 Naivasha Gen 520 540 12/5/2019 1/1/1900 155110 11 11 19759240 SV 200007 Naivasha Gen 540 560 12/5/2019 1/1/1900 155506 11 11 19759240 SV 200007 Naivasha Gen 560 580 12/5/2019 12/5/2019 173205 11 11 19759240 SV 200007 Naivasha Gen 580 582 12/5/2019 12/5/2019 173424 11 11 19759240 SV 200007 Naivasha Gen 582 620 12/5/2019 12/5/2019 173526 11 11 19759240 SV 200007 Naivasha Gen 620 999 12/5/2019 12/5/2019 90346 11 11

Desired Output:

For every ‘ORDER_NO’ I want to display only the row with ‘NEXT_STATUS’ of 620. Then in the same row derive two additional columns (SHIPPED_TIME and TIME_OF_DAY) with ‘DAYTIME’ values originating from rows with NEXT STATUS of 540 and 580 respectively, as the image below indicates.

What I have done:

I am getting the intended results from the sql statements below, but because the table is quite large it’s taking one and a half hours to complete the execution. Any help is appreciated.

WITH row_620
        AS (SELECT SLMCU AS "BUSINESSUNIT",
                   SLDOCO AS "ORDERNO",
                   SLDCTO AS "ORDERTYPE",
                   SLAN8 AS "CUSTOMERNO",
                   ABALPH AS "CUSTOMERNAME",
                   SLLITM AS "ITEMNO",
                   SLDSC1 AS "DESCRIPTION",
                   SLDSC2 AS "DESCRIPTION2",
                   SLLTTR AS "LAST STATUS",
                   SLNXTR AS "NEXT STATUS",
                   CASE SLTRDJ
                      WHEN 0 THEN TO_DATE (TO_CHAR (1 + 1900000), 'YYYYDDD')
                      ELSE TO_DATE (TO_CHAR (SLTRDJ + 1900000), 'YYYYDDD')
                   END AS "ORDER DATE",
                   CASE SLADDJ
                      WHEN 0 THEN TO_DATE (TO_CHAR (1 + 1900000), 'YYYYDDD')
                      ELSE TO_DATE (TO_CHAR (SLADDJ + 1900000), 'YYYYDDD')
                   END AS "SHIPPED DATE",
                   SLTDAY AS "DAYTIME",
                   SLUORG / 10000 AS "ORDER QUANTITY",
                   SLSOQS / 10000 AS "SHIPPED QUANTITY"
              FROM PRODDTA.F42199 INNER JOIN PRODDTA.F0101 ON SLAN8 = ABAN8
             WHERE SLTRDJ = 119327 AND SLLNID = 1000 AND AND SLNXTR = 620),
      row_540
        AS (SELECT sldoco, sltday     
              FROM proddta.f42199
                   INNER JOIN PRODDTA.F0101 ON SLAN8 = ABAN8
             WHERE SLTRDJ = 119327 AND SLLNID = 1000
               AND SLNXTR = 540),
      row_580
        AS (SELECT sldoco, sltday     
              FROM proddta.f42199
                   INNER JOIN PRODDTA.F0101 ON SLAN8 = ABAN8
             WHERE SLTRDJ = 119327 AND SLLNID = 1000
               AND SLNXTR = 580)
SELECT t.*,
       b.sltday time_of_day,
       c.sltday shipped_time
  FROM row_620 t
       LEFT OUTER JOIN
       row_540 b ON t."ORDERNO" = b.sldoco
       LEFT OUTER JOIN
       row_580 c ON t."ORDERNO" = c.sldoco

Answer

Use analytic functions rather than self-joins:

SELECT t.*,
       CASE next_status
       WHEN 620
       THEN MAX( CASE next_status WHEN 540 THEN daytime END )
              OVER ( PARTITION BY ORDER_NO )
       END AS time_of_day,
       CASE next_status
       WHEN 620
       THEN MAX( CASE next_status WHEN 580 THEN daytime END )
              OVER ( PARTITION BY ORDER_NO )
       END AS shipped_time
FROM   (
  SELECT   SLDOCO AS ORDER_NO,
           SLDCTO AS ORDER_TYPE,
           SLAN8 AS CUSTOMER_NO,
           ABALPH AS CUSTOMER_NAME,
           SLLTTR AS LAST_STATUS,
           SLNXTR AS NEXT_STATUS,
           CASE SLTRDJ
              WHEN 0 THEN TO_DATE (TO_CHAR (1 + 1900000), 'YYYYDDD')
              ELSE TO_DATE (TO_CHAR (SLTRDJ + 1900000), 'YYYYDDD')
           END
              AS ORDER_DATE,
           CASE SLADDJ
              WHEN 0 THEN TO_DATE (TO_CHAR (1 + 1900000), 'YYYYDDD')
              ELSE TO_DATE (TO_CHAR (SLADDJ + 1900000), 'YYYYDDD')
           END
              AS SHIPPED_DATE,
           SLTDAY AS DAYTIME,
           SLUORG / 10000 AS ORDER_QTY,
           SLSOQS / 10000 AS SHIPPED_QTY
  FROM     PRODDTA.F42199
           INNER JOIN PRODDTA.F0101
           ON SLAN8 = ABAN8
  WHERE        SLTRDJ = 119339
           AND SLLNID = 1000
           AND SLDOCO IN (19440270, 19759240, 19759237, 19440263, 19562535)
) t
ORDER BY ORDER_NO, NEXT_STATUS

(Note: PARTITION BY whatever conditions makes uniquely identifies the orders; your LEFT OUTER JOIN are just using ORDER_NO/SLDOCO so that is replicated in this query)