Case question

[inet]

Hi All,

The query below returns the following results:


select substring(sjt.name,1,charindex(':',sjt.name)-1) as 'JobName',
case js.freq_type when '4' then
case js.freq_subday_type when '4' then 'Sun - Sat'+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)+'-'+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_end_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)+' '+'every'+' '+cast(js.freq_subday_interval as varchar(2))+' '+'min'
end
else
case js.freq_type when '8' then
case js.freq_interval
when '1' then 'Sun'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)
when '64' then 'Sat'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)
end
else
case js.freq_type when '4' then 'Sun - Sat'+' '+Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), js.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7)
end end end as 'RunTime',
js.freq_type, js.freq_interval, js.freq_subday_type, js.freq_subday_interval
from
(select name, job_id, active_start_time, freq_type, freq_interval, freq_subday_type, freq_subday_interval, active_end_time
from sysjobschedules
where name like 'ALGL%' or name like 'WC%') as js
join
(select name, job_id
from sysjobs
where name like 'ALGL%' or name like 'WC%') as sjt
on sjt.job_id = js.job_id
order by sjt.name


Results:

JobName RunTime freq_type freq_interval freq_subday_type
-------------------------------- ------------------------------------- ----------- ------------- ----------------
ALGL-VCS Data Process Sun 10:30PM 8 1 1
ALGL-VCS Data Process Sun - Sat 6:30AM- 8:00PM every 5 min 4 1 4
ALGL-VCS Data Process NULL 4 1 1
ALGL-VCS Data Process NULL 4 1 1
ALGL-VCS Maintenance Process Sat 10:50PM 8 64 1
WC-VCS Data Process Sun 10:30PM 8 1 1
WC-VCS Data Process Sun - Sat 6:30AM- 8:00PM every 5 min 4 1 4
WC-VCS Data Process NULL 4 1 1
WC-VCS Data Process NULL 4 1 1
WC-VCS Maintenance Process Sat 10:50PM 8 64 1
WC-VCS Maintenance Process NULL 4 1 1


The results I expect to get is for the NULL values to return the runtime, i.e. Sun - Sat 10:40 PM


Can anybody tell me what is wrong with my case statement.

Thanks.

[rmiao]

Then you should add logical in case for 'null' value.