SQL Command Not Properly Ended - Oracle Subquery

Question

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

The report should return the following...

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  

Any assistance resolving the error will be appreciated.

Answer 1

If the where before Join really matters to you, use a CTE. (Employing with clause for temporary table and joining on the same.)

  With c as (SELECT "UNITS"."UnitNumber", "BILL"."EFF_DT","BILL"."UNIT_ID"  -- Correction:  Was " BILL"."UNIT_ID" (spacetanker)
    FROM "MFIVE"."BILL_UNIT_ACCT" "BILL" -- Returning unit id column too, to be used in join

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'))

    select * from c     --Filter out your required columns from c and d alias results, e.g c.UNIT_ID
    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"
    ) d

    ON c."UNIT_ID" = d."UNIT_ID"
     order by c."UnitNumber", c."EFF_DT" desc  -- COrrection:  Removed semicolon that Crystal Reports didn't like (spacetanker)

It seems this query has lots of scope for tuning though. However, one who has access to the data and requirement specification is the best judge.

EDIT : You are not able to see data PRIOR to previous month since you are using BILL.EFF_DT in your original question's select statement, which is filtered to give only dates of previous month(..AND TO_CHAR("BILL"."EFF_DT", 'MMYYYY') = TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE), -1), 'MMYYYY'))

If you want the data as you want, I guess you have to use the BILL2 section (d part in my subquery), by giving an alias to Max(EFF_DT), and using that alias in your select clause.