CODEWITHSUNDEEP
sqloracle-databasecase-when
NoName123
NoName123

Reputation: 137

ORACLE: USE RESULT OF CASE-WHEN-STATEMENT

I have a huge query and I am wondering if it is in Oracle possible to get the result of a case-when-statement and use it for comparison? My CASE-STATEMENT is declared in the Select-Statement and it looks like this.

SELECT........
  (CASE
     WHEN (Select 1 from DUAL) = 1 THEN 'TEST'
     ELSE 'TEST2'
  END) AS TEST;

Now I want to get the result of this case-statement and use it in the where part? Is it possible? (Sry this may be a dumb question)

Upvotes: 1

Views: 5364

Answers (2)

rohitvats
rohitvats

Reputation: 1851

You can use a case statement in the where clause, for eg.:

select * from table
where table.field = (CASE
                      WHEN (Select 1 from DUAL) = 1 THEN 'TEST'
                      ELSE 'TEST2'
                     END)

This will compare the value returned from the case statement with the table field.

Upvotes: 1

Matthew McPeak
Matthew McPeak

Reputation: 17924

If you define your CASE statement in either an inline-view or a common table expression (aka WITH clause), you can refer to it by whatever alias you give it.

For example (inline-view):

SELECT ...
FROM   ( SELECT .....
         (CASE
             WHEN (Select 1 from DUAL) = 1 THEN 'TEST'
             ELSE 'TEST2'
          END) AS TEST
         FROM...
       ) v
WHERE  v.test = 'TEST2';

As a common table expression, it would be:

WITH cte AS ( SELECT........
                (CASE
                   WHEN (Select 1 from DUAL) = 1 THEN 'TEST'
                   ELSE 'TEST2'
                END) AS TEST
              FROM ... )
SELECT ...
FROM   cte
WHERE  test = 'TEST2';

Upvotes: 2

Related Questions