Today we will extend the query to change our query so that the ‘Apply to Child Site’ & ‘Enabled’ display “Yes/No” & “Enabled/Disabled”

When you run yesterday’s query within Query Analyzer you would get something like this.

Now most of use know that 1 = True / Yes / Enabled and 0 = False / No / Disabled, but wouldn’t it be nice if the query would do that for use?

If you use the “CASE” statement we modify the displayed data. If you have done any programming or scripting the basic syntax is not that hard, using our base query this is how to change your query to display “Yes/No”

 case MPR.ApplyToChildSites
 when ‘1’ then ‘True’
 else ‘False’
 end as ‘Apply to Child Site’,

Now your update query looks like this.

 MPR.ProductName AS ‘Name’,
 MPR.OriginalFileName AS ‘Original File Name’,
 MPR.FileName AS ‘File Name’,
 MPR.FileVersion AS ‘Version’,
 MPR.SourceSite AS ‘Site’,
 case MPR.ApplyToChildSites
 when ‘1’ then ‘Yes’
 else ‘No’
 end as ‘Apply to Child Site’,
 case MPR.Enabled
 when ‘1’ then ‘True’
 else ‘False’
 end as ‘Enabled’,
 MPR.SecurityKey as ‘Rule ID’
 v_MeteredProductRule MPR

And our output looks like this

I have only touched on the basic of how to use the “CASE” statement, review the SQL online book for more information.