CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Victor
Victor

Reputation: 17097

Workaround for using alias in where clause:oracle10g

I have a query like this: select data.* from ((select table1.col1 as "a") union (select table2.col2 as "b")) data where 1 = 1

How can I add a where caluse to this if I wish to filter on column "a"? I understand that I cannot use the alias names, but how do I go about writing the where clause? I tried:

select data.* 
from (
   select table1.col1 as "a"
   union 
   select table2.col2 as "b"
) 
data where 1 = 1 
 and data.a = 'someValue'

Also:

select data.* 
from (
   select table1.col1 as "a"
   union 
   select table2.col2 as "b"
) data 
where 1 = 1 
  and data.table1.col1 = 'someValue'

Both gave errors

Upvotes: 0

Views: 1208

Answers (2)

DCookie
DCookie

Reputation: 43533

All selects in Oracle require a FROM clause:

select data.* 
  from ((select 1 as a FROM dual) union 
        (select 2 as b FROM dual)) DATA 
 where 1 = 1 
   and data.a = '1'

Substitute your table names for dual in my example (just used it for basic syntax and sanity checking).

Also, unless you need the alias to be lower case, it's easier to omit the double quotes from your aliases to avoid using double quotes everywhere you reference the alias.

Upvotes: 1

user330315
user330315

Reputation:

In addition to what DBCookie as correctly said (I assume the missing from in the inner queries is just a copy & paste error):

The derived table with the alias data only has a single column which is determined by the column name of the first SELECT in the union. This column is called "a" (not a) and because of the quotes is now case sensitive.

If you don't insist on having a lower case column name, then simply leave out the quotes from the inner queries.

select data.* 
from (
   select col1 as a
   from table1
   union 
   select col2   --- you can leave out the alias here it's useless.
   from table2  
) 
WHERE data.a = 'someValue' -- I remove the 1=1 as it doesn't serve any purpose

If you insist on having the alias in quotes, you must use them in the WHERE clause as well:

WHERE data."a" = 'someValue'

But this will also filter out values that come from table2 and have 'someValue' in their col2. I'm not sure if this is what you intended. If you only want to filter out rows that come from table1 and have 'someValue' in col1, then you need to put that condition inside the union:

select data.* 
from (
   select col1 as a
   from table1
   where col1 = 'someValue' 
   union 
   select col2
   from table2  
) data

If you are sure there are no duplicates between table1 and table2 I recommend UNION ALL over UNION as it is faster then.

Upvotes: 3

Related Questions