ORA-00933 SQL Command not properly ended, UNION ALL
662474Sep 29 2008 — edited Sep 29 2008I apologize if this is completely simple, but it's got me going in circles...
I inherited some spaghetti code to repair and revise (550 line sql query, sent from VBA). I'm trying to put it in parts as Views. In the following code, I'm trying to call two subqueries, which I've stored as views (Credit_Report_Exposure_P_Sub_A and Credit_Report_Exposure_P_Sub_B). I create a UNION of them, and it works fine--UNTIL I add a WHERE clause. So:
---------------------------------------------------------------------------------
SELECT
PROFILENAME,
month,
sum
FROM
Credit_Report_Exposure_P_Sub_A
UNION ALL
(SELECT
ccp_profile_name PROFILENAME,
VWMONTH Month,
sum(PSJ_TOTAL_AMOUNT) SUM
FROM
Credit_Report_Exposure_P_Sub_B
GROUP by
VWMONTH,
ccp_profile_name)
------------------------------------------------------------------------------------------
This works fine, to select the same 3 columns from each subquery (ignoring for a moment the bad choice of aliases "month" and "sum").
But when I add the following WHERE clause:
------------------------------------------------------------------------------------------
WHERE
month = '2008-09'
------------------------------------------------------------------------------------------
I get the error in the subject line--not properly ended.
But: if I comment out either half of the UNION, then the WHERE clause works fine.
Am I missing something obvious? I've tried wrapping the whole UNION in more parentheses, and the first clause of the UNION as well, but that only creates more errors about supposed missing right parens.
Thanks for any help.