Total data item in sql select statement

[therese1]

Hi I hope this is easy, I am trying to total a data item in sql so I can use it as a group by for main roll up statement this is the line I use Total([PROP_FRT_RT_AMT] FROM  FROM DMS_PRC_MN GROUP BY ROUT_SEQ)) AS TTL but I keep getting error, do I have to use this in subsequent lines?

Code Select Expand

SELECT SC_RFA_DIV_CD, PROP_NO, AMDT_SEQ, SVC_SCP_CD , CMDT_HDR_SEQ,  ROUT_NOTE_SEQ ,
(SELECT
Total([PROP_FRT_RT_AMT] FROM  FROM DMS_PRC_MN GROUP BY ROUT_SEQ)) AS TTL
       , REVERSE(SUBSTR(REVERSE(REPLACE(REGEXP_REPLACE(LISTAGG( ORG_LOC_DEF_CD || ',' ) WITHIN GROUP ( ORDER BY SC_RFA_DIV_CD, PROP_NO, AMDT_SEQ, SVC_SCP_CD , CMDT_HDR_SEQ,  ROUT_NOTE_SEQ, TTL ), '([^,]+)(,\1)+', '\1'),',,',NULL)),2,100)) ORG_LOC_DEF_CD
       , REVERSE(SUBSTR(REVERSE(REPLACE(REGEXP_REPLACE(LISTAGG( VIA_ORG_LOC_DEF_CD || ',' ) WITHIN GROUP ( ORDER BY SC_RFA_DIV_CD, PROP_NO, AMDT_SEQ, SVC_SCP_CD , CMDT_HDR_SEQ,  ROUT_NOTE_SEQ, TTL ),     '([^,]+)(,\1)+', '\1'),',,',NULL)),2,100)) VIA_ORG_LOC_DEF_CD
       , REVERSE(SUBSTR(REVERSE(REPLACE(REGEXP_REPLACE(LISTAGG( VIA_DEST_LOC_DEF_CD || ',' ) WITHIN GROUP ( ORDER BY SC_RFA_DIV_CD, PROP_NO, AMDT_SEQ, SVC_SCP_CD , CMDT_HDR_SEQ,  ROUT_NOTE_SEQ, TTL  ), '([^,]+)(,\1)+', '\1'),',,',NULL)),2,100)) VIA_DEST_LOC_DEF_CD
       , REVERSE(SUBSTR(REVERSE(REPLACE(REGEXP_REPLACE(LISTAGG( DEST_LOC_DEF_CD || ',' ) WITHIN GROUP ( ORDER BY SC_RFA_DIV_CD, PROP_NO, AMDT_SEQ, SVC_SCP_CD , CMDT_HDR_SEQ,  ROUT_NOTE_SEQ, TTL ), '([^,]+)(,\1)+', '\1'),',,',NULL)),2,100)) DEST_LOC_DEF_CD
       
FROM
(   
        SELECT DISTINCT SC_RFA_DIV_CD, PROP_NO, AMDT_SEQ, SVC_SCP_CD , CMDT_HDR_SEQ, ROUT_NOTE_SEQ, TTL
                 , DECODE(ORG_LOC_DEF_CD,'_', NULL,ORG_LOC_DEF_CD) AS ORG_LOC_DEF_CD , DECODE(VIA_ORG_LOC_DEF_CD,'_', NULL,VIA_ORG_LOC_DEF_CD) AS VIA_ORG_LOC_DEF_CD
                 , DECODE(VIA_DEST_LOC_DEF_CD,'_', NULL,VIA_DEST_LOC_DEF_CD) AS VIA_DEST_LOC_DEF_CD, DECODE(DEST_LOC_DEF_CD,'_', NULL,DEST_LOC_DEF_CD) AS DEST_LOC_DEF_CD   
                 
        FROM DMS_PRC_MN
        WHERE 1 = 1
        AND  1=1 #prompt('P_SC/RFA_No','string',' ','AND PROP_NO IN ( ', '', ')')#     
  ) 
GROUP BY SC_RFA_DIV_CD, PROP_NO, AMDT_SEQ, SVC_SCP_CD , CMDT_HDR_SEQ, ROUT_NOTE_SEQ, TTL

[therese1]

to add I think I should do this as sum but still can't get it to work. In the table Amnt is grouped by Per and Cargo type as well as CMDT hdr seq and rout seq.

The issue is that the data had been imported unrolled so Rout seq has split locations out, I need to roll these back out ignoring rout seq. I can get this to work on the select items shown in query and amnt by itself, but this amnt only pulls the last per/cargo type in rout seq so could be actually incorrect, hence why I am trying to total it.

[therese1]

ok let me clarify a little more below is the original sql I did, the excel is how the data is shown, I need to roll up all those lines highlighted in colour, I tried to use the code in my first message then do a join to original sql, ideally would like to put it in original sql but am finding it hard.

Any help would be appreciated.

Code Select Expand

Select
a.ACT_CUST_CD
,a.ACT_CUST_SEQ
,a.AMDT_SEQ
,a.AMDT_SEQ_MAX_FLG
,a.BLPL_CTNT
,a.BLPL_HDR_SEQ
,a.BLPL_TIT_NM
,a.CMDT_HDR_SEQ
,a.CMDT_SEQ
,a.CRNT_VOL_KNT
,a.CTRT_CUST_CD
,a.CTRT_DUR_TP_CD
,a.CTRT_EFF_DT
,a.CTRT_EXP_DT
,a.CTRT_MQC_TP_CD
,a.CTRT_MVC_TP_CD
,a.CTRT_NO
,a.CTRT_PTY_ADDR
,a.CTRT_PTY_CD
,a.CTRT_PTY_SGN_NM
,a.CTRT_PTY_SGN_TIT_NM
,a.CURR_CD
,a.DEL_TRSP_MOD_CD
,a.DEST_GRP_CD
,a.DEST_LOC_DEF_CD
,a.DEST_RCV_DE_TERM_CD
,a.DMDT_FT_TP_CD
,a.DUR_END_DT
,a.DUR_ST_DT
,a.EFF_DT
,a.EXP_DT
,a.FNL_FRT_RT_AMT
,a.FNL_MQC_QTY
,a.GRP_CMDT_DTL_SEQ
,a.GRP_CMDT_SEQ
,cust.NVOCC_BD_NO
,a.ORG_GRP_CD
,a.ORG_LOC_DEF_CD
,a.ORG_LOC_TP_CD
,a.ORG_RCV_DE_TERM_CD
,a.POR_TRSP_MOD_CD
,a.PRC_CGO_TP_CD
,a.PRC_CMDT_DEF_CD
,a.PRC_CMDT_DESC
,a.PRC_CMDT_DESC1
,a.PRC_CMDT_DESC2
,a.PRC_CMDT_TP_CD
,a.PRC_CTRT_CUST_TP_CD
,a.PRC_CTRT_PTY_TP_CD
,a.PRC_GRP_CMDT_CD
,a.PRC_GRP_CMDT_DESC
,a.PRC_GRP_DEL_CD
,a.PRC_GRP_POD_CD
,a.PRC_GRP_POL_CD
,a.PRC_GRP_POR_CD
,a.PROP_FRT_RT_AMT
,a.PROP_NO
,a.PRXY_FLG
,a.ROUT_DEL_LOC_DEF_CD
,a.ROUT_NOTE_CTNT
,a.ROUT_NOTE_SEQ
,a.ROUT_PNT_DEST_SEQ
,a.ROUT_PNT_ORG_SEQ
,a.ROUT_POD_LOC_DEF_CD
,a.ROUT_POL_LOC_DEF_CD
,a.ROUT_POR_LOC_DEF_CD
,a.ROUT_SEQ
,a.ROUT_VIA_DEST_SEQ
,a.ROUT_VIA_ORG_SEQ
,a.RT_SEQ
,a.SC_RFA_DIV_CD
,a.SPCL_NOTE_CTNT_SEQ
,a.SPCL_NOTE_SEQ
,a.SPCL_RT_NOTE_CTNT
,a.SPCL_RT_NOTE_TIT_NM
,a.SRC_INFO_CD
,a.SVC_SCP_CD
,a.SVC_SCP_EFF_DT
,a.SVC_SCP_EXP_DT
,a.SVC_SCP_LOC_UT_NM
,a.SVC_SCP_LOD_UT_CD
,a.SVC_SCP_MQC_QTY
,a.SVC_SCP_RQST_OFC_CD
,a.SVC_SCP_SREP_CD
,a.SVC_SCP_STS_CD
,a.SVC_SCP_STS_NM
,a.TGT_MVC_QTY
,a.VIA_DEST_GRP_CD
,a.VIA_DEST_LOC_DEF_CD
,a.VIA_DEST_LOC_TP_CD
,a.VIA_ORG_GRP_CD
,a.VIA_ORG_LOC_DEF_CD
,a.VIA_ORG_LOC_TP_CD
,b.BKG_DIR_CALL_FLG  as "D. Call"
,b.CUST_NM
,b.APPL_TP_ADJ as "Adjusted Surcharge"
,b.APPL_TP_FIX_AMT as "Fixed Surcharge"
,b.APPL_TP_INCL as "Included Surcharges"
,b.APPL_TP_NA as "Not Applicable"
,b.APPL_TP_SUBJ as "Subject To"
,b.PAY_TERM_CD
,b.PAY_TERM_NM
,b.PROP_OFC_CD
,b.PROP_SREP_CD
,b.PROP_SREP_NM
,b.RAT_EFF_DT
,b.RAT_EXP_DT
,b.RAT_UT_CD
,b.RESPB_SLS_OFC_CD
,b.SRC_NM
,(case when substr(b.RAT_UT_CD,2,1) = '2' then a.PROP_FRT_RT_AMT end) as "20"
, (case when substr(b.RAT_UT_CD,2,1) = '4' then a.PROP_FRT_RT_AMT end) as "40"
, (case when substr(b.RAT_UT_CD,2,1) = '5' then a.PROP_FRT_RT_AMT end) as "40HQ"
, (case when substr(b.RAT_UT_CD,2,1) = '7' then a.PROP_FRT_RT_AMT end) as "45"
,substr(a.RAT_UT_CD,1,1) as "Prefix"
,c.GRP_LOC_DTL_SEQ
,c.GRP_LOC_SEQ
,c.LOC_CD
,c.ORG_DEST_TP_CD
,c.PRC_GRP_LOC_CD
,c.PRC_GRP_LOC_DESC
,d.PRC_GRP_CMDT_CD as "GRP_CMDT_CD"
,d.PRC_GRP_CMDT_DESC as "GRP_CMDT_NM"
,d.PRC_CMDT_DEF_CD as "CMDT_DEF_CD"
,d.PRC_CMDT_DEF_NM as "CMDT_DEF_NM"
,d.PRC_CMDT_TP_CD as "CMDT_TP_CD"
  ,(SELECT CTRT_SRC_CD FROM
      (SELECT DISTINCT RP.CTRT_SRC_CD, RP.PROP_NO from DWS_RP_HDR RP union select DISTINCT SP.CTRT_SRC_CD, SP.PROP_NO from DWS_SP_HDR SP) B
      WHERE 1=1
      AND A.PROP_NO = B.PROP_NO
      ) AS CTRT_SRC_CD
,(
     SELECT DISTINCT LISTAGG( NOTE_CTNT, ',') WITHIN GROUP (ORDER BY Y.PROP_NO, Y.AMDT_SEQ, Y.SVC_SCP_CD, Y.CMDT_HDR_SEQ) OVER (PARTITION BY Y.PROP_NO, Y.AMDT_SEQ, Y.SVC_SCP_CD, Y.CMDT_HDR_SEQ) AS  NOTE_CTNT
      FROM
       ( SELECT DISTINCT  PROP_NO, AMDT_SEQ, SVC_SCP_CD, CMDT_HDR_SEQ, NOTE_CTNT
         FROM DWS_RP_SCP_RT_CNOTE
       WHERE SRC_INFO_CD <> 'AD'
          union all
          SELECT DISTINCT  PROP_NO, AMDT_SEQ, SVC_SCP_CD, CMDT_HDR_SEQ, NOTE_CTNT
       FROM DWS_SP_SCP_RT_CNOTE
        WHERE SRC_INFO_CD <> 'AD'
       ) Y
      WHERE 1 = 1     
       AND A.PROP_NO = Y.PROP_NO
      AND A.AMDT_SEQ = Y.AMDT_SEQ
      AND A.SVC_SCP_CD = Y.SVC_SCP_CD
      AND A.CMDT_HDR_SEQ = Y.CMDT_HDR_SEQ
     ) AS CMDT_NOTE
/*,null AS CMDT_NOTE*/
,a.BLET_DP_SEQ
, nvl((SELECT 'Y'
      FROM DWS_SP_SCP_NOTE  x     
   WHERE a.prop_no = x.prop_no and a.amdt_seq = x.amdt_seq and a.svc_scp_cd = x.svc_scp_cd
       AND x.NOTE_TP_CD = 'P'
       AND x.NOTE_CLSS_CD = 'D'
       
       AND EXISTS ( SELECT 'X' FROM DWS_SP_SCP_NOTE_CTNT
                     WHERE PROP_NO = x.PROP_NO
                       AND AMDT_SEQ = x.AMDT_SEQ 
                       AND SVC_SCP_CD = x.SVC_SCP_CD 
                       AND NOTE_SEQ = x.NOTE_SEQ 
                       AND NOTE_TP_CD = x.NOTE_TP_CD
                       AND SRC_INFO_CD <> 'AD' )
) ,'N') as Free_Time_Type
, a.AFIL_FLG

from DMS_PRC_MN a   
join DWS_PRI_CTRT_DTL_RPT b on a.SC_RFA_DIV_CD = b.SC_RFA_DIV_CD and a.PROP_NO = b.PROP_NO and a.AMDT_SEQ = b.AMDT_SEQ AND A.CTRT_NO = B.CTRT_NO and a.SVC_SCP_CD = b.SVC_SCP_CD 
      and a.CMDT_HDR_SEQ = b.CMDT_HDR_SEQ and a.CMDT_SEQ = b.CMDT_SEQ and a.ROUT_SEQ = b.ROUT_SEQ and a.PRC_CMDT_DEF_CD = b.PRC_CMDT_DEF_CD
      and a.RAT_UT_CD = b.RAT_UT_CD and a.PRC_CGO_TP_CD = b.PRC_CGO_TP_CD 
      and a.ORG_LOC_DEF_CD  = b.POR_DEF_CD and a.DEST_LOC_DEF_CD = b.DEL_DEF_CD
      AND A.ORG_RCV_DE_TERM_CD = B.ORG_RCV_DE_TERM_CD AND A.DEST_RCV_DE_TERM_CD= B.DEST_RCV_DE_TERM_CD   
     
--      AND A.VIA_ORG_LOC_DEF_CD = B.BKG_POR_DEF_CD AND A.VIA_DEST_LOC_DEF_CD= B.BKG_DEL_DEF_CD
      AND DECODE(A.VIA_DEST_LOC_DEF_CD,'_','*',VIA_DEST_LOC_DEF_CD) = B.POD_DEF_CD

left outer join DMS_PRC_LOCG c on a.PROP_NO = c.PROP_NO and A.CTRT_NO = B.CTRT_NO and a.amdt_seq = b.amdt_seq and a.SVC_SCP_CD = c.SVC_SCP_CD AND  B.POR_DEF_CD = C.LOC_CD
left outer join DMS_PRC_CMDTG d on a.PROP_NO = d.PROP_NO and A.CTRT_NO = B.CTRT_NO and a.amdt_seq = b.amdt_seq and a.GRP_CMDT_DTL_SEQ = d.GRP_CMDT_DTL_SEQ and a.GRP_CMDT_SEQ = d.GRP_CMDT_SEQ and a.SVC_SCP_CD = d.SVC_SCP_CD
left outer join dwc_customer CUST on a.CTRT_CUST_CD = cust.CUST_CNT_CD || lpad(cust.cUST_SEQ ,6,'0')
WHERE 1=1
/*a.PROP_NO in (#PROMPTMANY('P_SC/RFA_No', 'string', 'SELECT a.PROP_NO FROM DMS_PRC_MN a')# ) */
/*AND 1=1 #promptmany('P_SC/RFA_No','string',' ','AND A.PROP_NO IN ( ', '', ')')# */
AND 1=1 #prompt('P_SC/RFA_No','string',' ','AND A.PROP_NO IN ( ', '', ')')#

[bdbits]

First my standard soapbox... Having to write SQL in a Cognos report should be a last resort, and is almost always symptomatic of either not understanding how Congos should be used, a poorly designed data warehouse, or having to deal with a transactional source system (in which case you have my deepest sympathies). Moving on...

It sounds like you have a data warehouse, and the data has been imported in a way that does not work for you. 90% of what I am seeing in your SQL should be done by ETL when populating the data warehouse. Have you talked to whoever it is about getting another table populated with data that has the grain you need instead of having to hand code SQL? 

Otherwise, not being familiar with your data, I am finding it very difficult to decipher. If you copy your code directly into your query tool of choice, does it run? I would recommend starting there and simplify it as much as possible, getting the core query returning the proper core data then building things back in and verifying each new piece works as expected. Once it is working there and returning the proper result set, you can paste it into the report.

[therese1]

many thanks... yes we have request a complete rebuild of our data warehouse and data mart as we should not be using sql so much. I will breakdown and see how I go.