Using SELECT subquery in a CASE statement and handling NULLS

Question

I have 2 SQL tables one holding stock information then a style table which details how the stock is used. For some jobs the information is contained with a print file so the value in the stock table would be BIN, if not it would contain a template name which would cause a query to a Styles table which then shows how the stock is used. There a 10 positions that a stock could be applied but the template table wouldn't have records for where a stock couldn't be used (on the back of a simplex job for example).

I have a stored procedure which works to provide the detail based on whether the logic value is BIN or not but for the records in the style table that have no value I get NULL back which I need to suppress but just putting ISNULL around the column name in the subquery isn't having any effect. Am I just missing something as I'd rather not nest in another CASE statement to check if the query would produce NULL

WITH STYLES AS 
(
    SELECT 
        @JOBNAME AS JobName, Logic AS StyleName 
    FROM 
        [dbo].[CON_Tbl_511_DigitalStocks]
    WHERE  
        JOBNAME = @JOBNAME
)
SELECT TOP 1 
    [Logic], 
    [S1F], [S1B], [S2F], [S2B], 
    [S3F], [S3B], [S4F], [S4B],
    [S5F], [S5B],
    CASE 
        WHEN b.stylename = 'BIN' 
            THEN --checks if there is a style for this job
                CASE 
                    WHEN S1F = '' THEN '' 
                    ELSE '1' 
                END -- if a stock code is specified then return the bin name ("1")
            ELSE (SELECT PAGE 
                  FROM [dbo].[CON_Tbl_512_DigitalLogic] 
                  WHERE STYLENAME = B.StyleName 
                    AND STOCKREF = 'S1F') 
    END AS S1F_LOGIC,    -- If a style is used return the style instruction for this bin and side 
    CASE 
        WHEN b.stylename = 'BIN' -- repeat this for all bins/sides
            THEN 
                CASE WHEN S1B = '' THEN ''
                ELSE '1' 
            END 
            ELSE (SELECT PAGE 
                  FROM [dbo].[CON_Tbl_512_DigitalLogic] 
                  WHERE STYLENAME = B.StyleName AND STOCKREF = 'S1B') 
    END AS S1B_LOGIC,
    CASE WHEN b.stylename = 'BIN' THEN 
            CASE WHEN S2F = '' THEN ''
                ELSE '2' END 
            ELSE  (SELECT PAGE FROM [dbo].[CON_Tbl_512_DigitalLogic] WHERE STYLENAME = B.StyleName AND STOCKREF = 'S2F') END AS S2F_LOGIC -- this one returns NULL as there is no instruction required for "2SF"
FROM 
    [CON_Tbl_511_DigitalStocks] A 
JOIN 
    STYLES B ON A.JOBNAME = B.JOBNAME
WHERE 
    A.JobName = @JobName

This code works fine until the last one as there is no value in the stockref column that says 'S2F' but putting SELECT ISNULL(PAGE, '') still returns NULL

Use something like `isnull((select ....), '') as ...` – Arvo Sep 16 '22 at 13:36 — Arvo, Sep 16 '22 at 13:36

score 1 · Accepted Answer · answered Sep 16 '22 at 13:36

Because this query is not finding any records:

SELECT PAGE FROM [dbo].[CON_Tbl_512_DigitalLogic] WHERE STYLENAME = B.StyleName AND STOCKREF = 'S2F'

and you want it to return something, you can change it to:

SELECT coalesce(min(PAGE),'') FROM [dbo].[CON_Tbl_512_DigitalLogic] WHERE STYLENAME = B.StyleName AND STOCKREF = 'S2F'

This will return the minimum value (min) of the found records, which will return NULL when noting found. The coalesce will turn this NULL into the empty string: ''

P.S.: see also SQL - Difference between COALESCE and ISNULL?

Using SELECT subquery in a CASE statement and handling NULLS

1 Answers1