I'm using a Crystal Reports 13 Add Command for record selection from an Oracle database connected through the Oracle 11g Client. The error I am receiving is ORA-00933: SQL command not properly ended, but I can't find anything the matter with my code (incomplete):
/* Determine units with billing code effective dates in the previous month */ SELECT "UNITS"."UnitNumber", "BILL"."EFF_DT" FROM "MFIVE"."BILL_UNIT_ACCT" "BILL" LEFT OUTER JOIN "MFIVE"."VIEW_ALL_UNITS" "UNITS" ON "BILL"."UNIT_ID" = "UNITS"."UNITID" WHERE "UNITS"."OwnerDepartment" LIKE '580' AND TO_CHAR("BILL"."EFF_DT", 'MMYYYY') = TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE), -1), 'MMYYYY') INNER JOIN /* Loop through previously identified units and determine last billing code change prior to preious month */ ( SELECT "BILL2"."UNIT_ID", MAX("BILL2"."EFF_DT") FROM "MFIVE"."BILL_UNIT_ACCT" "BILL2" WHERE TO_CHAR("BILL2"."EFF_DT", 'MMYYYY') < TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE), -1), 'MMYYYY') GROUP BY "BILL2"."UNIT_ID" ) ON "BILL"."UNIT_ID" = "BILL2"."UNIT_ID" ORDER BY "UNITS"."UnitNumber", "BILL"."EFF_DT" DESC We are a state entity that leases vehicles (units) to other agencies. Each unit has a billing code with an associated effective date. The application is to develop a report of units with billing codes changes in the previous month. Complicating the matter is that for each unit above, the report must also show the latest billing code and associated effective date prior to the previous month. A brief example: Given this data and assuming it is now April 2016 (ordered for clarity).
Unit Billing Code Effective Date Excluded ---- ------------ -------------- -------- 1 A 04/15/2016 Present month 1 B 03/29/2016 1 A 03/15/2016 1 C 03/02/2016 1 B 01/01/2015 2 C 03/25/2016 2 A 03/04/2016 2 B 07/24/2014 2 A 01/01/2014 A later effective date prior to previous month exists 3 D 11/28/2014 No billing code change during previous month Unit Billing Code Effective Date ---- ------------ -------------- 1 B 03/29/2016 1 A 03/15/2016 1 C 03/02/2016 1 B 01/01/2015 2 C 03/25/2016 2 A 03/04/2016 2 B 07/24/2014