May 9, 2008 at 4:47 pm
SELECT sqlserver.Name0, sqlserver.FileVersion AS SQLSERVR, sqlagent90.FileVersion AS SQLAGENT90, msftesql.FileVersion AS MSFTESQL,
msdtssrvr.FileVersion AS MSDTSSRVR, sqlwriter.FileVersion AS SQLWRITER, sqllhelper.FileVersion AS SQLHELPER,
sqlbrowser.FileVersion AS SQLBROWSER, sqlreporting.FileVersion AS SQLREPORTING
FROM (SELECT v_R_System_7.Name0, v_GS_SoftwareFile_7.FileVersion
FROM v_R_System AS v_R_System_7 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_7 ON v_R_System_7.ResourceID = v_GS_SoftwareFile_7.ResourceID
WHERE (v_GS_SoftwareFile_7.FileName = 'ReportingServicesService.exe') AND
(v_GS_SoftwareFile_7.FilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\bin\'))
AS sqlreporting FULL OUTER JOIN
(SELECT v_R_System_6.Name0, v_GS_SoftwareFile_6.FileVersion
FROM v_R_System AS v_R_System_6 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_6 ON v_R_System_6.ResourceID = v_GS_SoftwareFile_6.ResourceID
WHERE (v_GS_SoftwareFile_6.FileName = 'sqlbrowser.exe') AND
(v_GS_SoftwareFile_6.FilePath = 'C:\Program Files\Microsoft SQL Server\90\Shared\')) AS sqlbrowser ON
sqlreporting.Name0 = sqlbrowser.Name0 FULL OUTER JOIN
(SELECT v_R_System_5.Name0, v_GS_SoftwareFile_5.FileVersion
FROM v_R_System AS v_R_System_5 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_5 ON v_R_System_5.ResourceID = v_GS_SoftwareFile_5.ResourceID
WHERE (v_GS_SoftwareFile_5.FileName = 'sqladhlp90.exe') AND
(v_GS_SoftwareFile_5.FilePath = 'C:\Program Files\Microsoft SQL Server\90\Shared\')) AS sqllhelper ON
sqlbrowser.Name0 = sqllhelper.Name0 FULL OUTER JOIN
(SELECT v_R_System_4.Name0, v_GS_SoftwareFile_4.FileVersion
FROM v_R_System AS v_R_System_4 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_4 ON v_R_System_4.ResourceID = v_GS_SoftwareFile_4.ResourceID
WHERE (v_GS_SoftwareFile_4.FileName = 'sqlwriter.exe') AND
(v_GS_SoftwareFile_4.FilePath = 'C:\Program Files\Microsoft SQL Server\90\Shared\')) AS sqlwriter ON
sqllhelper.Name0 = sqlwriter.Name0 FULL OUTER JOIN
(SELECT v_R_System_2.Name0, v_GS_SoftwareFile_2.FileVersion
FROM v_R_System AS v_R_System_2 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_2 ON v_R_System_2.ResourceID = v_GS_SoftwareFile_2.ResourceID
WHERE (v_GS_SoftwareFile_2.FileName = 'msftesql.exe') AND
(v_GS_SoftwareFile_2.FilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\')) AS msftesql FULL OUTER JOIN
(SELECT v_R_System_3.Name0, v_GS_SoftwareFile_3.FileVersion
FROM v_R_System AS v_R_System_3 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_3 ON v_R_System_3.ResourceID = v_GS_SoftwareFile_3.ResourceID
WHERE (v_GS_SoftwareFile_3.FileName = 'MsDtsSrvr.exe') AND
(v_GS_SoftwareFile_3.FilePath = 'C:\Program Files\Microsoft SQL Server\90\DTS\Binn\')) AS msdtssrvr ON
msftesql.Name0 = msdtssrvr.Name0 ON sqlwriter.Name0 = msdtssrvr.Name0 FULL OUTER JOIN
(SELECT v_R_System_1.Name0, v_GS_SoftwareFile_1.FileVersion
FROM v_R_System AS v_R_System_1 LEFT OUTER JOIN
v_GS_SoftwareFile AS v_GS_SoftwareFile_1 ON v_R_System_1.ResourceID = v_GS_SoftwareFile_1.ResourceID
WHERE (v_GS_SoftwareFile_1.FileName = 'sqlagent90.exe') AND
(v_GS_SoftwareFile_1.FilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\')) AS sqlagent90 ON
msftesql.Name0 = sqlagent90.Name0 FULL OUTER JOIN
(SELECT v_R_System.Name0, v_GS_SoftwareFile.FileVersion
FROM v_R_System LEFT OUTER JOIN
v_GS_SoftwareFile ON v_R_System.ResourceID = v_GS_SoftwareFile.ResourceID
WHERE (v_GS_SoftwareFile.FileName = 'sqlservr.exe') AND
(v_GS_SoftwareFile.FilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\')) AS sqlserver ON
sqlagent90.Name0 = sqlserver.Name0
WHERE (sqlserver.Name0 IS NOT NULL)
ORDER BY sqlserver.Name0
May 9, 2008 at 6:07 pm
Henry - was there a question lurking in there somewhere?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 9, 2008 at 7:46 pm
With all those outer joins, I'm not sure how well you can rewrite this. It's kind of a mess right now and it really needs reformatting.
You might try using views to abstract out some of the sub-selects and see if you can make this easier to read.
May 9, 2008 at 9:40 pm
The name for what you are attempting to do is a PIVOT. Please be sure to check the value of the constants as a typo is probable.
SELECTv_R_System.Name0
,MAX ( CASE WHEN v_GS_SoftwareFile.FileName = 'sqlservr.exe'
AND v_GS_SoftwareFile.FilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\'
THEN v_GS_SoftwareFile.FileVersion
ELSE NULL
END ) ) AS SQLSERVR
,MAX ( CASE WHEN v_GS_SoftwareFile.FileName = 'sqlagent90.exe'
AND v_GS_SoftwareFile.FilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\'
THEN v_GS_SoftwareFile.FileVersion
ELSE NULL
END ) ) AS SQLAGENT90
,MAX ( CASE WHEN v_GS_SoftwareFile.FileName = 'MsDtsSrvr.exe'
AND v_GS_SoftwareFile.FilePath = 'C:\Program Files\Microsoft SQL Server\90\DTS\Binn\'
THEN v_GS_SoftwareFile.FileVersion
ELSE NULL
END ) ) AS MSDTSSRVR
,MAX ( CASE WHEN v_GS_SoftwareFile.FileName = 'msftesql.exe'
AND v_GS_SoftwareFile.FilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\')
THEN v_GS_SoftwareFile.FileVersion
ELSE NULL
END ) ) AS MSFTESQL
,MAX ( CASE WHEN v_GS_SoftwareFile.FileName = 'sqlwriter.exe'
AND v_GS_SoftwareFile.FilePath = 'C:\Program Files\Microsoft SQL Server\90\Shared\'
THEN v_GS_SoftwareFile.FileVersion
ELSE NULL
END ) ) AS SQLWRITER
,MAX ( CASE WHEN v_GS_SoftwareFile.FileName = 'sqladhlp90.exe'
AND v_GS_SoftwareFile.FilePath = 'C:\Program Files\Microsoft SQL Server\90\Shared\'
THEN v_GS_SoftwareFile.FileVersion
ELSE NULL
END ) ) AS SQLHELPER
,MAX ( CASE WHEN v_GS_SoftwareFile.FileName = 'sqlbrowser.exe'
AND v_GS_SoftwareFile.FilePath = 'C:\Program Files\Microsoft SQL Server\90\Shared\'
THEN v_GS_SoftwareFile.FileVersion
ELSE NULL
END ) ) AS SQLBROWSER
,MAX ( CASE WHEN v_GS_SoftwareFile.FileName = 'ReportingServicesService.exe'
AND v_GS_SoftwareFile.FilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\bin\'
THEN v_GS_SoftwareFile.FileVersion
ELSE NULL
END ) ) AS SQLREPORTING
FROMv_R_System
JOINv_GS_SoftwareFile
ON v_R_System_7.ResourceID = v_GS_SoftwareFile_7.ResourceID
WHERE v_R_System.Name0 IS NOT NULL
GROUP BY v_R_System.Name0
ORDER BY v_R_System.Name0
SQL = Scarcely Qualifies as a Language
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply