ORACLE: USE RESULT OF CASE-WHEN-STATEMENT

Question

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)

The exact way you ask in your question is not possible. If you define the expression which you labeled (aliased) as TEST in the SELECT clause, the name TEST is not visible in the WHERE clause of the same SELECT query, because WHERE is processed before SELECT. If you don't want to repeat the same (possibly very long) CASE expression in the WHERE clause, you are better off defining TEST in a subquery, then select and use it in the WHERE clause in an outer query. (You can do that with a WITH clause.) Better for future code maintenance, too. — user5683823
– user5683823, Commented Mar 7, 2017 at 16:34

Matthew McPeak · Accepted Answer · 2017-03-07 16:24:21Z

2

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';

answered Mar 7, 2017 at 16:24

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rohitvats · Accepted Answer · 2017-03-07 16:30:44Z

1

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.

edited Mar 7, 2017 at 16:30

answered Mar 7, 2017 at 16:24

rohitvats

1,85113 silver badges14 bronze badges

Collectives™ on Stack Overflow

ORACLE: USE RESULT OF CASE-WHEN-STATEMENT

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related