Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-00933 SQL Command not properly ended, UNION ALL

662474Sep 29 2008 — edited Sep 29 2008
I 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.
This post has been answered by Frank Kulash on Sep 29 2008
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 27 2008
Added on Sep 29 2008
18 comments
9,234 views