CASE statement

General

CASE statement

Hi experts,

Can you please let me know one the below behavior.

select 
case when 1=1 then
cast('00010101' as timestamp format 'YYYYMMDD')
end

O/P:  1/1/0001 00:00:00.000000

select 
case when 1=1 then
cast('00010101' as timestamp format 'YYYYMMDD')
else
cast('00010101' as date format 'YYYYMMDD')
end

O/P: 1/1/0001

select 
case when 1=2 then
cast('00010101' as timestamp format 'YYYYMMDD')
else
cast('00010101' as date format 'YYYYMMDD')
end

O/P: 1/1/0001

Strangely, I could see that even though the second query in which 1=1 is true, it looks like it is executing the else part.  Any insights on this will be helpful.

Tags (2)
3 REPLIES
N/A

Re: CASE statement

Hi Cheeli,

according to the manuals "IF the THEN/ELSE clause expressions  contain a DateTime or Interval data type then  all of the THEN/ELSE clause expressions must have the same data type." 

So there will be an automatic (implicit) typecast and the rule for comparing DATE and TIMESTAMP is "The TIMESTAMP value is always converted to DATE in case of comparison." 

When i first noticed this rule i didn't find it very intuitive:

SELECT 'true' WHERE CURRENT_DATE = CURRENT_TIMESTAMP;

As a rule of thumb: When you have DATE and TIMESTAMP in a comparison/CASE you have to CAST the DATE to a TIMESTAMP explicitly.

In your case the optimizer seems to do the automatic typecast first and then applies the algorithm to simplify the expression and removes the FALSE part.

Dieter

Re: CASE statement

Hi Dieter,

Can you please explain below (I encountered it in our code - generated through Informatica) -- colname is a varchar(24) unicode

 CAST(
(
CASE WHEN ((CHARACTER_LENGTH(tablename.colname) = 8)
AND (tablename.colname <> '00000000')) THEN
CAST(tablename.colname AS TIMESTAMP FORMAT 'YYYYMMDD')
ELSE CAST('00010101' AS TIMESTAMP FORMAT 'YYYYMMDD')
END
) AS date)

Instead of casting the colname in both cases to timestamp and then to date, it can be done directly to date as below.

 CAST(
(
CASE WHEN ((CHARACTER_LENGTH(tablename.colname) = 8)
AND (tablename.colname <> '00000000')) THEN
tablename.colname
ELSE '00010101'
END
) AS date format 'YYYYMMDD')

Not sure, what caused them to follow the above one.  Is it due to Informatica

N/A

Re: CASE statement

Hi Cheeli,

of course this could be simplified.

All BI tools tend to produce overly complex code :-)

Dieter