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
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
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?