Using EXISTS in Sub-Query

1

Still have my training wheels on, sorry.

I am using sub-queries to provide the needed information. When I receive null values, I want to return a specific string to tell that the parameter is 'Not Defined' (instead of NULL).

I have tried the following, but the command EXISTS is flagged as being improper.

DECLARE @SystemString VARCHAR(MAX) = 'Gas - Iso'

SELECT DISTINCT
MnTbl.AnalysisName AS 'ALL',

(SELECT 
CASE EXISTS (SELECT Tbl2.AnalysisName FROM SampleIQ_U13.SampleIQ_App.AnalysisDefinitions AS Tbl2 
	WHERE Tbl2.SystemName = MnTbl.SystemName AND Tbl2.AnalysisName = MnTbl.AnalysisName AND Tbl2.UnitNumber = 1)
	THEN IIF(Tbl2.Deactivated=1,CONCAT('***',Tbl2.AnalysisName),Tbl2.AnalysisName)
	ELSE ISNULL(Tbl2.AnalysisName,'Not Defined') 
END) AS 'Unit 1',

(SELECT TOP(1) CASE 
	WHEN Tbl2.Deactivated= 1 		THEN CONCAT('***',Tbl2.AnalysisName)			-- Returns as expected
	WHEN Tbl2.AnalysisName IS NULL	THEN ISNULL(Tbl2.AnalysisName,'Not Defined')	-- DOES NOT RETURN
	ELSE Tbl2.AnalysisName END
FROM SampleIQ_U13.SampleIQ_App.AnalysisDefinitions AS Tbl2
WHERE Tbl2.SystemName = MnTbl.SystemName AND Tbl2.AnalysisName = MnTbl.AnalysisName AND Tbl2.UnitNumber = 2) AS 'Unit 2',

(SELECT TOP(1) CASE 
	WHEN Tbl2.Deactivated= 1 		THEN CONCAT('***',Tbl2.AnalysisName)			-- Returns as expected
	WHEN Tbl2.AnalysisName IS NULL	THEN ISNULL(Tbl2.AnalysisName,'Not Defined')	-- DOES NOT RETURN
	ELSE Tbl2.AnalysisName END
FROM SampleIQ_U13.SampleIQ_App.AnalysisDefinitions AS Tbl2
WHERE Tbl2.SystemName = MnTbl.SystemName AND Tbl2.AnalysisName = MnTbl.AnalysisName AND Tbl2.UnitNumber = 3) AS 'Unit 3'

FROM
SampleIQ_U13.SampleIQ_App.AnalysisDefinitions AS MnTbl
WHERE
MnTbl.[UnitNumber] IN (1,2,3) AND
MnTbl.[SystemDeactivated] = 0 AND
MnTbl.[UnitDeactivated] = 0 AND
MnTbl.[SystemName] LIKE @SystemString AND
MnTbl.[AnalysisName] LIKE '%-%'
ORDER BY
[AnalysisName]

2

The syntax is CASE WHEN EXISTS. WHEN is part of the Case expression and EXISTS is a boolean operator that returns True or False based on whether there is 0 or > 0 rows in the output)

3

Likely this is what you want/need:

(SELECT 
    ISNULL((SELECT CASE WHEN Tbl2.Deactivated=1 THEN '****' ELSE '' END + 
    Tbl2.AnalysisName FROM SampleIQ_U13.SampleIQ_App.AnalysisDefinitions AS Tbl2 
	WHERE Tbl2.SystemName = MnTbl.SystemName AND Tbl2.AnalysisName = 
    MnTbl.AnalysisName AND Tbl2.UnitNumber = 1), 'Not Defined') 
) AS 'Unit 1'
4

T-H-A-N-K Y-O-U !!!

That worked precisely how I needed it to.