0

I have updated this question sicne I have made progress, my HAVING statement is still not working correctly. I am getting a ORA-00933: SQL command not properly ended..

I believe its my HAVING clause. Any Ideas?

RECAP:

I am trying to capture progress of customers who did not have purchasing activity between 2013-2015 and track their buying activity from Oct-2015 forward to track salesman activity..

SELECT INV.COMPANY
     , INV.CUSTOMER_ID
     , INV.ADDRESS_ID
     , INV.NAME
     , INV.ADDRESS1
     , INV.ADDRESS2
     , INV.CITY
     , INV.STATE
     , INV.ZIP_CODE
     , INV.COUNTRY
     , CICM.CLIENT_VALUE as contact_type
     , CICM.VALUE
     , CICM.DESCRIPTION
     , INV.REGION_CODE
     , INV.SITE
     , LD.LSTINVDATE
     , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13') AND TO_DATE('30-Sep-15') THEN SUM(INV.SALES) ELSE 0 END AS TWO_YEARS
     , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-15') AND SYSDATE THEN SUM(INV.SALES) ELSE 0 END AS PROGRESS
     , SUM(INV.SALES) AS SLSAMNT

FROM IFSINFO.HB_INVOICING_ALL INV 
LEFT JOIN IFSINFO.HB_LAST_INVOICE_DATE LD 
    ON (INV.COMPANY = LD.COMPANY) 
    AND (INV.CUSTOMER_ID = LD.IDENTITY)
LEFT JOIN IFSAPP.CUSTOMER_INFO_COMM_METHOD CICM
    ON (INV.ADDRESS_ID = CICM.ADDRESS_ID)
    AND (INV.CUSTOMER_ID = CICM.CUSTOMER_ID)
WHERE INV.CUST_GRP = '4'
AND INV.COMPANY = '20'
HAVING SUM(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13') AND TO_DATE('30-Sep-15') = 0
--HAVING MAX(LD.LSTINVDATE) < (SYSDATE - INTERVAL '2' YEAR)     
GROUP BY INV.COMPANY
       , INV.CUSTOMER_ID
       , INV.ADDRESS_ID
       , INV.NAME
       , INV.ADDRESS1
       , INV.ADDRESS2
       , INV.CITY
       , INV.STATE
       , INV.ZIP_CODE
       , INV.COUNTRY
       , CICM.CLIENT_VALUE
       , CICM.VALUE
       , CICM.DESCRIPTION
       , INV.REGION_CODE
       , INV.SITE
       , LD.LSTINVDATE  
       , INV.INVDATE
2
  • Do you have access to the data tracking customer marketing? Create a list of CustIDs with activty 2+ years and join your DirectMailing CustID to your sales table. Instant list of prior customers who have had direct marketing and any activity since whatever date.
    – Dave
    Sep 30 '15 at 19:46
  • @Dave, we currently do not. I am editing the question (I have made more progress) to be more clear.
    – Hector
    Sep 30 '15 at 19:49
1

The HAVING clause is the cause of the error as it not syntactically correct. You have (added parentheses to show how it is parsed):

HAVING (SUM(..) BETWEEN x AND y) = 0 

and the expression inside the parentheses returns a boolean result.

Additionally, the SUM(INV.INVDATE) doesn't make much sense. What result should one expect if we add dates?

And as @mustaccio correctly noticed, the HAVING clause goes after GROUP BY, not before.


It seems you want something like this in the HAVING clause:

HAVING SUM(CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13') 
                                              AND TO_DATE('30-Sep-15') 
           THEN INV.SALES ELSE 0 
           END) = 0

which I would prefer rewritten as:

HAVING SUM(CASE WHEN INV.INVDATE >= DATE '2013-10-01' 
                 AND INV.INVDATE < DATE '2015-10-01' 
             THEN INV.SALES ELSE 0 
           END) = 0

The INV.INVDATE should be removed from the GROUP BY clause as well and the TWO_YEARS and PROGRESS expressions should also be corrected as the SUM(...) expression suggested SUM(CASE WHEN ... THEN x ELSE y END) instead of CASE WHEN ... THEN SUM(x) ELSE y END.

4
  • Good point! I am having HAVING CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13') AND TO_DATE('30-Sep-15') THEN SUM(INV.SALES) ELSE 0 END < 1 run right now to see the results but I think your method is the clean way to approach this issue. Thank you for your reply and I will be testing it as soon as the query is done running! Thanks @ypercube for that info!
    – Hector
    Oct 1 '15 at 13:52
  • 2
    I think the bigger problem is that HAVING precedes GROUP BY...
    – mustaccio
    Oct 1 '15 at 13:52
  • @mustaccio thnx. I somehow missed that! Oct 1 '15 at 13:53
  • Good pointout @mustaccio Thank you for that!
    – Hector
    Oct 1 '15 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.