Need help converting a CASE statement with NVL from an Oracle SQL query 1

[elsenorjose]

Hello everyone,

I have a line of code from an SQL query that I am trying to convert into syntax Crystal understands.

CASE WHEN  SCHEDULE.EVENT_CODE = 190 THEN nvl(to_char(SCHEDULE.TIME_IN,'HH24:mi' ),0) else '9/9/99' end

EVENT_CODE is a string (VARCHAR) and TIME_IN is a DATE field. The CASE statement looks at the event code and if it is = 190 and the TIME_IN field is NULL, it assigns a 0. If the EVENT_CODE = 190 and TIME_IN is not NULL, then it takes the time portion of TIME_IN and converts it to a 24hour time format (military time basically). If none of the conditions are met, then it returns 9/9/99.

Does anyone know how to do this in CR? I'm using CR XI Developer on an Oracle 10g database.

Thank you.

 

[IanWaterman]

Try

If SCHEDULE.EVENT_CODE = 190 then
(If isnull(SCHEDULE.TIME_IN) then '00:00' else
totext(SCHEDULE.TIME_IN, 'HH:mm'))
else
'9/9/99'

Ian

 

[elsenorjose]

Thanks Ian. Worked perfectly.