So while doing some script maintenance on a TSM server I came across this select statement and was shocked to see the CASE option in a select. I've used it in shell scripts but didn't think TSM allowed for it. Well it does and it works. Go figure! (Runs on a TSM 5.3.5.2 server).
/* ---------------------------------------------*/
/* Script Name: eventx */
/* ---------------------------------------------*/
select -
schedule_name as "Schedule Name ", -
cast(substring(cast(scheduled_start as character(26)) from 12 for 8) as char(8)) as "Sched", -
node_name, -
cast(status as char(10)) as "Status", -
case -
when result=0 then ' 0-Succ' -
when result=4 then ' 4-SkFi' -
when result=8 then ' 8-Warn' -
when result=12 then '12-Errs' -
else cast(result as char(7)) -
end -
as "Result" -
from events -
where current_timestamp-scheduled_start<interval '24' hours -
and result<>0
/* Script Name: eventx */
/* ---------------------------------------------*/
select -
schedule_name as "Schedule Name ", -
cast(substring(cast(scheduled_start as character(26)) from 12 for 8) as char(8)) as "Sched", -
node_name, -
cast(status as char(10)) as "Status", -
case -
when result=0 then ' 0-Succ' -
when result=4 then ' 4-SkFi' -
when result=8 then ' 8-Warn' -
when result=12 then '12-Errs' -
else cast(result as char(7)) -
end -
as "Result" -
from events -
where current_timestamp-scheduled_start<interval '24' hours -
and result<>0
I saw this and a whole new world opened up before my eyes! The possibilities!
I was pretty surprised by this too, good spot Chad!
ReplyDeleteHowever, a colleague just tried this on a 6.2.1 server - didn't get such a favourable response:
ANR0162W Supplemental database diagnostic information: -1:42601:-104
([IBM][CLI Driver][DB2/SUN64] SQL0104N An unexpected token "for 8" was found
following "ter ( 26 ) ) from 12". Expected tokens may include:
"". SQLSTATE=42601
).
ANR0516E SQL processing for statement select schedule_name as "Schedule Name "
, cast ( substring ( cast ( scheduled_start as character ( 26 ) ) from 12 for 8
) as char ( 8 ) ) as "Sched" , node_name , cast ( status as char ( 10 ) ) as
"Status" , case when result = 0 then ' 0-Succ' when result = 4 then ' 4-SkFi'
when result = 8 then ' 8-Warn' when result = 12 then '12-Errs' else cast (
result as char ( 7 ) ) end as "Result" from events where
current_timestamp-scheduled_start < 24 hours and result <> 0 failed.
ANS8001I Return code 3.