CODEWITHSUNDEEP
sqlsql-servert-sql
Riz
Riz

Reputation: 6676

how do I select a column based on condition?

I have a variable called @status which I set before this select statement:

Select
ordr_num as num,
ordr_date as date,
ordr_ship_with as shipwith
From
order
where ordr_num = @ordrNum

I only want to select ordr_ship_with column if @status <> 'Cancelled', otherwise I want to select null for shipwith. How do I accomplish this?

Upvotes: 43

Views: 153146

Answers (5)

Russell Steen
Russell Steen

Reputation: 6612

Try this 

SELECT CASE     
 WHEN @status <> 'cancelled' THEN ordr_ship_with  
 ELSE null  
END AS shipwith, ... other fields

Upvotes: 1

gbn
gbn

Reputation: 432180

Select
    ordr_num as num,
    ordr_date as date,
    CASE WHEN @status <> 'Cancelled' THEN ordr_ship_with ELSE NULL END as shipwith
From
    order where ordr_num = @ordrNum

Upvotes: 4

Stephen Lacy
Stephen Lacy

Reputation:

Use Case : http://msdn.microsoft.com/en-us/library/ms181765.aspx

Upvotes: 2

Raj More
Raj More

Reputation: 48018

Try this out

Select 
    ordr_num as num, 
    ordr_date as date, 
    CASE 
        WHEN @Status <> 'Cancelled' THEN ordr_ship_with 
        ELSE NULL END
    as shipwith 
From order 
where ordr_num = @ordrNum

Although I have a feeling that you STATUS is an actual column in the Order table. In that case, do this:

Select 
    ordr_num as num, 
    ordr_date as date, 
    CASE 
        WHEN Status <> 'Cancelled' THEN ordr_ship_with 
        ELSE NULL END
    as shipwith 
From order 
where ordr_num = @ordrNum

Upvotes: 5

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

SELECT ordr_num as num, ordr_date as date, 
    CASE WHEN @status<>'Cancelled' THEN ordr_ship_with ELSE NULL END as shipwith 
FROM order 
WHERE ordr_num = @ordrNum

Upvotes: 64

Related Questions