Help adding another case to subquery. I am trying to pull the bin label and warehouse but I am having trouble adding another case to subquery?

Transact-SQL
1

SELECT CASE WHEN EXISTS(SELECT binlabel
FROM binmstr with (nolock)
WHERE empty='Y') THEN (SELECT WAREHOUSE FROM binmstr with (nolock)
WHERE empty='Y' and binlabel not like '#%'
ORDER BY WAREHOUSE)
ELSE 'NOMORE'
END as binlabel

2

Maybe?:


SELECT ISNULL(bm.binlabel, notfound.binlabel) AS binlabel, 
    ISNULL(bm.warehouse, notfound.warehouse) AS warehouse
FROM (
    SELECT binlabel, warehouse
    FROM binmstr with (nolock)
    WHERE empty='Y' and binlabel not like '#%'
) AS bm
LEFT OUTER JOIN (
    SELECT 'NOMORE' AS binlabel, 'NOMORE' AS warehouse
) AS notfound ON bm.binlabel IS NULL
ORDER BY bm.warehouse, bm.binlabel
1 Like