CODEWITHSUNDEEP
sqloracle-databasecasewhere-clause
Chethan Vishal
Chethan Vishal

Reputation: 21

Use of CASE inside where clause

I am trying to incorporate the CASE condition inside the where clause as we cant use if condition. I have this if condition code with me. I want to include it in where clause. For this purpose i am using the CASE but it doesn't seem to be working, giving the following error:

ORA-00905: missing keyword

I have tried CASE condition like:

AND (CASE
        WHEN p_trx_date_low Is Null and p_trx_date_high Is Null
        Then a.trx_date = a.trx_date
        WHEN p_trx_date_low Is Not Null and p_trx_date_high Is Null
        Then a.trx_date >= p_trx_date_low
        WHEN p_trx_date_low Is Null and p_trx_date_high Is Not Null
        Then a.trx_date <= p_trx_date_high
        WHEN p_trx_date_low Is Not Null and p_trx_date_high Is Not Null 
        Then a.trx_date between p_trx_date_low and p_trx_date_high
      End CASE)

in place of below if condition:

If :p_trx_date_low Is Null and :p_trx_date_high Is Null Then
  :p_trx_date_clause := ' and a.trx_date = a.trx_date ';
ElsIf :p_trx_date_low Is Not Null and :p_trx_date_high Is Null Then
  :p_trx_date_clause := ' and a.trx_date >= :p_trx_date_low ';
ElsIf :p_trx_date_low Is Null and :p_trx_date_high Is Not Null Then
  :p_trx_date_clause := ' and a.trx_date <= :p_trx_date_high ';
ElsIf :p_trx_date_low Is Not Null and :p_trx_date_high Is Not Null Then
  :p_trx_date_clause := ' and a.trx_date between :p_trx_date_low and :p_trx_date_high ';
End If;

Upvotes: 1

Views: 60

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272446

The "if condition" could literally be translated to this:

AND (:p_trx_date_low IS     NULL AND :p_trx_date_high IS     NULL AND a.trx_date = a.trx_date)
OR  (:p_trx_date_low IS NOT NULL AND :p_trx_date_high IS     NULL AND a.trx_date >= :p_trx_date_low)
OR  (:p_trx_date_low IS     NULL AND :p_trx_date_high IS NOT NULL AND a.trx_date <= :p_trx_date_high)
OR  (:p_trx_date_low IS NOT NULL AND :p_trx_date_high IS NOT NULL AND a.trx_date BETWEEN :p_trx_date_low AND :p_trx_date_high)

And could be simplified to:

(:p_trx_date_low  IS NULL OR a.trx_date >= :p_trx_date_low) AND
(:p_trx_date_high IS NULL OR a.trx_date <= :p_trx_date_high)

Upvotes: 2

JohnHC
JohnHC

Reputation: 11205

That's not how case works. You can't return a condition, only a value

Try OR

AND (
    (p_trx_date_low Is Null and p_trx_date_high Is Null and a.trx_date = a.trx_date)
    OR
    (p_trx_date_low Is Not Null and p_trx_date_high Is Null and a.trx_date >= p_trx_date_low)
    OR
    (p_trx_date_low Is Null and p_trx_date_high Is Not Null and a.trx_date <= p_trx_date_high)
    OR
    (p_trx_date_low Is Not Null and p_trx_date_high Is Not Null and a.trx_date between p_trx_date_low and p_trx_date_high)
    )

Upvotes: 0

Related Questions