CODEWITHSUNDEEP
sqlsql-serverif-statementcase
Justin Erswell
Justin Erswell

Reputation: 708

CASE statement for checking a field value

Hopefully you guy's can help.

I am writing a query from a table which has variable data specifically a completed column which can have a value of 1 or 3 the table also has two qty_ fields QTYORDERED and QTYSHIPPED

If the value is 3 in COMPLETED then QTYSHIPPED will contain a value and if the value is 1 the QTYORDERED will have a value.

What I need to do is within my query create one column which just has a QTY can someone show me how to achieve this in SQL Server

Upvotes: 1

Views: 3466

Answers (2)

Pavel Kutakov
Pavel Kutakov

Reputation: 971

SELECT 
   CASE completed
      WHEN 1 THEN QTYORDERED 
      WHEN 3 THEN QTYSHIPPED
      ELSE 0  --Or add any logic when something goes wrong with "completed" value
   END as Quantity
FROM ....

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269743

This is a simple case statement:

select (case when completed = 1 then QTYORDERED
             when completed = 3 then QTYSHIPPED
        end) as QTY

Note this will return NULL when completed has any other value.

You can also write this as:

select (case completed when 1 then QTYORDERED
                       when 3 then QTYSHIPPED
        end)

However, the first form is more general, giving you more flexibility for complicated logic.

Upvotes: 7

Related Questions