Skip to content
Advertisement

Using SELECT subquery in a CASE statement and handling NULLS

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

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

Advertisement

Answer

Because this query is not finding any records:

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

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?

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement