Link to home
Start Free TrialLog in
Avatar of futureDBA
futureDBA

asked on

Group By issue

in reality I only want to group by cm_custno, but when I add "+TH_TKTOTL" to my first sum statement, i am told its not a valid group by, if i add sdate to the groupby clause then the query works, but its being grouped by date when I only want to group by customer number


SELECT 
       CM_CUSTNO AS "Customer Number",
       CM_CUSTMR_NAME as "Business Name",
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND(SYSDATE) - (23) AND ROUND(SYSDATE) THEN nvl(th_dolrs,0) END),0)+TH_TKTOTL AS "Current Period",
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND((SYSDATE - (23)) - (30)) AND ROUND(SYSDATE - 30) THEN nvl(th_dolrs,0) END),0) AS "Second Period",
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND(SYSDATE - ((30)*3)) and ROUND(SYSDATE - ((30)*2)) then th_dolrs END),0) as "Third Period",
       nvl(SUM(CASE WHEN sdate < ROUND(SYSDATE - ((30)*3)) then th_dolrs END),0) as "Fourth Period"
    FROM(   
SELECT 
        CM_CUSTNO,
        THD_TICKNO, 
        TH_PRODNO, 
        TH_UNITS, 
        TH_DOLRS, 
        PM_PRDESC, 
        PM_VOLUME, 
        CM_TKTMSG, 
        CM_CUSTMR_NAME, 
        TH_CSHRCV, 
        TH_PURORD, 
        TH_ROUTNO, 
        TO_DATE((thd_tranda) || '-' || thd_tranmo || '-' || thd_trancn || thd_tranyr, 'DD-MM-YYYY') AS "SDATE", 
        CM_NOWDUE AS "CUSTOMER BALANCE", 
        CM_SLSMAN, 
        THD_CUSTNO, 
        THD_TRANYR, 
        THD_TRANDA, 
        TH_TKTOTL, 
        TH_CSHRCV AS DIFF, 
        cm_chnid
FROM ((THSDTL@"DB2" INNER JOIN CUSMAS@"DB2" ON THSDTL.THD_CUSTNO = CUSMAS.CM_CUSTNO) 
	INNER JOIN RAPFILES.PROMAS@"DB2" ON THSDTL.TH_PRODNO = PROMAS.PM_PRODNO) 
	INNER JOIN TICHIS@"DB2" ON (THSDTL.THD_TICKNO = TICHIS.TH_TICKNO)
  LEFT OUTER JOIN TICKET@"DB2" ON TICKET.TK_CUSTNO = CUSMAS.CM_CUSTNO 
  AND CUSMAS.CM_CUSTNO = TICHIS.TH_CUSTNO
WHERE TH_TKTOTL-TH_CSHRCV >1
AND CM_CUSTNO IN (1606,1608,1609,1611,1614,1620,9701,9778))
GROUP BY
CM_CUSTNO,
CM_CUSTMR_NAME;

Open in new window

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Add TH_TKTOTL to the group by.
Avatar of futureDBA

ASKER

when i do that it splits cm_custno, so for customer 1609 i have multiple rows returned in the query
Then try another outer select:


SELECT
       "Customer Number",
       "Business Name",
       "Current Period" +TH_TKTOTL AS "Current Period",
       "Second Period",
       "Third Period",
       "Fourth Period"
    FROM(
SELECT
       CM_CUSTNO AS "Customer Number",
       CM_CUSTMR_NAME as "Business Name",
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND(SYSDATE) - (23) AND ROUND(SYSDATE) THEN nvl(th_dolrs,0) END),0) AS "Current Period",
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND((SYSDATE - (23)) - (30)) AND ROUND(SYSDATE - 30) THEN nvl(th_dolrs,0) END),0) AS "Second Period",
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND(SYSDATE - ((30)*3)) and ROUND(SYSDATE - ((30)*2)) then th_dolrs END),0) as "Third Period",
       nvl(SUM(CASE WHEN sdate < ROUND(SYSDATE - ((30)*3)) then th_dolrs END),0) as "Fourth Period"
    FROM(
...
that makes sense.  give me a sec, let me try that
ORA-00904: "TH_TKTOTL": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 4 Column: 5
SELECT
  CM_CUSTNO AS "Customer Number",
  BusNam AS "Business Name",
  CP+TH_TKTOTL AS "Current Priod",
  SP AS "Second Period",
  TP AS "Third Period",
  FP AS "Fourth Period"
FROM(
SELECT 
       CM_CUSTNO,
       CM_CUSTMR_NAME as BusNam,
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND(SYSDATE) - (23) AND ROUND(SYSDATE) THEN nvl(th_dolrs,0) END),0) AS CP,
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND((SYSDATE - (23)) - (30)) AND ROUND(SYSDATE - 30) THEN nvl(th_dolrs,0) END),0) AS SP,
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND(SYSDATE - ((30)*3)) and ROUND(SYSDATE - ((30)*2)) then th_dolrs END),0) as TP,
       nvl(SUM(CASE WHEN sdate < ROUND(SYSDATE - ((30)*3)) then th_dolrs END),0) as FP
    FROM(   
SELECT 
        CM_CUSTNO,
        THD_TICKNO, 
        TH_PRODNO, 
        TH_UNITS, 
        TH_DOLRS, 
        PM_PRDESC, 
        PM_VOLUME, 
        CM_TKTMSG, 
        CM_CUSTMR_NAME, 
        TH_CSHRCV, 
        TH_PURORD, 
        TH_ROUTNO, 
        TO_DATE((thd_tranda) || '-' || thd_tranmo || '-' || thd_trancn || thd_tranyr, 'DD-MM-YYYY') AS "SDATE", 
        CM_NOWDUE AS "CUSTOMER BALANCE", 
        CM_SLSMAN, 
        THD_CUSTNO, 
        THD_TRANYR, 
        THD_TRANDA, 
        TH_TKTOTL, 
        TH_CSHRCV AS DIFF, 
        cm_chnid
FROM ((THSDTL@"DB2" INNER JOIN CUSMAS@"DB2" ON THSDTL.THD_CUSTNO = CUSMAS.CM_CUSTNO) 
	INNER JOIN RAPFILES.PROMAS@"DB2" ON THSDTL.TH_PRODNO = PROMAS.PM_PRODNO) 
	INNER JOIN TICHIS@"DB2" ON (THSDTL.THD_TICKNO = TICHIS.TH_TICKNO)
  LEFT OUTER JOIN TICKET@"DB2" ON TICKET.TK_CUSTNO = CUSMAS.CM_CUSTNO 
  AND CUSMAS.CM_CUSTNO = TICHIS.TH_CUSTNO
WHERE TH_TKTOTL-TH_CSHRCV >1
AND CM_CUSTNO IN (1606,1608,1609,1611,1614,1620,9701,9778))
GROUP BY
CM_CUSTNO,
CM_CUSTMR_NAME);//

Open in new window

>>ORA-00904: "TH_TKTOTL": invalid identifier

DUH... yes, it needs to be part of the innser select which leads back to it having to be part of the group by.

try adding TH_TKTOTL to the group by then the SUM in the outer query.
:-\ sorry, your last comment did not make sense to me. kinda lost me


th_tktotl is on the first select does it need to be in the middle one ?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial