Home » SQL & PL/SQL » SQL & PL/SQL » Return Rows when no data exists (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
Return Rows when no data exists [message #672305] Wed, 10 October 2018 14:11 Go to next message
ssmith001
Messages: 32
Registered: August 2018
Member
I struggled a bit trying to come up with an appropriate subject line for this question, so please bear with as I didn't know what to call it.

I need to update this query so that it returns rows for days of the week that have no data returned, as shown in the attached mock up. The time frame is a running 8 days, so I always want to see every day of the week for an 8 day period.

SELECT TO_NUMBER(D.SHPG_LOC_CD, '99999') DC,
       TO_CHAR(DC.CMTD_STRT_DTT,'mm/dd/yyyy') AS "Appt Date",
       TO_CHAR(DC.CMTD_STRT_DTT,'DY') AS DAY,
       SUM(S.TOT_PCE + S.TOT_SKID) "# of Cartons Scheduled",
       SUM(NVL(CASE WHEN S.RFRC_NUM8 = 'Y' THEN S.TOT_PCE + S.TOT_SKID END,0)) "# of HOT PO CARTONS",
       TC.MAX_QTY "MAX Carton Capacity",
       SUM(NVL(CASE 
                  WHEN D.DOCK_CD LIKE 'DOOR%' AND S.RFRC_NUM15 = 'Collect' THEN S.TOT_SKID + S.TOT_PCE 
                  WHEN D.DOCK_CD LIKE 'DOOR%' AND (S.RFRC_NUM15 IN ('Prepaid','PREPAID') OR S.RFRC_NUM15 IS NULL) THEN S.TOT_PCE + S.TOT_SKID
       END,0)) "LIVE Cartons",
       SUM(NVL(CASE 
                  --WHEN (D.DOCK_CD LIKE 'DROP%' AND S.RFRC_NUM15 = 'Collect' AND S.CUST_CD = 'DECON') THEN S.TOT_PCE + S.TOT_SKID       
                  WHEN D.DOCK_CD LIKE 'DROP%' AND S.RFRC_NUM15 = 'Collect' THEN S.TOT_SKID + S.TOT_PCE
                  WHEN D.DOCK_CD LIKE 'DROP%' AND (S.RFRC_NUM15 IN ('Prepaid','PREPAID') OR S.RFRC_NUM15 IS NULL) THEN S.TOT_PCE + S.TOT_SKID  
       END,0)) "DROP Cartons",
       (SELECT COUNT(*)
        FROM JDATM_2016PRD.DOCK_CMTD_T A
        JOIN JDATM_2016PRD.DOCK_T B ON A.DOCK_ID = B.DOCK_ID
        WHERE TO_CHAR(A.CMTD_STRT_DTT, 'MM/DD/YYYY') = TO_CHAR(DC.CMTD_STRT_DTT,'mm/dd/yyyy')
          AND B.SHPG_LOC_CD = TO_NUMBER(D.SHPG_LOC_CD, '99999') 
          AND (B.DOCK_CD LIKE ('%DROP%') OR B.DOCK_CD LIKE ('%DOOR%'))
        GROUP BY TO_NUMBER(B.SHPG_LOC_CD), TO_CHAR(A.CMTD_STRT_DTT,'MM/DD/YYYY')
        ) Appt_Count
FROM JDATM_2016PRD.DOCK_CMTD_T DC,
     JDATM_2016PRD.DOCK_T D,
     JDATM_2016PRD.LD_LEG_DETL_T LD,
     JDATM_2016PRD.SHPM_T S,
     JDATM_2016PRD.LOC_THGP_GRP_T TG,
     JDATM_2016PRD.LOC_THGP_GRP_CPTY_T TC
WHERE D.DOCK_ID = DC.DOCK_ID
      AND D.SHPG_LOC_CD IN ('70','77','84','87','717','725','781','1376') -- RDC List
      AND LD.LD_LEG_ID = DC.LD_LEG_ID
      AND S.SHPM_ID = LD.SHPM_ID
      AND TG.SHPG_LOC_CD = D.SHPG_LOC_CD
      AND (TG.LOC_THGP_GRP_ID = TC.LOC_THGP_GRP_ID AND TO_CHAR(TC.STRT_DT,'MM-DD-YYYY') = TO_CHAR(DC.CMTD_STRT_DTT,'MM-DD-YYYY'))
      AND (DOCK_CD LIKE ('%DROP%') OR DOCK_CD LIKE ('%DOOR%'))
      AND TRUNC(DC.CMTD_STRT_DTT) >= TRUNC(SYSDATE)
      AND TRUNC(DC.CMTD_STRT_DTT) <= TRUNC(SYSDATE + 8)
GROUP BY TO_NUMBER(D.SHPG_LOC_CD, '99999'),
                    TO_CHAR(DC.CMTD_STRT_DTT,'mm/dd/yyyy'),
                    TO_CHAR(DC.CMTD_STRT_DTT,'DY'),
                    TC.MAX_QTY
ORDER BY TO_NUMBER(D.SHPG_LOC_CD, '99999'), TO_CHAR(DC.CMTD_STRT_DTT,'mm/dd/yyyy')


Re: Return Rows when no data exists [message #672308 is a reply to message #672305] Wed, 10 October 2018 14:28 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
We don't have your tables, so post necessary DDL to make them.
We don't have your data, so post necessary INSERT statement so we have sample data.
We don't have your requirements, so post desired results based upon sample data & complete explanation how to produce those results.
Re: Return Rows when no data exists [message #672314 is a reply to message #672305] Wed, 10 October 2018 15:53 Go to previous message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
Are you talking about "data densification"? In that case, you may need a partitioned outer join,
https://docs.oracle.com/cd/B19306_01/server.102/b14223/analysis.htm#i1014934
Previous Topic: TRIM function
Next Topic: Error in converting CLOB to BLOB
Goto Forum:
  


Current Time: Sat Oct 20 00:15:43 CDT 2018