CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Saurabh
Saurabh

Reputation: 35

SELECT statement on top of UNION statement

select * 
from
    {
        SELECT 
            ID, CLASS, CHANGE_NUMBER AS OBJECT_NUMBER 
        FROM table_A

        UNION

        SELECT 
            ID, CLASS, CUST_NO AS OBJECT_NUMBER 
        FROM table_B
        ORDER BY ID
    } x where x.id ='5434';

Help me to run this query.

I am getting error "invalid table name"

Upvotes: 1

Views: 101

Answers (2)

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Use '(' bracket instead of '{'.

            select * from
            (
            SELECT ID,CLASS, CHANGE_NUMBER AS OBJECT_NUMBER FROM table_A
            UNION
            SELECT ID,CLASS,CUST_NO AS OBJECT_NUMBER FROM table_B
            ORDER BY ID
            ) x where x.id ='5434';

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would suggest writing the query like this:

select x.*
from (SELECT ID, CLASS, CHANGE_NUMBER AS OBJECT_NUMBER FROM table_A
      UNION ALL
      SELECT ID, CLASS, CUST_NO AS OBJECT_NUMBER FROM table_B
     ) x
where x.id = '5434';

Notes:

  • The curly braces are probably your syntax problem.
  • Use UNION ALL instead of UNION, unless you really want to incur the overhead of removing duplicates.
  • The ORDER BY is not needed. After all, you are only choosing one id.
  • If you do have an ORDER BY, it is better practice to put it in the outer query than in the subquery.

Upvotes: 2

Related Questions