## Two ways to build a salary range report without using CASE function

Interview Question: Produce a salary range report with a single SELECT statement. Decode function is allowed, CASE function – is not.

Level: Intermediate

Expected Result:

```RANGE                             Employees
-------------------------------- ----------
0-999                                     2
1000-2999                                 9
3000-5999                                 3
```

Strategy #1:

```SELECT COALESCE(DECODE(LEAST(sal, 999), sal, '0-999'),
DECODE(LEAST(sal, 2999), GREATEST(sal, 1000), '1000-2999'),
DECODE(LEAST(sal, 9999), GREATEST(sal, 3000), '3000-5999')
) AS range,
COUNT(*) "Employees"
FROM emp
GROUP BY COALESCE(DECODE(LEAST(sal, 999), sal, '0-999'),
DECODE(LEAST(sal, 2999), GREATEST(sal, 1000), '1000-2999'),
DECODE(LEAST(sal, 9999), GREATEST(sal, 3000), '3000-5999')
)
ORDER BY 1
```

Explanation:

In Oracle SQL terms, a mathematical condition

`a <=x <=b`

can be  interpreted as

`x BETWEEN a AND b`

however, this condition is good only for CASE function, and not for DECODE. The trick is to use another interpretation:

`LEAST(b,x)=GREATEST(x,a)`

– that can be used in DECODE.

CASE-based Solution:

```SELECT CASE WHEN sal<=999 THEN '0-999'
WHEN sal BETWEEN 1000 AND 2999 THEN '1000-2999'
WHEN sal BETWEEN 3000 AND 5999 THEN '3000-5999'
END AS range,
COUNT(*) "Employees"
FROM emp
GROUP BY CASE WHEN sal<=999 THEN '0-999'
WHEN sal BETWEEN 1000 AND 2999 THEN '1000-2999'
WHEN sal BETWEEN 3000 AND 5999 THEN '3000-5999'
END
ORDER BY 1
```

Strategy #2:

```WITH x AS (
SELECT DECODE(1, (SELECT COUNT(*) FROM dual WHERE emp.sal<=999), '0-999',
(SELECT COUNT(*) FROM dual WHERE emp.sal BETWEEN 1000 AND 2999), '1000-2999',
(SELECT COUNT(*) FROM dual WHERE emp.sal BETWEEN 3000 AND 5999), '3000-5999'
) AS range
FROM emp
)
SELECT range, COUNT(*) AS "Employees"
FROM x
GROUP BY range
ORDER BY 1
```

Explanation:
This query demonstrates how to mimic CASE function using DECODE and in-line scalar subquery from dual.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/