CODEWITHSUNDEEP
sqlsql-server
Tsukasa
Tsukasa

Reputation: 6552

Return string when no results

What is the correct way to return a string when no results are returned. The following isn't working

SELECT 
TOP 1 CASE
WHEN
    CustomerName IS NULL
        THEN 
            'Unknown'
WHEN
    CustomerName = ''
        THEN
            'Unknown'
ELSE 
    CustomerName
END
AS
CustomerName FROM CUSTOMER WHERE CustomerCode = 222

Upvotes: 0

Views: 551

Answers (3)

Anon
Anon

Reputation: 10908

If I understand the question, you want to return a value even when the WHERE clause doesn't match, and you want 'Unknown' to replace the empty string or a NULL value:

SELECT TOP 1 COALESCE(NULLIF(CustomerName,''),'Unknown')
FROM (
  SELECT CustomerName FROM CUSTOMER WHERE CustomerCode = 222
  UNION ALL
  SELECT NULL
) t
ORDER BY CustomerName DESC

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32392

It seems you want to return Unknown when there are no rows in your table that have CustomerName that's not NULL or not ''.

SELECT COALESCE((SELECT TOP 1 CustomerName FROM 
            CUSTOMER WHERE CustomerCode = 222
            AND CustomerName IS NOT NULL
            AND CustomerName <> ''),'Unknown') CustomerName

Upvotes: 1

Harshil
Harshil

Reputation: 411

Try this may be it's work..

    SELECT DISTINCT TOP 1 CASE WHEN ISNULL(CustomerName,'') <>'' THEN CustomerName ELSE 'Unknown'
 END as CustomerName FROM CUSTOMER WHERE CustomerCode = 222

or 

SELECT TOP 1 CustomerName FROM 
(SELECT DISTINCT TOP 1 CASE WHEN ISNULL(CustomerName,'') <>'' THEN CustomerName ELSE 'Unknown'
 END as CustomerName ,1 No FROM CUSTOMER WHERE CustomerCode = 222
UNION 
SELECT 'Unknown',2)
AS T ORDER BY No

Upvotes: -1

Related Questions