Replacing NULL Values in Sub-Query

1

I have been banging my head for awhile, someone needs to help me stop before I go unconscious (just kidding).

I am trying to retrieve information using sub-queries. I want to remove the 'NULL' results with '- - -'.

Attached are 4 versions of my ill fated attempts.

What comma or parenth is misplaced.

Thank you Master,
Grasshopper

SELECT DISTINCT
      a.[SystemName],
      a.[AnalysisName],

	--Attempt 1
	(SELECT TOP(1) COALESCE(b.[LimitValue],'- - -')
	FROM [SampleIQ_U13].[CustomReporting].[AnalysisLimits] b
	WHERE		a.[AnalysisID] = b.[AnalysisID]
			AND b.[LimitName] = 'Action Value A'
	ORDER BY b.[EffectiveDate] DESC) AS 'Action A',

	--Attempt 2
	(SELECT TOP(1) ISNULL(b.[LimitValue],'- - -')
	FROM [SampleIQ_U13].[CustomReporting].[AnalysisLimits] b
	WHERE		a.[AnalysisID] = b.[AnalysisID]
			AND b.[LimitName] = 'Action Value B'
	ORDER BY b.[EffectiveDate] DESC) AS 'Action B',

	--Attempt 3
	(SELECT ISNULL((SELECT TOP(1)  b.[LimitValue]
	FROM [SampleIQ_U13].[CustomReporting].[AnalysisLimits] b
	WHERE		a.[AnalysisID] = b.[AnalysisID]
			AND b.[LimitName] = 'Action Value C'
	ORDER BY b.[EffectiveDate] DESC),'- - -')) AS 'Action-C',

	--Attempt 4
	(SELECT TOP(1) CASE
						WHEN b.[LimitValue] IS NULL THEN '- - -'
						ELSE b.[LimitValue]
				   END
	FROM [SampleIQ_U13].[CustomReporting].[AnalysisLimits] b
	WHERE		a.[AnalysisID] = b.[AnalysisID]
			AND b.[LimitName] = 'Action Value D'
	ORDER BY b.[EffectiveDate] DESC) AS 'Action-D'


FROM [DatabaseName].[DataReporting].[AnalysisLimits] a
WHERE
[UnitNumber] IN (1,2,3)
AND [SystemName] LIKE 'SIT %'
AND [AnalysisName] = 'Boron'
ORDER BY [SystemName],[AnalysisName]
2

You can use ISNULL around the Subquery like this example:

DROP TABLE IF EXISTS #Temp;

SELECT 'A' AS Letter
INTO #Temp
UNION
SELECT 'B'
UNION 
SELECT 'C'
UNION 
SELECT 'D';

/* NULL */
SELECT
*, (SELECT Letter FROM #Temp WHERE Letter='E') AS 'E'
FROM #Temp;

/* NOT NULL */
SELECT
*, ISNULL((SELECT Letter FROM #Temp WHERE Letter='E'),'E') AS 'E'
FROM #Temp;
3

I think this will do what you want:


SELECT DISTINCT
      a.[SystemName],
      a.[AnalysisName],
      ISNULL(b.[Action A],'- - -') AS [Action A],
      ISNULL(b.[Action B],'- - -') AS [Action B],
      ISNULL(b.[Action C],'- - -') AS [Action C],
      ISNULL(b.[Action D],'- - -') AS [Action D]
FROM [DatabaseName].[DataReporting].[AnalysisLimits] a
OUTER APPLY (
    SELECT 
        MAX(CASE WHEN LimitName = 'Action Value A' THEN LimitValue END) 
            AS [Action A],
        MAX(CASE WHEN LimitName = 'Action Value B' THEN LimitValue END) 
            AS [Action B],
        MAX(CASE WHEN LimitName = 'Action Value C' THEN LimitValue END) 
            AS [Action C],
        MAX(CASE WHEN LimitName = 'Action Value D' THEN LimitValue END) 
            AS [Action D]
    FROM (
        SELECT b.LimitName, b.LimitValue, ROW_NUMBER() OVER(
            PARTITION BY b.LimitName ORDER BY b.EffectiveDate DESC) AS row_num
	    FROM [SampleIQ_U13].[CustomReporting].[AnalysisLimits] b
        WHERE A.AnalysisID = b.AnalysisID
          AND b.[LimitName] IN ( 'Action Value A', 'Action Value B', 
              'Action Value C', 'Action Value D' ) 
    ) AS b
    WHERE row_num = 1
) AS b
WHERE
a.[UnitNumber] IN (1,2,3)
AND a.[SystemName] LIKE 'SIT %'
AND a.[AnalysisName] = 'Boron'
ORDER BY [SystemName],[AnalysisName]
4

So, as I attempted to resolve, I concluded that when assigning a calculated field, the first value assigned to the field, SQL automatically assigns the DataType.

So initially, my calculated field took float + ( float * float / 100 ), assigning the calculated field as a float, which was causing error for the 'else' portion of the case, which was assigning '- - -' to the field and causing a data type error.

To avoid this, I am performing the code below, which provides the expected results...

CASE WHEN [Operator] = 'Sigma' AND [UseStandardForLimit] = 1 THEN CONVERT(VARCHAR(MAX), [QCStandard] + ( 2 * [StdDev] ))
         WHEN [Operator] = 'Sigma' AND [UseMeanForLimit] = 1 THEN CONVERT(VARCHAR(MAX), [Mean]       + ( 2 * [StdDev] ))
		 ELSE '- - -' END AS '+2 Sigma',