Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Case Statement Question

    Hi All,

    Can anybody tell why the underlined when statement doesn't return any results? It should return something like this: Sun - Sat 10:30PM

    select substring(sjt.name,1,charindex(':',sjt.name)-1),
    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
    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'
    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
    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


    This is the output of this query:
    ------------------------------- -------------------------------------
    ALGL-VCS Data Process Sun 10:30PM
    ALGL-VCS Data Process Sun - Sat 6:30AM- 8:00PM every 5 min
    ALGL-VCS Data Process NULL (underlined when stmt)
    ALGL-VCS Data Process NULL (underlined when stmt)
    ALGL-VCS Maintenance Process Sat 10:50PM
    WC-VCS Data Process Sun 10:30PM
    WC-VCS Data Process Sun - Sat 6:30AM- 8:00PM every 5 min
    WC-VCS Data Process NULL (underlined when stmt)
    WC-VCS Data Process NULL (underlined when stmt)
    WC-VCS Maintenance Process Sat 10:50PM
    WC-VCS Maintenance Process NULL (underlined when stmt)

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Is it just me, or does it appear that you have two WHEN '4' entries in your case statement?

    Perhaps you aren't seeing the results of the second one because you are seeing the results of the first one?

    Not trying to be a smartass, but unless I missed an END somewhere, seems like you have duplicate WHEN's in there.


    BTW...my head hurts from reading that code...yikes!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Perhaps formatting the statement a bit will point out what I think is an issue to deal with:
    Code:
    select substring(sjt.name,1,charindex(':',sjt.name)-1),
    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
    	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' 
    			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
    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
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2004
    Posts
    268
    Thanks. I fixxed it. It works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •