Monday, January 12, 2015

SQL: CASE and CONCAT

SO I was trying to build a better report for TSM Client levels replacing the crappy windows OS level with the correct version using CASE but was worried that case with two fields being concatenated would work. Well it does and quite well. The only issue was that if the platform_name is longer than the varchar setting then you will receive a warning error at the end of the select (the select runs successfully but will truncate any results for platform_name which is easily fixed).

select case -
  when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 5.00' then 'WinNT 2000' -
  when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 5.02' then 'WinNT 2003' -
  when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.00' then 'WinNT 2008' -
  when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.01' then 'WinNT 2008 R2' -
  when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.02' then 'WinNT 2012' -
  when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 6.03' then 'WinNT 2012 R2' -
  else varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) -
end -
  AS platform_name, -
cast(client_version as char(1)) || '.' || cast(client_release as char(1)) || '.' || cast(client_level as char(1)) || '.' || cast(client_sublevel as char(1)) as TSM_Version, count(distinct tcp_name) AS COUNT from nodes where LASTACC_TIME>(CURRENT_TIMESTAMP - 70 DAYS) and node_name like '%SU%' group by platform_name, client_os_level, client_version, client_release, client_level, client_sublevel


The results were exactly what I wanted.

PLATFORM_NAME          TSM_VERSION           COUNT
------------------     -----------     -----------
SUN SOLARIS 5.9        5.2.2.0                   4
WinNT 2000             5.3.0.0                   1
WinNT 2000             5.3.6.0                   1
WinNT 2003             5.3.0.0                   4
WinNT 2003             5.3.2.0                   6
WinNT 2003             5.3.4.0                   6
WinNT 2003             5.4.0.2                   3
WinNT 2003             5.4.1.4                   2
WinNT 2003             5.4.2.0                   2
WinNT 2003             5.4.3.0                   2
WinNT 2003             5.5.0.4                   8
WinNT 2003             5.5.1.0                   1
WinNT 2003             5.5.2.0                   1
WinNT 2003             5.5.3.0                   2
WinNT 2003             6.1.3.0                   1
WinNT 2008             5.5.0.4                   1
WinNT 2008 R2          6.1.4.0                   3
WinNT 2008 R2          6.2.4.0                   2
WinNT 2008 R2          6.3.0.0                   2
WinNT 2012             6.4.1.0                   1

6 comments:

  1. Great select. I'm trying to modify it so that I can get, for example, all the Windows 2003 clients sorted by distinct node name. Should be easy but I'm banging my head against the wall.

    ReplyDelete
  2. Post your script. If all you want is distinct node name then you have to remove the count and the group by wont be needed either.

    ReplyDelete
  3. This gets me most of the way there but leaves gaps that I don't need.

    select case -
    when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 5.02' then 'WinNT 2003' -
    end -
    AS platform_name, node_name from nodes

    ReplyDelete
  4. select case-
    when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 5.02' then 'WinNT 2003'-
    end -
    AS platform_name, node_name from nodes

    This gives me the information but there are big gaps because it also includes all the non Windows 2003 clients

    ReplyDelete
  5. select case-
    when varchar(platform_name,10) || ' ' || cast(client_os_level as char(14)) ='WinNT 5.02' then 'WinNT 2003'-
    end -
    AS platform_name, node_name from nodes where platform_name='WinNT' and client_os_level=5.02

    ReplyDelete
  6. Many thanks. I figured it was something simple!

    ReplyDelete