CODEWITHSUNDEEP
oracle-database
D_ROCKS
D_ROCKS

Reputation: 59

How to use like operator outside SELECT

I have the following query which works fine.

SELECT A.*
FROM 
(
    SELECT 'ABC','DEF' FROM DUMMY
    UNION ALL
    SELECT col1,col2 FROM DUMMY
) A;

I want to apply the like key word on the first column returned by the nested query. I tried the following:

SELECT col1, col2
FROM 
(
    SELECT 'ABC','DEF' FROM DUMMY
    UNION ALL
    SELECT col1,col2 FROM DUMMY
) A
WHERE COL1 LIKE 'A%';

But the above query did not work. I'm getting the error: ORA-00904: "COL1": invalid identifier.

The fiddle link for the same is as follows: http://sqlfiddle.com/#!4/1c54b/7

Please could anybody guide me on how I can achieve the same?

Edit: I'm also trying to get the columns from the existing table as well.

Thanks in advance.

Upvotes: 0

Views: 95

Answers (1)

Yusuf K.
Yusuf K.

Reputation: 4250

  • When using union, column names or aliases have to be matched on both tables.
  • You can call already defined column names and aliases.

Do not forget define column alias('ABC' as col1)

SELECT col1, col2
FROM 
(
    SELECT 'ABC' as col1,'DEF' as col2 FROM DUMMY
    UNION ALL
    SELECT 'GHI' as col1,'JKL' as col2 FROM DUMMY
) A
WHERE COL1 LIKE 'A%';

I think you are trying something like;

SELECT col1, col2
FROM 
(
    SELECT col1,col2 FROM DUMMY
    UNION ALL
    SELECT col1,col2 FROM DUMMY2
) A
WHERE col1 LIKE 'A%';

Have a look at another sample from me on here

Upvotes: 1

Related Questions