CODEWITHSUNDEEP
sqloracle-database
gabriel119435
gabriel119435

Reputation: 6802

How to change WHERE clause with conditional?

I have the following query:

select t1.a,
       t1.b,
       t2.c,
       ....
  from table1 t1,
       table2 t2,
       ...
 where ...
   and condition1
   and condition2
   and
  case
     when (t1.a in ('aa', 'bb') and
          t1.e = t2.e) then
      true
     when (t1.a in ('cc', 'dd') and
          t1.e = t2.f) then
      true
     else
      false
   end;

I want to determine where clause using a column value. Query above returns ORA-00920 invalid relational operator on last line. I checked here to adjust my clause. If t1.a is aa or bb, I must only select row if t1.e = t2.e return true. Now if t1.a is cc or dd, then it must only select row if t1.e = t2.f, comparing a different column. Is it possible to do that without procedures?

Upvotes: 0

Views: 574

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The correct way to write this is with proper join syntax. Never use commas in the FROM clause:

from table1 t1 join
     table2 t2
     on (t1.a in ('aa', 'bb') and t1.e = t2.e) or
        (t1.a in ('cc', 'dd') and t1.e = t2.f)

Or, because Oracle supports tuples:

from table1 t1 join
     table2 t2
     on (t1.a, t1.e) in ( ('aa', t2.e), ('bb', t2.e), ('cc', t2.f), ('dd', t2.f) )

Upvotes: 2

jose_bacoy
jose_bacoy

Reputation: 12684

If you really want to fix the error, change true to 1 and false to 0 then equate it to 1. Case statement is like a function and expects an expression (like = 1).

and
  (case
     when (t1.a in ('aa', 'bb') and
          t1.e = t2.e) then
      1
     when (t1.a in ('cc', 'dd') and
          t1.e = t2.f) then
      1
     else
      0
   end) = 1;

Upvotes: 2

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Boolean values are not supported by the Oracle database. They are available in PL/SQL, but the database product itself lacks Booleans, and thus you can't use values such as TRUE and FALSE in a query. Because of this, it's common to use the string values 'Y' and 'N' or 't' and 'f' to represent Boolean outcomes. I suggest rewriting your query as:

select t1.a,
       t1.b,
       t2.c,
       ....
  from table1 t1,
       table2 t2,
       ...
 where ...
   and condition1
   and condition2
   and case
         when (t1.a in ('aa', 'bb') and
              t1.e = t2.e)
           then 'Y'
         when (t1.a in ('cc', 'dd') and
              t1.e = t2.f)
           then 'Y'
         else 'N'
       end = 'Y'

Best of luck.

Upvotes: 1

9000
9000

Reputation: 40894

Why case at all? You produce trivial boolean outputs, they can be used directly.

where
   ...
   and ((t1.a in ('aa', 'bb') and t1.e = t2.e) or
        (t1.a in ('cc', 'dd') and t1.e = t2.f)
   )

Upvotes: 7

Related Questions