Use Case in SQL

CASE may be one of the keywords most misused in SQL. Although you may have used this keyword before to create a field, it also has more features. For example, you can

Use CASE in clause.

First, let's take a look at the CASE syntax. In a general SELECT statement, the syntax is as follows:

SELECT =
CASE
WHEN THEN
WHENTHEN
ELSE
END

In the above Code, you need to replace the content in angle brackets with specific parameters. The following is a simple example:

USE pubs
GO
SELECT
Title,
'Price range' =
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'Gift to impress relatives'
END
FROM titles
Order by price
GO

Informix cannot be in the preceding format:
USE pubs
GO
SELECT
Title,
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'Gift to impress relatives'
END
Price Range
FROM titles
Order by price
GO

This is a typical example of CASE, but you can do more with CASE. For example, the CASE in the group by clause below:

SELECT 'number of tidles ', Count (*)
FROM titles
GROUP
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'Gift to impress relatives'
END
GO

You can even combine these options to add an order by clause, as shown below:

USE pubs
GO
SELECT
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'Gift to impress relatives'
End as Range,
Title
FROM titles
GROUP
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'Gift to impress relatives'
END,
Title
ORDER
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'Gift to impress relatives'
END,
Title
GO

The formats in informix are different, for example:
Select
Case when td_acc1_no [610] = '000000' then get_contrast (td_acc1_no)
Else get_contrast (td_td_acct_no)
End sx_acct_no
, Sum (td_actu_amt) sx_bal
From dcc_tdacnacn where td_td_acct_no in ('123 ',
'123 ')
Group by 1

Note: To use CASE in the group by block, the query statement must repeat the CASE block in the SELECT block in the group by block.

In addition to selecting custom fields, CASE is useful in many cases. Further, you can get the grouping sorting result set that you previously thought was impossible to get.