How do I perform an IF...THEN in an SQL SELECT statement?
For example:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
This question is related to
sql
sql-server
tsql
if-statement
case
Simple if-else statement in SQL Server:
DECLARE @val INT;
SET @val = 15;
IF @val < 25
PRINT 'Hi Ravi Anand';
ELSE
PRINT 'By Ravi Anand.';
GO
Nested If...else statement in SQL Server -
DECLARE @val INT;
SET @val = 15;
IF @val < 25
PRINT 'Hi Ravi Anand.';
ELSE
BEGIN
IF @val < 50
PRINT 'what''s up?';
ELSE
PRINT 'Bye Ravi Anand.';
END;
GO
As an alternative solution to the CASE statement, a table-driven approach can be used:
DECLARE @Product TABLE (ID INT, Obsolete VARCHAR(10), InStock VARCHAR(10))
INSERT INTO @Product VALUES
(1,'N','Y'),
(2,'A','B'),
(3,'N','B'),
(4,'A','Y')
SELECT P.* , ISNULL(Stmt.Saleable,0) Saleable
FROM
@Product P
LEFT JOIN
( VALUES
( 'N', 'Y', 1 )
) Stmt (Obsolete, InStock, Saleable)
ON P.InStock = Stmt.InStock OR P.Obsolete = Stmt.Obsolete
Result:
ID Obsolete InStock Saleable
----------- ---------- ---------- -----------
1 N Y 1
2 A B 0
3 N B 1
4 A Y 1
From SQL Server 2012 you can use the IIF function for this.
SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM Product
This is effectively just a shorthand (albeit not standard SQL) way of writing CASE.
I prefer the conciseness when compared with the expanded CASE version.
Both IIF() and CASE resolve as expressions within a SQL statement and can only be used in well-defined places.
The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.
If your needs can not be satisfied by these limitations (for example, a need to return differently shaped result sets dependent on some condition) then SQL Server does also have a procedural IF keyword.
IF @IncludeExtendedInformation = 1
BEGIN
SELECT A,B,C,X,Y,Z
FROM T
END
ELSE
BEGIN
SELECT A,B,C
FROM T
END
Care must sometimes be taken to avoid parameter sniffing issues with this approach however.
For the sake of completeness, I would add that SQL uses three-valued logic. The expression:
obsolete = 'N' OR instock = 'Y'
Could produce three distinct results:
| obsolete | instock | saleable |
|----------|---------|----------|
| Y | Y | true |
| Y | N | false |
| Y | null | null |
| N | Y | true |
| N | N | true |
| N | null | true |
| null | Y | true |
| null | N | null |
| null | null | null |
So for example if a product is obsolete but you dont know if product is instock then you dont know if product is saleable. You can write this three-valued logic as follows:
SELECT CASE
WHEN obsolete = 'N' OR instock = 'Y' THEN 'true'
WHEN NOT (obsolete = 'N' OR instock = 'Y') THEN 'false'
ELSE NULL
END AS saleable
Once you figure out how it works, you can convert three results to two results by deciding the behavior of null. E.g. this would treat null as not saleable:
SELECT CASE
WHEN obsolete = 'N' OR instock = 'Y' THEN 'true'
ELSE 'false' -- either false or null
END AS saleable
The case statement is your friend in this situation, and takes one of two forms:
The simple case:
SELECT CASE <variable> WHEN <value> THEN <returnvalue>
WHEN <othervalue> THEN <returnthis>
ELSE <returndefaultcase>
END AS <newcolumnname>
FROM <table>
The extended case:
SELECT CASE WHEN <test> THEN <returnvalue>
WHEN <othertest> THEN <returnthis>
ELSE <returndefaultcase>
END AS <newcolumnname>
FROM <table>
You can even put case statements in an order by clause for really fancy ordering.
Using SQL CASE is just like normal If / Else statements. In below query, If obsolete value = 'N' or If InStock value = 'Y' Then Output will be 1. Otherwise output will be 0. Then we put that 0 or 1 value under the Salable Column.
SELECT
CASE
WHEN obsolete = 'N' OR InStock = 'Y'
THEN 1
ELSE 0
END AS Salable
, *
FROM PRODUCT
If you're inserting results into a table for the first time, rather than transferring results from one table to another, this works in Oracle 11.2g:
INSERT INTO customers (last_name, first_name, city)
SELECT 'Doe', 'John', 'Chicago' FROM dual
WHERE NOT EXISTS
(SELECT '1' from customers
where last_name = 'Doe'
and first_name = 'John'
and city = 'Chicago');
From this link, we can understand IF THEN ELSE in T-SQL:
IF EXISTS(SELECT *
FROM Northwind.dbo.Customers
WHERE CustomerId = 'ALFKI')
PRINT 'Need to update Customer Record ALFKI'
ELSE
PRINT 'Need to add Customer Record ALFKI'
IF EXISTS(SELECT *
FROM Northwind.dbo.Customers
WHERE CustomerId = 'LARSE')
PRINT 'Need to update Customer Record LARSE'
ELSE
PRINT 'Need to add Customer Record LARSE'
Isn't this good enough for T-SQL?
Use pure bit logic:
DECLARE @Product TABLE (
id INT PRIMARY KEY IDENTITY NOT NULL
,Obsolote CHAR(1)
,Instock CHAR(1)
)
INSERT INTO @Product ([Obsolote], [Instock])
VALUES ('N', 'N'), ('N', 'Y'), ('Y', 'Y'), ('Y', 'N')
;
WITH cte
AS
(
SELECT
'CheckIfInstock' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Instock], 'Y'), 1), 'N'), 0) AS BIT)
,'CheckIfObsolote' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Obsolote], 'N'), 0), 'Y'), 1) AS BIT)
,*
FROM
@Product AS p
)
SELECT
'Salable' = c.[CheckIfInstock] & ~c.[CheckIfObsolote]
,*
FROM
[cte] c
See working demo: if then without case in SQL Server.
For start, you need to work out the value of true and false for selected conditions. Here comes two NULLIF:
for true: ISNULL(NULLIF(p.[Instock], 'Y'), 1)
for false: ISNULL(NULLIF(p.[Instock], 'N'), 0)
combined together gives 1 or 0. Next use bitwise operators.
It's the most WYSIWYG method.
SELECT
CAST(
CASE WHEN Obsolete = 'N'
or InStock = 'Y' THEN ELSE 0 END AS bit
) as Saleable, *
FROM
Product
SELECT
CASE
WHEN OBSOLETE = 'N' or InStock = 'Y' THEN 'TRUE'
ELSE 'FALSE'
END AS Salable,
*
FROM PRODUCT
You can find some nice examples in The Power of SQL CASE Statements, and I think the statement that you can use will be something like this (from 4guysfromrolla):
SELECT
FirstName, LastName,
Salary, DOB,
CASE Gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
END
FROM Employees
Use CASE. Something like this.
SELECT Salable =
CASE Obsolete
WHEN 'N' THEN 1
ELSE 0
END
A new feature, IIF (that we can simply use), was added in SQL Server 2012:
SELECT IIF ( (Obsolete = 'N' OR InStock = 'Y'), 1, 0) AS Saleable, * FROM Product
case statement some what similar to if in SQL server
SELECT CASE
WHEN Obsolete = 'N' or InStock = 'Y'
THEN 1
ELSE 0
END as Saleable, *
FROM Product
Use a CASE statement:
SELECT CASE
WHEN (Obsolete = 'N' OR InStock = 'Y')
THEN 'Y'
ELSE 'N'
END as Available
etc...
SELECT
CASE
WHEN OBSOLETE = 'N' or InStock = 'Y' THEN 'TRUE'
ELSE 'FALSE'
END AS Salable,
*
FROM PRODUCT
This isn't an answer, just an example of a CASE statement in use where I work. It has a nested CASE statement. Now you know why my eyes are crossed.
CASE orweb2.dbo.Inventory.RegulatingAgencyName
WHEN 'Region 1'
THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
WHEN 'Region 2'
THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
WHEN 'Region 3'
THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
WHEN 'DEPT OF AGRICULTURE'
THEN orweb2.dbo.CountyStateAgContactInfo.ContactAg
ELSE (
CASE orweb2.dbo.CountyStateAgContactInfo.IsContract
WHEN 1
THEN orweb2.dbo.CountyStateAgContactInfo.ContactCounty
ELSE orweb2.dbo.CountyStateAgContactInfo.ContactState
END
)
END AS [County Contact Name]
For those who uses SQL Server 2012, IIF is a feature that has been added and works as an alternative to Case statements.
SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM Product
SELECT
if((obsolete = 'N' OR instock = 'Y'), 1, 0) AS saleable, *
FROM
product;
Using SQL CASE is just like normal If / Else statements. In below query, If obsolete value = 'N' or If InStock value = 'Y' Then Output will be 1. Otherwise output will be 0. Then we put that 0 or 1 value under the Salable Column.
SELECT
CASE
WHEN obsolete = 'N' OR InStock = 'Y'
THEN 1
ELSE 0
END AS Salable
, *
FROM PRODUCT
SELECT CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0
END AS Saleable, *
FROM Product
You can find some nice examples in The Power of SQL CASE Statements, and I think the statement that you can use will be something like this (from 4guysfromrolla):
SELECT
FirstName, LastName,
Salary, DOB,
CASE Gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
END
FROM Employees
You can use Case Statement:
Select
Case WHEN (Obsolete = 'N' or InStock = 'Y') THEN 1 ELSE 0 END Saleable,
Product.*
from Product
I like the use of the CASE statements but the question asked for an IF statement in the SQL Select. What I've used in the past has been:
SELECT
if(GENDER = "M","Male","Female") as Gender
FROM ...
It's like the excel or sheets IF statements where there is a conditional followed by the true condition and then the false condition:
if(condition, true, false)
Furthermore, you can nest the if statements (but then use should use a CASE :-)
(Note: this works in MySQLWorkbench but may not work in other platforms)
SELECT 1 AS Saleable, *
FROM @Product
WHERE ( Obsolete = 'N' OR InStock = 'Y' )
UNION
SELECT 0 AS Saleable, *
FROM @Product
WHERE NOT ( Obsolete = 'N' OR InStock = 'Y' )
SELECT
(CASE
WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES'
ELSE 'NO'
END) as Salable
, *
FROM Product
Use pure bit logic:
DECLARE @Product TABLE (
id INT PRIMARY KEY IDENTITY NOT NULL
,Obsolote CHAR(1)
,Instock CHAR(1)
)
INSERT INTO @Product ([Obsolote], [Instock])
VALUES ('N', 'N'), ('N', 'Y'), ('Y', 'Y'), ('Y', 'N')
;
WITH cte
AS
(
SELECT
'CheckIfInstock' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Instock], 'Y'), 1), 'N'), 0) AS BIT)
,'CheckIfObsolote' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Obsolote], 'N'), 0), 'Y'), 1) AS BIT)
,*
FROM
@Product AS p
)
SELECT
'Salable' = c.[CheckIfInstock] & ~c.[CheckIfObsolote]
,*
FROM
[cte] c
See working demo: if then without case in SQL Server.
For start, you need to work out the value of true and false for selected conditions. Here comes two NULLIF:
for true: ISNULL(NULLIF(p.[Instock], 'Y'), 1)
for false: ISNULL(NULLIF(p.[Instock], 'N'), 0)
combined together gives 1 or 0. Next use bitwise operators.
It's the most WYSIWYG method.
You can have two choices for this to actually implement:
Using IIF, which got introduced from SQL Server 2012:
SELECT IIF ( (Obsolete = 'N' OR InStock = 'Y'), 1, 0) AS Saleable, * FROM Product
Using Select Case:
SELECT CASE
WHEN Obsolete = 'N' or InStock = 'Y'
THEN 1
ELSE 0
END as Saleable, *
FROM Product
SELECT CASE WHEN profile.nrefillno = 0 THEN 'N' ELSE 'R'END as newref
From profile
SELECT
(CASE
WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES'
ELSE 'NO'
END) as Salable
, *
FROM Product
Microsoft SQL Server (T-SQL)
In a select, use:
select case when Obsolete = 'N' or InStock = 'Y' then 'YES' else 'NO' end
In a where clause, use:
where 1 = case when Obsolete = 'N' or InStock = 'Y' then 1 else 0 end
Use a CASE statement:
SELECT CASE
WHEN (Obsolete = 'N' OR InStock = 'Y')
THEN 'Y'
ELSE 'N'
END as Available
etc...
SELECT
CAST(
CASE WHEN Obsolete = 'N'
or InStock = 'Y' THEN ELSE 0 END AS bit
) as Saleable, *
FROM
Product
Microsoft SQL Server (T-SQL)
In a select, use:
select case when Obsolete = 'N' or InStock = 'Y' then 'YES' else 'NO' end
In a where clause, use:
where 1 = case when Obsolete = 'N' or InStock = 'Y' then 1 else 0 end
This isn't an answer, just an example of a CASE statement in use where I work. It has a nested CASE statement. Now you know why my eyes are crossed.
CASE orweb2.dbo.Inventory.RegulatingAgencyName
WHEN 'Region 1'
THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
WHEN 'Region 2'
THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
WHEN 'Region 3'
THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
WHEN 'DEPT OF AGRICULTURE'
THEN orweb2.dbo.CountyStateAgContactInfo.ContactAg
ELSE (
CASE orweb2.dbo.CountyStateAgContactInfo.IsContract
WHEN 1
THEN orweb2.dbo.CountyStateAgContactInfo.ContactCounty
ELSE orweb2.dbo.CountyStateAgContactInfo.ContactState
END
)
END AS [County Contact Name]
Use CASE. Something like this.
SELECT Salable =
CASE Obsolete
WHEN 'N' THEN 1
ELSE 0
END
As an alternative solution to the CASE statement, a table-driven approach can be used:
DECLARE @Product TABLE (ID INT, Obsolete VARCHAR(10), InStock VARCHAR(10))
INSERT INTO @Product VALUES
(1,'N','Y'),
(2,'A','B'),
(3,'N','B'),
(4,'A','Y')
SELECT P.* , ISNULL(Stmt.Saleable,0) Saleable
FROM
@Product P
LEFT JOIN
( VALUES
( 'N', 'Y', 1 )
) Stmt (Obsolete, InStock, Saleable)
ON P.InStock = Stmt.InStock OR P.Obsolete = Stmt.Obsolete
Result:
ID Obsolete InStock Saleable
----------- ---------- ---------- -----------
1 N Y 1
2 A B 0
3 N B 1
4 A Y 1
A new feature, IIF (that we can simply use), was added in SQL Server 2012:
SELECT IIF ( (Obsolete = 'N' OR InStock = 'Y'), 1, 0) AS Saleable, * FROM Product
You can find some nice examples in The Power of SQL CASE Statements, and I think the statement that you can use will be something like this (from 4guysfromrolla):
SELECT
FirstName, LastName,
Salary, DOB,
CASE Gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
END
FROM Employees
SELECT
(CASE
WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES'
ELSE 'NO'
END) as Salable
, *
FROM Product
Microsoft SQL Server (T-SQL)
In a select, use:
select case when Obsolete = 'N' or InStock = 'Y' then 'YES' else 'NO' end
In a where clause, use:
where 1 = case when Obsolete = 'N' or InStock = 'Y' then 1 else 0 end
It will be something like that:
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN "The quantity is greater than 30"
WHEN Quantity = 30 THEN "The quantity is 30"
ELSE "The quantity is under 30"
END AS QuantityText
FROM OrderDetails;
case statement some what similar to if in SQL server
SELECT CASE
WHEN Obsolete = 'N' or InStock = 'Y'
THEN 1
ELSE 0
END as Saleable, *
FROM Product
The case statement is your friend in this situation, and takes one of two forms:
The simple case:
SELECT CASE <variable> WHEN <value> THEN <returnvalue>
WHEN <othervalue> THEN <returnthis>
ELSE <returndefaultcase>
END AS <newcolumnname>
FROM <table>
The extended case:
SELECT CASE WHEN <test> THEN <returnvalue>
WHEN <othertest> THEN <returnthis>
ELSE <returndefaultcase>
END AS <newcolumnname>
FROM <table>
You can even put case statements in an order by clause for really fancy ordering.
Question:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
ANSI:
Select
case when p.Obsolete = 'N'
or p.InStock = 'Y' then 1 else 0 end as Saleable,
p.*
FROM
Product p;
Using aliases -- p in this case -- will help prevent issues.
SELECT
if((obsolete = 'N' OR instock = 'Y'), 1, 0) AS saleable, *
FROM
product;
You can use Case Statement:
Select
Case WHEN (Obsolete = 'N' or InStock = 'Y') THEN 1 ELSE 0 END Saleable,
Product.*
from Product
SELECT
(CASE
WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES'
ELSE 'NO'
END) as Salable
, *
FROM Product
From this link, we can understand IF THEN ELSE in T-SQL:
IF EXISTS(SELECT *
FROM Northwind.dbo.Customers
WHERE CustomerId = 'ALFKI')
PRINT 'Need to update Customer Record ALFKI'
ELSE
PRINT 'Need to add Customer Record ALFKI'
IF EXISTS(SELECT *
FROM Northwind.dbo.Customers
WHERE CustomerId = 'LARSE')
PRINT 'Need to update Customer Record LARSE'
ELSE
PRINT 'Need to add Customer Record LARSE'
Isn't this good enough for T-SQL?
Use CASE. Something like this.
SELECT Salable =
CASE Obsolete
WHEN 'N' THEN 1
ELSE 0
END
From SQL Server 2012 you can use the IIF function for this.
SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM Product
This is effectively just a shorthand (albeit not standard SQL) way of writing CASE.
I prefer the conciseness when compared with the expanded CASE version.
Both IIF() and CASE resolve as expressions within a SQL statement and can only be used in well-defined places.
The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.
If your needs can not be satisfied by these limitations (for example, a need to return differently shaped result sets dependent on some condition) then SQL Server does also have a procedural IF keyword.
IF @IncludeExtendedInformation = 1
BEGIN
SELECT A,B,C,X,Y,Z
FROM T
END
ELSE
BEGIN
SELECT A,B,C
FROM T
END
Care must sometimes be taken to avoid parameter sniffing issues with this approach however.
For the sake of completeness, I would add that SQL uses three-valued logic. The expression:
obsolete = 'N' OR instock = 'Y'
Could produce three distinct results:
| obsolete | instock | saleable |
|----------|---------|----------|
| Y | Y | true |
| Y | N | false |
| Y | null | null |
| N | Y | true |
| N | N | true |
| N | null | true |
| null | Y | true |
| null | N | null |
| null | null | null |
So for example if a product is obsolete but you dont know if product is instock then you dont know if product is saleable. You can write this three-valued logic as follows:
SELECT CASE
WHEN obsolete = 'N' OR instock = 'Y' THEN 'true'
WHEN NOT (obsolete = 'N' OR instock = 'Y') THEN 'false'
ELSE NULL
END AS saleable
Once you figure out how it works, you can convert three results to two results by deciding the behavior of null. E.g. this would treat null as not saleable:
SELECT CASE
WHEN obsolete = 'N' OR instock = 'Y' THEN 'true'
ELSE 'false' -- either false or null
END AS saleable
Use a CASE statement:
SELECT CASE
WHEN (Obsolete = 'N' OR InStock = 'Y')
THEN 'Y'
ELSE 'N'
END as Available
etc...
The case statement is your friend in this situation, and takes one of two forms:
The simple case:
SELECT CASE <variable> WHEN <value> THEN <returnvalue>
WHEN <othervalue> THEN <returnthis>
ELSE <returndefaultcase>
END AS <newcolumnname>
FROM <table>
The extended case:
SELECT CASE WHEN <test> THEN <returnvalue>
WHEN <othertest> THEN <returnthis>
ELSE <returndefaultcase>
END AS <newcolumnname>
FROM <table>
You can even put case statements in an order by clause for really fancy ordering.
Simple if-else statement in SQL Server:
DECLARE @val INT;
SET @val = 15;
IF @val < 25
PRINT 'Hi Ravi Anand';
ELSE
PRINT 'By Ravi Anand.';
GO
Nested If...else statement in SQL Server -
DECLARE @val INT;
SET @val = 15;
IF @val < 25
PRINT 'Hi Ravi Anand.';
ELSE
BEGIN
IF @val < 50
PRINT 'what''s up?';
ELSE
PRINT 'Bye Ravi Anand.';
END;
GO
SELECT IIF(Obsolete = 'N' OR InStock = 'Y',1,0) AS Saleable, * FROM Product
SELECT CASE WHEN profile.nrefillno = 0 THEN 'N' ELSE 'R'END as newref
From profile
You can have two choices for this to actually implement:
Using IIF, which got introduced from SQL Server 2012:
SELECT IIF ( (Obsolete = 'N' OR InStock = 'Y'), 1, 0) AS Saleable, * FROM Product
Using Select Case:
SELECT CASE
WHEN Obsolete = 'N' or InStock = 'Y'
THEN 1
ELSE 0
END as Saleable, *
FROM Product
SELECT
CASE
WHEN OBSOLETE = 'N' or InStock = 'Y' THEN 'TRUE'
ELSE 'FALSE'
END AS Salable,
*
FROM PRODUCT
Question:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
ANSI:
Select
case when p.Obsolete = 'N'
or p.InStock = 'Y' then 1 else 0 end as Saleable,
p.*
FROM
Product p;
Using aliases -- p in this case -- will help prevent issues.
Use a CASE statement:
SELECT CASE
WHEN (Obsolete = 'N' OR InStock = 'Y')
THEN 'Y'
ELSE 'N'
END as Available
etc...
SELECT CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0
END AS Saleable, *
FROM Product
You can find some nice examples in The Power of SQL CASE Statements, and I think the statement that you can use will be something like this (from 4guysfromrolla):
SELECT
FirstName, LastName,
Salary, DOB,
CASE Gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
END
FROM Employees
I like the use of the CASE statements but the question asked for an IF statement in the SQL Select. What I've used in the past has been:
SELECT
if(GENDER = "M","Male","Female") as Gender
FROM ...
It's like the excel or sheets IF statements where there is a conditional followed by the true condition and then the false condition:
if(condition, true, false)
Furthermore, you can nest the if statements (but then use should use a CASE :-)
(Note: this works in MySQLWorkbench but may not work in other platforms)
SELECT 1 AS Saleable, *
FROM @Product
WHERE ( Obsolete = 'N' OR InStock = 'Y' )
UNION
SELECT 0 AS Saleable, *
FROM @Product
WHERE NOT ( Obsolete = 'N' OR InStock = 'Y' )
If you're inserting results into a table for the first time, rather than transferring results from one table to another, this works in Oracle 11.2g:
INSERT INTO customers (last_name, first_name, city)
SELECT 'Doe', 'John', 'Chicago' FROM dual
WHERE NOT EXISTS
(SELECT '1' from customers
where last_name = 'Doe'
and first_name = 'John'
and city = 'Chicago');
The case statement is your friend in this situation, and takes one of two forms:
The simple case:
SELECT CASE <variable> WHEN <value> THEN <returnvalue>
WHEN <othervalue> THEN <returnthis>
ELSE <returndefaultcase>
END AS <newcolumnname>
FROM <table>
The extended case:
SELECT CASE WHEN <test> THEN <returnvalue>
WHEN <othertest> THEN <returnthis>
ELSE <returndefaultcase>
END AS <newcolumnname>
FROM <table>
You can even put case statements in an order by clause for really fancy ordering.
For those who uses SQL Server 2012, IIF is a feature that has been added and works as an alternative to Case statements.
SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM Product
Microsoft SQL Server (T-SQL)
In a select, use:
select case when Obsolete = 'N' or InStock = 'Y' then 'YES' else 'NO' end
In a where clause, use:
where 1 = case when Obsolete = 'N' or InStock = 'Y' then 1 else 0 end
It will be something like that:
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN "The quantity is greater than 30"
WHEN Quantity = 30 THEN "The quantity is 30"
ELSE "The quantity is under 30"
END AS QuantityText
FROM OrderDetails;
Use CASE. Something like this.
SELECT Salable =
CASE Obsolete
WHEN 'N' THEN 1
ELSE 0
END
Source: Stackoverflow.com