CODEWITHSUNDEEP
sqloracle-databasecasedecode
Firstname
Firstname

Reputation: 365

Using AND condition in decode

I have a scenario where I need to check the date and a value to determine the result.

Ex-

case when my_date > '10-01-2011' and my_value = 0 then
'Do this'
else 
'Do that'
end

I'm trying to use this in oracle SQL query, I was wondering if there is a better approach to this logic rather than using case statement; perhaps using decode or any other oracle function.

Upvotes: 1

Views: 13604

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132630

CASE is the ANSI SQL standard for conditional logic in SQL. Your code is therefore fine except for the string '10-01-2011' which you are treating implicitly as a date. I would use the ANSI DATE literal format for this: 

case when my_date > date '2011-01-10' and my_value = 0 then
  'Do this'
else 
  'Do that'
end

DECODE was Oracle's proprietary solution to conditional SQL before CASE was available. The same logic could be expressed using DECODE like this:

decode (my_value,
        0, decode (sign (my_date - date '2011-01-10'), 
                   1, 
                   'Do this',
                   'Do that'),
        'Do that')

Not very elegant or readable is it? It also uses the SIGN function, which returns 1 for a positive number, -1 for negative, and 0 for 0.

Note also that CASE can be used in PL/SQL whereas DECODE cannot.

-- This works
myvar := case when x=1 then 'a' else 'b' end;
-- This doesn't
myvar := decode (x, 1, 'a', 'b');

Upvotes: 6

Related Questions