Sql Query With Select and IF

Question

here is my table description:

Table Name : Orders

Columns : ID, NO, Quantity, Base, Code

First my query should check the value of Code, if value of Code is 'B' then OUTPUT VALUE should be Code+Base if it is not 'B' then OUTPUT VALUE should be Code+Quantity

the obtained rows again will be filtered by using where clause where ID='' and quantity=''

How can I form this complex query, any ideas ?...

Answer 1

Modified Answer

Something bothered me about my original post (see below, but do not use) so I went back to check. If Code is 'B' (therefore a varchar) and Quantity is an integer, then SQL will not let you add them for obvious reasons and will not do an implicit conversion for you (at least it did not do it for me). So I had to convert Quantity to a varchar to be able to use it.

Look at the following working code built on SQL Server 2008

DECLARE @MyTable TABLE
(
    ID int identity (1, 1),
    Num int,
    Quantity int,
    Base varchar (1),
    Code varchar (1)
)

INSERT INTO @MyTable VALUES 
  (1, 1, 'a', 'A')
, (2, 2, 'b', 'B')
, (3, 3, 'c', 'C')
, (4, 4, 'd', 'D')
, (5, 5, 'e', 'E')

SELECT * FROM @MyTable

SELECT 
    CASE WHEN Code = 'B' THEN Code+Base 
    ELSE Code+CONVERT (VarChar, Quantity) 
    END AS OutputValue
FROM @MyTable

Original Answer (do not use)

try the following

SELECT CASE WHEN Code = 'B' THEN Code+Base ELSE Code+Quantity END AS OutputValue
FROM MyTable
WHERE ID = @Id
and Quantity = @Quantity

Answer 2

SELECT CASE Code WHEN 'B' THEN Code + Base ELSE Code + Quantity END As OutputValue
FROM Orders
WHERE ID = @id AND Quantity = @quantity

Answer 3

SELECT
(
   CASE Code
      WHEN CODE = 'B' THEN Code+Base
      ELSE Code+Quantity
   END
) AS OutputValue
FROM Orders
WHERE ID  = '' AND Quantity=''