-2

Edit: I figured out the code. I decided to go a totaly different way with it. IF the software is comptible I make the background green, if not compatible I made the background red.

Here is a sample of the query:

Case When tblOperatingsystem.Version Like '10.0.17763%' And (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.10.%' Or tblSoftware.softwareVersion Like '4.9%') Then '#d4f4be'

At the very end of all the case statments I had a line the stated Else '#ffadad' which turned all other lines red. That for all the input.

I am writing a report that will display what versions of a particular software is compatible with which versions of Windows 10. I have matrix that was give to me by the vendor. Below is part of my query.

In the first case when I am linking the Windows version with the build number to make the results easier to read.

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 second case statement is putting not compatible on the Windows 1809 as well as 1607. The first case should not even be concerned with 1809. Any ideas?

Here is some sample data:

AssestName Build        SoftwareVersion SoftwareName
uwshiswr6  10.0.17763   5.3.0.8987        Vmware Horizon Client

Results

AssestName  Build    Version     Compat3      compat2 software version   software name
uwshiswr6   1809      10.0.17763  Compatible           5.3.0.8987          Vmware
ultufmcart1  1607      10.0.14393             Not compatible 5.3.0.897     vmware
7
  • You need some parens around all the OR'ed SoftwareVersion criteria
    – AMtwo
    Jul 13 '20 at 1:38
  • I tried this and the columns are now blank: Case When tblOperatingsystem.Version Like '1607' And (tblSoftware.softwareVersion Like '5.4.%' Or ... tblSoftware.softwareVersion Like '4.6.%') Jul 13 '20 at 2:37
  • You only have the single WHEN in that CASE so when the criteria isn't met, it will be blank. For the row where OS version is 1607, does the software version match one of those criteria?
    – AMtwo
    Jul 13 '20 at 2:52
  • I wasn’t able to post the whole query. I was told it was too long. The query will be the same as the original post. Yes at least one row matches the criteria. Jul 13 '20 at 3:08
  • 1
    If this was me I'd put all those version numbers in a table and then join it - that way it wouldn't require a rewrite when a new version number is released - just to insert a row into a table. Jul 13 '20 at 15:07
1

You use a CASE expression that you alias as version. Based on that CASE expression, the data in the version column of tblOperatingsystem has data like '10.0.14393'.

In your compat2 and compat3 CASE expressions, you reference tblOperatingsystem.version, comparing it to a value LIKE '1809'. 1809 is the value in the output of your CASE expression, not the value of tblOperatingsystem.version.

In your compat2 and compat3 CASE expressions, you should reference the actual column data in the format they are stored (ex 10.0.14393) rather than your computed version. I'd also recommend avoiding a column alias that matches a column name, in order to avoid confusion like this.

Addintionaly, you lack parentheses around your ORed criteria. As written compat2, would be evaluated like this, with the first two values locked together with an AND but the rest of the ORed valued evaluated independently:

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,

Lastly, from your expected output, it looks like you're expecting "Compatible" to be included in the output of the compat2 and compat3 CASE expressions, but you don't specify that in the criteria--you just have a single "Not Compatible" value for the single criteria. Presumably, you'll need to add ELSE 'Compatible' to those expressions.

If you add parentheses in the right place, and use the proper value for OS version (as stored in the database), and the ELSE catch-all, a query like this would work:

(Note, I've also added table aliases & used them consistently to make the code a little easier (for me) to read.)

SELECT TOP 1000000 
    a.AssetID,
    a.AssetName,
    t.AssetTypeIcon10 AS icon,
    os.Version AS Build,
    CASE
        WHEN os.Version = '10.0.10240' THEN '1507'
        WHEN os.Version = '10.0.10586' THEN '1511'
        WHEN os.Version = '10.0.14393' THEN '1607'
        WHEN os.Version = '10.0.15063' THEN '1703'
        WHEN os.Version = '10.0.16299' THEN '1709'
        WHEN os.Version = '10.0.17134' THEN '1803'
        WHEN os.Version = '10.0.17763' THEN '1809'
        WHEN os.Version = '10.0.18362' THEN '1903'
        WHEN os.Version = '10.0.18363' THEN '1909'
        WHEN os.Version = '10.0.19041' THEN '2004'
    END OsVersion,
    CASE
        WHEN os.Version = '10.0.14393' 
            AND (
                s.softwareVersion LIKE '5.4.%' OR
                s.softwareVersion LIKE '5.3.%' OR
                s.softwareVersion LIKE '5.2.%' OR
                s.softwareVersion LIKE '5.1.%' OR
                s.softwareVersion LIKE '5.0.%' OR
                s.softwareVersion LIKE '4.1.%' OR
                s.softwareVersion LIKE '4.9.%' OR
                s.softwareVersion LIKE '4.8.%' OR
                s.softwareVersion LIKE '4.7.%' OR
                s.softwareVersion LIKE '4.6.%' 
            )
        THEN 'Not Compatable'
        ELSE 'Compatible'
    END compat3,
    CASE
        WHEN os.Version = '10.0.17763' 
            AND (
                s.softwareVersion LIKE '5.4.%' OR
                s.softwareVersion LIKE '4.8.%' OR
                s.softwareVersion LIKE '4.7.%' OR
                s.softwareVersion LIKE '4.6.%' OR
                s.softwareVersion LIKE '4.5.%' OR
                s.softwareVersion LIKE '4.4.%'
            )
        THEN 'Not Compatable'
        ELSE 'Compatible'
    END compat2,
    s.softwareVersion,
    su.softwareName
FROM tblAssets AS a
INNER JOIN tblAssetCustom AS ac ON a.AssetID = ac.AssetID
INNER JOIN tsysAssetTypes AS t ON t.AssetType = a.Assettype
INNER JOIN tblSoftware AS s ON a.AssetID = s.AssetID
INNER JOIN tblSoftwareUni AS su ON su.SoftID = s.softID
INNER JOIN tblOperatingsystem AS os ON a.AssetID = os.AssetID
WHERE su.softwareName = 'vmware horizon client' 
AND os.Caption = 'Microsoft Windows 10 Pro' 
AND ac.State = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.