CODEWITHSUNDEEP
sqlsql-serversql-server-2012
ramandeep
ramandeep

Reputation: 31

SQL Computed column

I am facing an issue: I have written a query and got the desired results but now my need arises that I have to add a column which can be calculated by query results.

I want the new column to have the condition that if CAT6 is A then its good if B then BETTER IF C THEN BAD

My query:

Select 
    MSLADH.UNIQUE_KEY, MSLADH.ASMNAME, MslADH.DBCode, 
    MslADH.RetCode, CATEGORY.CAT2, CATEGORY.CAT6,  
    MslADH.SKUCode, MslADH.L3M, CPC.[CPC Code]
From 
    MslADH
Left Join 
    CATEGORY ON MSLADH.UNIQUE_KEY = CATEGORY.OutletCode
Left Join 
    CPC on MslADH.SKUCode=CPC.ItemCode
Where 
    L3M <> 0  
    AND CAT2 IS NOT NULL 
    AND CAT6 IS NOT NULL 
    AND [CPC Code] IS NOT NULL

Upvotes: 2

Views: 73

Answers (1)

Matt
Matt

Reputation: 15061

Use a case statement to generate new column, i assumed that if non of your criteria is met then the column is null.

SELECT m.UNIQUE_KEY, m.ASMNAME, m.DBCode, m.RetCode, c.CAT2, c.CAT6,  m.SKUCode, m.L3M, p.[CPC Code], 
CASE WHEN c.CAT6 = 'A' THEN 'Good' WHEN c.CAT6 = 'B' THEN 'Better' WHEN c.CAT6 = 'C' THEN 'Bad' ELSE NULL END AS NEWCOLUMN
FROM MslADH m
LEFT JOIN CATEGORY c ON m.UNIQUE_KEY = c.OutletCode
LEFT JOIN CPC p OIN m.SKUCode = p.ItemCode
WHERE m.L3M != 0 
AND c.CAT2 IS NOT NULL 
AND c.CAT6 IS NOT NULL 
AND p.[CPC Code] IS NOT NULL

Upvotes: 1

Related Questions