If-Else Statement in T-SQL

Question

I'm trying to alter the results of a Transact-SQL (MS SQL Server) report that is currently showing:

CompanyA     DVCE3
CompanyB     DVCE2
CompanyC     DVCE3
CompanyD     NULL 
CompanyE     DVCE3

and I would like it instead to show:

CompanyA     36
CompanyB     24
CompanyC     36
CompanyD      
CompanyE     36

I'm sure the answer to this is to use some kind of if/else statement or a case statement, but I'm not sure of the syntax of this in T-SQL or where it should go in my query.

My basic query looks like this:

SELECT
   companyName
   , term
FROM tableA a
   JOIN tableB b on a.id = b.id ...
WHERE 
   customerNumber IS NOT NULL
   AND companyName != 'TEST'... ;

Thanks for your help.

Answer 1

If you only have a few replacements you could stick them in a case statement as below.

SELECT
   companyName
   ,CASE term WHEN  'DVCE3' THEN '36' WHEN 'DVCE2' THEN '24' ELSE '' END AS term
FROM tableA a
   JOIN tableB b on a.id = b.id ...
WHERE 
   customerNumber IS NOT NULL
   AND companyName != 'TEST'... ;

If you have more or you will be repeating this logic in other queries you could maybe set up a mapping table (either a permanent table, view, or an inline TVF) that you can then join onto.

Answer 2

Replace your "term" field with the following:

CASE WHEN term='DVCE3' THEN '36' WHEN term='DVCE2' THEN '24' ELSE '' END as term

I'll rewrite it for readability:

CASE
    WHEN term='DVCE3' THEN '36'
    WHEN term='DVCE2' THEN '24'
    ELSE ''
END as term

Answer 3

A Case statement will work. However a better answer might be to place that data into a table and do a join or just place it into a field in your company table. This makes it MUCH more extensible in the long run.

CREATE TABLE CompanyTerms (
    id INT,
    CompanyName NVARCHAR(100) NOT NULL, 
    Term NVARCHAR(100) NULL
)

...

SELECT
   companyName
   , c.Term
FROM tableA a
   JOIN tableB b on a.id = b.id ...
   JOIN CompanyTerm c ON c.id = a.id
WHERE 
   customerNumber IS NOT NULL
   AND companyName != 'TEST'... ;