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
Best Answer
The
HAVING
clause is the cause of the error as it not syntactically correct. You have (added parentheses to show how it is parsed):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 afterGROUP BY
, not before.It seems you want something like this in the
HAVING
clause:which I would prefer rewritten as:
The
INV.INVDATE
should be removed from theGROUP BY
clause as well and theTWO_YEARS
andPROGRESS
expressions should also be corrected as theSUM(...)
expression suggestedSUM(CASE WHEN ... THEN x ELSE y END)
instead ofCASE WHEN ... THEN SUM(x) ELSE y END
.