Max Max - 1 month ago 10
SQL Question

how to not display the NULL rows when using CASE When?

Here is a sample code:

select i.computerguid, i.logfile_enabled, i.logfile_logExtFileFlags
,Case
When
CHARINDEX('Date' , i.logfile_logExtFileFlags) = 0 or
CHARINDEX('Time' , i.logfile_logExtFileFlags) = 0 or
CHARINDEX('ClientIP', i.logfile_logExtFileFlags) = 0
Then 'Need these W3SVC fields enabled by default: Date, Time, ClientIP'
End as 'Reason'
,Case When i.logfile_enabled = 'true' Then 'Enabled the IIS log fields (via IIS MMC console) for site: ' + i.sitename End as 'Remediation'
from aspr_iissite i inner join aspr_root r on r.computerguid = i.computerguid
where i.logfile_enabled = 'true'


Here is a screen shot, I dont want to display the rows when 'Reason' is NULL:
enter image description here

Thank you

Answer

In your case, it is easiest to use a subquery:

select *
from (select i.computerguid, i.logfile_enabled, i.logfile_logExtFileFlags,
             (Case When CHARINDEX('Date'    , i.logfile_logExtFileFlags) = 0 or
                        CHARINDEX('Time'    , i.logfile_logExtFileFlags) = 0 or
                        CHARINDEX('ClientIP', i.logfile_logExtFileFlags) = 0                
                   Then 'Need these W3SVC fields enabled by default: Date, Time, ClientIP' 
              End) as Reason,
             (Case When i.logfile_enabled = 'true'
                   Then 'Enabled the IIS log fields (via IIS MMC console) for site: ' + i.sitename
              End) as Remediation
      from aspr_iissite i inner join
           aspr_root r
           on r.computerguid = i.computerguid 
      where i.logfile_enabled = 'true'
     ) ir
where Reason is not null;

You could repeat the conditions in the case statement, but that makes the code harder to modify and maintain.

Two notes:

  • I think it is easier to follow the conditional logic using like: i.logfile_logExtFileFlags not like '%Date%'`. This is standard SQL.
  • Only use single quotes for string and date constants. Don't use them for column aliases.