CODEWITHSUNDEEP
sql-servert-sql
user1800361
user1800361

Reputation:

CASE is not working in SQL Server

I'm trying to write if..else condition inside sql what I want to get the company name from "Company" table if their are companyID in companyID is NULL print "N/A" con someone help me 

SELECT [contactID]
      ,[customerID]
      ,(SELECT Label.labelContactType FROM  Label WHERE Label.labelContactTypeID = Customer_Contacts.labelContactTypeID)AS Type
      ,[contactDetails]
      ,[status]
      ,[notes]
      ,CASE WHEN [Customer_Contacts].companyID = NULL THEN 'N/A'
        WHEN [Customer_Contacts].companyID  <> NULL THEN (SELECT [companyName]
        FROM [TaskManagementSystem_DB].[dbo].[Company] WHERE [Company].companyID = [Customer_Contacts].companyID)
        END AS Company

  FROM [TaskManagementSystem_DB].[dbo].[Customer_Contacts]

  WHERE customerID = 24

Upvotes: 0

Views: 132

Answers (5)

berty
berty

Reputation: 2206

Try using 'IS NULL' instead of '= NULL', and 'IS NOT NULL' instead of '<> NULL'.

Upvotes: 8

DeanGC
DeanGC

Reputation: 680

You don't say what you mean by 'not working'. It is important to say exactly what you mean by 'not working' - returns incorrect results? Throws an error?

In this case, I believe that the problem is that you are testing NULL with '=' and ANSI_NULLS is ON. If you are getting a message like 'Incorrect syntax near the keyword 'then'' and 'Incorrect syntax near the keyword 'as'', then I think this is your problem.

If that is the case, you can do a couple of things. 1. Turn ANSI_NULLS OFF. I don't recommend this. 2. Change = NULL to IS NULL and <> NULL to IS NOT NULL. This is the approach I recommend.

Upvotes: 1

Pavel Hodek
Pavel Hodek

Reputation: 14577

When SET ANSI_NULLS is ON (default and standard setting) you need to compare columns with NULL using a_column IS NULL or a_column IS NOT NULL construct.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) works - but it is nonstandard. I recommend do not alter default setting of ANSI_NULL.

Upvotes: 0

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107508

I would really rewrite this using JOINs: it is more readable, maintainable, and will be more efficient. What you are currently doing is implementing correlated subqueries (two of them; one for the label, one for the company name). Here is your query rewritten with JOINs and reformatted for readability:

SELECT 
    cust.[contactID]
    ,cust.[customerID]
    ,l.labelContactType AS Type
    ,cust.[contactDetails]
    ,cust.[status]
    ,cust.[notes]
    ,ISNULL(comp.companyName, 'N/A') AS Company
FROM 
    [TaskManagementSystem_DB].[dbo].[Customer_Contacts] cust
LEFT JOIN
    [TaskManagementSystem_DB].[dbo].[Label] l
    ON
    cust.labelContactTypeID = l.labelContactTypeID
LEFT JOIN
    [TaskManagementSystem_DB].[dbo].[Company] comp
    ON
    cust.companyID = comp.companyID
WHERE 
    cust.customerID = 24

I would research the JOIN syntax if you are unfamiliar with it.

Upvotes: 1

Taryn
Taryn

Reputation: 247680

Why not just use a JOIN for this query:

SELECT cc.[contactID]
    ,cc.[customerID]
    ,l.labelContactType AS Type
    ,cc.[contactDetails]
    ,cc.[status]
    ,cc.[notes]
    , COALESCE(cp.[companyName], 'N/A') AS Company
FROM [TaskManagementSystem_DB].[dbo].[Customer_Contacts] cc
LEFT JOIN [TaskManagementSystem_DB].[dbo].[Company] cp
    on cc.companyID = cp.companyID
LEFT JOIN Label l
    on cc.labelContactTypeID = l.labelContactTypeID
WHERE cc.customerID = 24

If you need help with join syntax, here is a great visual explanation of joins

Upvotes: 3

Related Questions