?
Solved

Help with syntax error on SQL UNION command

Posted on 2004-09-22
4
Medium Priority
?
3,208 Views
Last Modified: 2007-12-19
This SQL command:

SELECT
  IT_TYPE  type,
  IT_DBAC1 db_acct,
  IT_CRAC1 cr_acct,
  IT_AMNT1 total
FROM IVTR_AML
WHERE IT_AMNT1 > 0

UNION

SELECT
  IT_TYPE  type,
  IT_DBAC2 db_acct,
  IT_CRAC2 cr_acct,
  IT_AMNT2 total
FROM IVTR_AML
WHERE IT_AMNT2 > 0

GROUP BY type, db_acct, cr_acct;

EXIT;

Produces these errors:

SP2-0042: unknown command "UNION" - rest of line ignored.
SP2-0734: unknown command beginning "GROUP BY t..." - rest of line ignored.

Why?

I'm running the command in SQLPlus against an Oracle 8i database running on an IBM RS/6000 computer.
0
Comment
Question by:jrollins138
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 

Expert Comment

by:CedD
ID: 12124096
Remove empty lines and it will be ok

SELECT
  IT_TYPE  type,
  IT_DBAC1 db_acct,
  IT_CRAC1 cr_acct,
  IT_AMNT1 total
FROM IVTR_AML
WHERE IT_AMNT1 > 0
UNION
SELECT
  IT_TYPE  type,
  IT_DBAC2 db_acct,
  IT_CRAC2 cr_acct,
  IT_AMNT2 total
FROM IVTR_AML
WHERE IT_AMNT2 > 0
GROUP BY type, db_acct, cr_acct;
0
 

Author Comment

by:jrollins138
ID: 12124268
Did that and now I'm getting this ('*' is under the 'c' in cr_acct):

GROUP BY type, db_acct, cr_acct
                                    *
ERROR at line 16:
ORA-00904: invalid column name

Any suggestions?
0
 

Accepted Solution

by:
CedD earned 1200 total points
ID: 12124373
It depends on what you want to group by.

First, the correct query is much like :

select type, db_acct, cr_acct, total from (
SELECT
  IT_TYPE  type,
  IT_DBAC1 db_acct,
  IT_CRAC1 cr_acct,
  IT_AMNT1 total
FROM IVTR_AML
WHERE IT_AMNT1 > 0
UNION
SELECT
  IT_TYPE  type,
  IT_DBAC2 db_acct,
  IT_CRAC2 cr_acct,
  IT_AMNT2 total
FROM IVTR_AML
WHERE IT_AMNT2 > 0 )
GROUP BY type, db_acct, cr_acct, total;

For the group by, that depends on what you want to do after the union.
0
 

Author Comment

by:jrollins138
ID: 12126009
That nailed it. Here's the resulting query:

SELECT type, db_acct, cr_acct, SUM(total) FROM (
SELECT
  IT_TYPE  type,
  IT_DBAC1 db_acct,
  IT_CRAC1 cr_acct,
  IT_AMNT1 total
FROM IVTR_AML
WHERE IT_AMNT1 > 0
UNION
SELECT
  IT_TYPE  type,
  IT_DBAC2 db_acct,
  IT_CRAC2 cr_acct,
  IT_AMNT2 total
FROM IVTR_AML
WHERE IT_AMNT2 > 0)
GROUP BY type, db_acct, cr_acct;

What I've got is a record that has two sets of debit/credit/amount fields. What I want is a list of records summarized by type/debit account/credit account. The UNION aligns the second set of fields with the first and the SUM/GROUP BY handles the math. Records from the resulting query will show me the total amount associated with each type/debit/credit combination. Thanks for the help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question