Software Version Compatibility Report (Solved)

[brandon_jones]

Edit: I figured out the code. I decided to go a different way. If the software was compatible I set the background to green, if not I set it to red. Here is a sample of the query:

When tblOperatingsystem.Version Like '10.0.18362%' And
(tblSoftware.softwareVersion Like '5.4.%' Or
tblSoftware.softwareVersion Like '5.3.%' Or
tblSoftware.softwareVersion Like '5.2.%' Or
tblSoftware.softwareVersion Like '5.1.%' Or
tblSoftware.softwareVersion Like '5.0.%') Then '#d4f4be'
When tblOperatingsystem.Version Like '10.0.19041%' And
(tblSoftware.softwareVersion Like '5.4.%') Then '#d4f4be'
Else '#ffadad'



I am trying to write a report for software the we use named Horizion VMware Client that will tell me what versions are compatible with what versions of windows. Below is my code:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblOperatingsystem.Version As Build,
Case
When tblOperatingsystem.Version Like '10.0.10240' Then '1507'
When tblOperatingsystem.Version Like '10.0.10586' Then '1511'
When tblOperatingsystem.Version Like '10.0.14393' Then '1607'
When tblOperatingsystem.Version Like '10.0.15063' Then '1703'
When tblOperatingsystem.Version Like '10.0.16299' Then '1709'
When tblOperatingsystem.Version Like '10.0.17134' Then '1803'
When tblOperatingsystem.Version Like '10.0.17763' Then '1809'
When tblOperatingsystem.Version Like '10.0.18362' Then '1903'
When tblOperatingsystem.Version Like '10.0.18363' Then '1909'
When tblOperatingsystem.Version Like '10.0.19041' Then '2004'
End version,
Case
When tblOperatingsystem.Version Like '1607' And
tblSoftware.softwareVersion Like '5.4.%' Or
tblSoftware.softwareVersion Like '5.3.%' Or
tblSoftware.softwareVersion Like '5.2.%' Or
tblSoftware.softwareVersion Like '5.1.%' Or
tblSoftware.softwareVersion Like '5.0.%' Or
tblSoftware.softwareVersion Like '4.1.%' Or
tblSoftware.softwareVersion Like '4.9.%' Or
tblSoftware.softwareVersion Like '4.8.%' Or
tblSoftware.softwareVersion Like '4.7.%' Or
tblSoftware.softwareVersion Like '4.6.%' Then 'Not Compatable'
End compat3,
Case
When tblOperatingsystem.Version Like '1809' And
tblSoftware.softwareVersion Like '5.4.%' Or
tblSoftware.softwareVersion Like '4.8.%' Or
tblSoftware.softwareVersion Like '4.7.%' Or
tblSoftware.softwareVersion Like '4.6.%' Or
tblSoftware.softwareVersion Like '4.5.%' Or
tblSoftware.softwareVersion Like '4.4.%' Then 'Not Compatable'
End compat2,
tblSoftware.softwareVersion,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblSoftwareUni.softwareName = 'vmware horizon client' And
tblOperatingsystem.Caption = 'Microsoft Windows 10 Pro' And
tblAssetCustom.State = 1

For some reason the first case statement is putting not compatible on the Windows 1809 as well as 1607. Any ideas?

[RCorbeil]

You also shouldn't need to set up a special CASE to determine the Windows 10 version. See this thread. You should be able to find the version number in tblAssets.Version.

[RCorbeil]

Logical operators have an order of precedence just like mathemetical operators. AND takes precedence over OR, so you CASE statement is being evaluated as:

Case
  When (tblOperatingsystem.Version Like '1809' And tblSoftware.softwareVersion Like '5.4.%')
        Or tblSoftware.softwareVersion Like '4.8.%'
        Or tblSoftware.softwareVersion Like '4.7.%'
        Or tblSoftware.softwareVersion Like '4.6.%'
        Or tblSoftware.softwareVersion Like '4.5.%'
        Or tblSoftware.softwareVersion Like '4.4.%'
  Then 'Not Compatable'
End compat2,

Just as you can force A + B * C to be evaluated as (A + B) * C by using parentheses, you can do the same with logical operators. Block the version check in parentheses and you should get the results you're expecting:

Case
  When tblOperatingsystem.Version Like '1809'
       And (tblSoftware.softwareVersion Like '5.4.%'
            Or tblSoftware.softwareVersion Like '4.8.%'
            Or tblSoftware.softwareVersion Like '4.7.%'
            Or tblSoftware.softwareVersion Like '4.6.%'
            Or tblSoftware.softwareVersion Like '4.5.%'
            Or tblSoftware.softwareVersion Like '4.4.%'
           )
   Then 'Not Compatable'
End compat2,