UNION / ORA-00933: SQL command not properly ended

Question

I'm getting a "ORA-00933: SQL command not properly ended" error message when I run this code. I've checked commas, parenthesis, etc. and cannot find what is wrong with this. Any suggestions? This is the first time that I have used UNION ALL in my code. The first part of the union does exactly what I looking for. I've added the second part of the union because I only want those couple of fields for fiscal year 2014 and accounting period 11 only. I dont want that information for accounting period 10 in this part even though I do want it in the first part. Thanks for the help

Select
    Bus_UNIT, 
    DESCRIPT,
    DEPTID,
    ASSET_NO,
    ACCT_AD,
    BOOK,
    CURRENT_AMT,
    PRIOR_AMT,
    ACCT_DE,
    JRNL_ID,
    JRNL_DT    
From ( SELECT 
    pa.BUSINESS_UNIT as BUS_UNIT, 
    pa.DESCR AS DESCRIPT,
    pdr.DEPTID AS DEPTID, 
    pdr.ASSET_ID AS ASSET_NO, 
    pdr.ACCOUNT_AD AS ACCT_AD, 
    pdr.BOOK AS BOOK,

    MAX(CASE WHEN (pdr.FISCAL_YEAR =2014 AND pdr.ACCOUNTING_PERIOD =11) THEN  pdr.DEPR END) as CURRENT_AMT,
    MAX(CASE WHEN (pdr.FISCAL_YEAR =2104 AND pdr.ACCOUNTING_PERIOD =10) THEN pdr.DEPR  END) as PRIOR_AMT,

    '' AS ACCT_DE,
    '' AS JRNL_ID,
    '' AS JRNL_DT

    FROM PS_ASSET pa

    INNER JOIN PS_DEPR_RPT pdr 
    ON pa.ASSET_ID = pdr.ASSET_ID
    AND pa.BUSINESS_UNIT = pdr.BUSINESS_UNIT

    WHERE 
    pa.BUSINESS_UNIT='A0465'
    AND pdr.BOOK='PERFORM'
    AND ((pdr.FISCAL_YEAR=2014 AND pdr.ACCOUNTING_PERIOD=11) 
    OR (pdr.FISCAL_YEAR=2014 AND pdr.ACCOUNTING_PERIOD=10))

    group by
    pa.business_unit,
    pa.descr,
    pdr.deptid,
    pdr.asset_id,
    pdr.account_ad,
    pdr.book

UNION ALL

    select
    '' as BUS_UNT,
    '' AS DESCRIPT,
    '' AS DEPTID, 
    '' AS ACCT_AD, 
    '' AS BOOK,
    '' AS CURRENT_AMT,
    '' AS PRIOR_AMT,
    pdl.asset_id AS ASSET_NO,
    pdl.account AS ACCT_DE,
    pdl.journal_id AS JRNL_ID,
    pdl.journal_date AS JRNL_DT

    from ps_dist_ln pdl

    where
    book = 'PERFORM'
    and business_unit = 'A0465'
    and fiscal_year = 2014
    and accounting_period = 11
    and distribution_type = 'DE'
) as TEST    
group BY ASSET_NO;

score 0 · Accepted Answer · answered Sep 12 '14 at 13:47

0

Oracle does not allow as for table aliases. Just remove the as.

The as is optional for column aliases, but highly recommended.

answered Sep 12 '14 at 13:47

Gordon Linoff

1,242,037
58
646
786

Thank you very much. I removed it and now I'm getting the following error message: ORA-01790: expression must have same datatype as corresponding expression. The line references the first Max(case....) statement. Any suggestions? Thanks for your help. – Shaves Sep 12 '14 at 14:23
@Shaves . . . Remove the columns one-by-one from the two parts of the `union`. When you know which column has the problem, then fix the problem, probably using `cast()`. – Gordon Linoff Sep 12 '14 at 15:46
Thank You......that helped a bunch and allowed me to fix the issue – Shaves Sep 15 '14 at 15:56
@Shaves . . . Is there a reason the answer was "unaccepted"? I'm just curious; the OP has full authority to accept whichever answer s/he wants to. – Gordon Linoff Dec 02 '14 at 21:07
I think its failing because these come out as amounts columns can't say 100% the dataypes by looking at it. MAX(CASE WHEN (pdr.FISCAL_YEAR =2014 AND pdr.ACCOUNTING_PERIOD =11) THEN pdr.DEPR END) as CURRENT_AMT, MAX(CASE WHEN (pdr.FISCAL_YEAR =2104 AND pdr.ACCOUNTING_PERIOD =10) THEN pdr.DEPR END) as PRIOR_AMT In you're union you have them as an empty string ''. You should change this to the correct datatype. – Jt2ouan Dec 03 '14 at 16:43
@GordonLinoff ....If I marked it as "unaccepted" it was a mistake on my part. I didn't mean to do that. I apologize for my error – Shaves Dec 11 '14 at 20:38
@GordonLinoff....I think I just accepted the answer. Sorry for the confusion....and Thanks for the help – Shaves Dec 15 '14 at 14:55

UNION / ORA-00933: SQL command not properly ended

1 Answers1