case when gone wrong

[kangklin]

I meant for the following query (run on a friday) to return "FRIDAY" in the first column and "1" in the second column. It returns FRIDAY alright, but in the second column returns 0. Can anyone tell my why and how to fix it?

SELECT a.dayofweek, (CASE WHEN a.dayofweek = 'FRIDAY' THEN 1 ELSE 0 END) daynum
FROM (SELECT TO_CHAR(sysdate ,'DAY') as dayofweek
FROM dual
) a

[r035198x]

The field might not be exactly 'FRIDAY' in the database. Perhaps there are extra spaces e.t.c. Try TRIMming the values before comparing.

[debasisdas]

As suggested in the previous post ry the following.

[code=oracle]
SELECT a.dayofweek, (CASE WHEN TRIM(a.dayofwee k) = 'MONDAY' THEN 1 ELSE 0 END) daynum
FROM (SELECT TO_CHAR(sysdate ,'DAY') as dayofweek
FROM dual
) a

[/code]