How to Simulate SIGN Function

Puzzle of the day:

How to simulate the SIGN function in Oracle SQL by only using CEIL, FLOOR, and ABS Oracle SQL functions along with arithmetic operators? No PL/SQL.

Solution:

```SIGN(x)=CEIL(x/(1+ABS(x)))+FLOOR(x/(1+ABS(x)))
```

In SQL, we can demonstrate it as follows:

```WITH r AS (
SELECT dbms_random.VALUE(-999,999) rnd
FROM dual
CONNECT BY LEVEL<=10
UNION ALL
SELECT 0
FROM dual
)
SELECT rnd, SIGN(rnd), CEIL(rnd/(1+ABS(rnd)))+FLOOR(rnd/(1+ABS(rnd))) "MySign"
FROM r
```

Result:

```       RND  SIGN(RND)     MySign
---------- ---------- ----------
-519.606         -1         -1
-657.62692         -1         -1
414.625079          1          1
736.175183          1          1
268.689074          1          1
-647.12649         -1         -1
338.192233          1          1
784.780876          1          1
-529.69184         -1         -1
-596.56803         -1         -1
0          0          0
```

As you can see, “MySign” column perfectly matches SIGN column.

Comment:

WITH clause is needed to generate 10 random values in the range of -999 .. +999. “0” value is added to demonstrate a special case as it is unlikely that zero will be randomly generated.

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

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the Week #21:

Produce a report that shows employee name, his/her immediate manager name, and the next level manager name. The following conditions should be met:

• Use Single SELECT statement only
• Use mgr column to identify employee’s immediate manager
• The query should work in Oracle 11g.
• A preferred solution should use only a single instance of emp table.

Expected Result:

```NAME1      NAME2      NAME3
---------- ---------- ------
SMITH      FORD       JONES
ALLEN      BLAKE      KING
WARD       BLAKE      KING
JONES      KING
MARTIN     BLAKE      KING
BLAKE      KING
CLARK      KING
SCOTT      JONES      KING
KING
TURNER     BLAKE      KING
JAMES      BLAKE      KING
FORD       JONES      KING
MILLER     CLARK      KING```

Solutions:

#1. Using connect_by_root, sys_connect_by_path, and regexp_substr functions

```col name1 for a10
col name2 for a10
col name3 for a10
WITH x AS(
SELECT CONNECT_BY_ROOT(ename) name,
SYS_CONNECT_BY_PATH(ename, ',') path,
CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT name, REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 2) name2,
REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 3) name3
FROM x
GROUP BY name, empno
ORDER BY empno;```

#2. Using CONNECT BY twice

```WITH x AS (
SELECT ename, PRIOR ename mname, empno, mgr
FROM emp
WHERE LEVEL=2 OR mgr IS NULL
CONNECT BY PRIOR empno=mgr
)
SELECT ename name1, mname name2, MAX(PRIOR mname) name3
FROM x
WHERE LEVEL<=2
CONNECT BY PRIOR empno=mgr
GROUP BY ename, mname, empno
ORDER BY empno```

#3. Using CONNECT BY and Self Outer Join

```WITH x AS (
SELECT ename, PRIOR ename mname, PRIOR mgr AS mgr, empno
FROM emp
WHERE LEVEL=2 OR mgr IS NULL
CONNECT BY PRIOR empno=mgr
)
SELECT x.ename name1, x.mname name2, e.ename name3
FROM x LEFT JOIN emp e ON x.mgr=e.empno
ORDER BY x.empno```

#4. Using 2 Self Outer Joins

```SELECT a.ename name1, b.ename name2, c.ename name3
FROM emp a LEFT JOIN emp b ON a.mgr=b.empno
LEFT JOIN emp c ON b.mgr=c.empno
ORDER BY a.empno```

#5. Using CONNECT BY and PIVOT

```SELECT name1, name2, name3
FROM (
SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
PIVOT(
MAX(ename)
FOR lvl IN (1 AS name1, 2 AS name2, 3 AS name3)
)
ORDER BY empno;```

#6. PIVOT Simulation

```WITH x AS (
SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT MAX(DECODE(lvl, 1, ename)) name1,
MAX(DECODE(lvl, 2, ename)) name2,
MAX(DECODE(lvl, 3, ename)) name3
FROM x
GROUP BY empno
ORDER BY empno;```

#7. Using CONNECT BY and no WITH/Subqueries (Credit to Krishna Jamal)

```SELECT ename Name1, PRIOR ename Name2,
DECODE(LEVEL,
3, CONNECT_BY_ROOT(ename),
4, TRIM(BOTH ' ' FROM
REPLACE(
REPLACE(SYS_CONNECT_BY_PATH(PRIOR ename, ' '), PRIOR ename),
CONNECT_BY_ROOT(ename)))
) Name3
FROM emp
CONNECT BY PRIOR empno = mgr
ORDER BY ROWID;```

#8. A composition of Methods 1 and 7:

```SELECT ename Name1, PRIOR ename Name2,
CASE WHEN LEVEL IN (3,4)
THEN REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ename, ','),'[^,]+',1,LEVEL-2)
END AS Name3
FROM emp
CONNECT BY PRIOR empno = mgr
ORDER BY ROWID;```

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

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the Week #21:

Produce a report that shows employee name, his/her immediate manager name, and the next level manager name. The following conditions should be met:

• Use Single SELECT statement only
• Use mgr column to identify employee’s immediate manager
• The query should work in Oracle 11g.
• A preferred solution should use only a single instance of emp table.

Expected Result:

```NAME1      NAME2      NAME3
---------- ---------- ------
SMITH      FORD       JONES
ALLEN      BLAKE      KING
WARD       BLAKE      KING
JONES      KING
MARTIN     BLAKE      KING
BLAKE      KING
CLARK      KING
SCOTT      JONES      KING
KING
TURNER     BLAKE      KING
JAMES      BLAKE      KING
FORD       JONES      KING
MILLER     CLARK      KING```

A correct answer (and workarounds!) will be published here in a week.

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

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the Week #20:

Produce the historical highest/lowest salary report that should comply with the following requirements:

• Use Single SELECT statement only
• Only employees who was paid the highest or lowest salary in their respective department at the moment of hiring should be selected
• Show name, date of hire, department number, job title, salary table (emp) columns and two additional calculated columns/flags: min_flag and max_flag to indicate that the employee was hired with the min/max salary in their respective department as of the time of hiring.
• If two or more employees in the same department are paid the same max/min salary, only the one who was hired first should be picked for the report.
• The query should work in Oracle 11g.

Expected Result:

#1. Using Common Table Expression (CTE) or Recursive WITH clause

```WITH y AS (
SELECT ename, job, deptno, hiredate, sal,
ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY hiredate) rn
FROM emp
), x (ename, job, deptno, hiredate, sal, min_sal, max_sal, min_flag, max_flag, rn) AS (
SELECT ename, job, deptno, hiredate, sal, sal, sal, 1, 1, 1
FROM y
WHERE rn=1
UNION ALL
SELECT y.ename, y.job, y.deptno, y.hiredate, y.sal,
LEAST(x.min_sal, y.sal), GREATEST(x.max_sal, y.sal),
CASE WHEN y.sal<x.min_sal THEN 1 END,
CASE WHEN y.sal>x.max_sal THEN 1 END, y.rn
FROM y JOIN x ON y.deptno=x.deptno AND y.rn=x.rn+1
)
SELECT ename, job, deptno, hiredate, sal, min_flag, max_flag
FROM x
WHERE 1 IN (min_flag, max_flag)
ORDER BY deptno, hiredate;```

#2. Using Cumulative Analytic Functions MIN, MAX, and ROW_NUMBER

```WITH x AS (
SELECT ename, job, deptno, hiredate, sal,
MIN(sal)OVER(PARTITION BY deptno ORDER BY hiredate) min_sal,
MAX(sal)OVER(PARTITION BY deptno ORDER BY hiredate) max_sal,
ROW_NUMBER()OVER(PARTITION BY deptno, sal ORDER BY hiredate) rn
FROM emp
)
SELECT ename, job, deptno, hiredate, sal,
DECODE(sal, min_sal, 1) min_flag,
DECODE(sal, max_sal, 1) max_flag
FROM x
WHERE sal IN (min_sal, max_sal)
AND rn=1;```

#3. Using Cumulative Analytic Functions MIN, MAX, and COUNT

```WITH x AS (
SELECT ename, job, deptno, hiredate, sal,
CASE WHEN MIN(sal)OVER(PARTITION BY deptno ORDER BY hiredate)=sal
AND COUNT(*)OVER(PARTITION BY deptno, sal ORDER BY hiredate)=1 THEN 1
END min_flag,
CASE WHEN MAX(sal)OVER(PARTITION BY deptno ORDER BY hiredate)=sal
AND COUNT(*)OVER(PARTITION BY deptno, sal ORDER BY hiredate)=1 THEN 1
END max_flag
FROM emp
)
SELECT *
FROM x
WHERE 1 IN (min_flag, max_flag);```

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

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the Week #20:

Produce the historical highest/lowest salary report that should comply with the following requirements:

• Use Single SELECT statement only
• Only employees who was paid the highest or lowest salary in their respective department at the moment of hiring should be selected
• Show name, date of hire, department number, job title, salary table (emp) columns and two additional calculated columns/flags: min_flag and max_flag to indicate that the employee was hired with the min/max salary in their respective department as of the time of hiring.
• If two or more employees in the same department are paid the same max/min salary, only the one who was hired first should be picked for the report.
• The query should work in Oracle 11g.

Comment: Apparently, the first employee in each department automatically qualifies for both, the lowest and the highest paid employee at the time of hiring.

Expected Result:

A correct answer (and workarounds!) will be published here in a week.

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

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the Week #19:

Produce the department salary report (shown below) with the following  assumptions/requirements:

• Use Single SELECT statement only
• DECODE and CASE functions are not allowed
• An employee’s salary is shown in the corresponding department column (10, 20 or 30), all other department columns should contain NULLs.
• The query should work in Oracle 11g.

Expected Result:

```ENAME              10         20         30
---------- ---------- ---------- ----------
SMITH                        800
ALLEN                                  1600
WARD                                   1250
JONES                       2975
MARTIN                                 1250
BLAKE                                  2850
CLARK            2450
SCOTT                       3000
KING             5000
TURNER                                 1500
JAMES                                   950
FORD                        3000
MILLER           1300```

#1: Using NULLIF, ABS, and SIGN functions

```SELECT ename, NULLIF(sal * (1-ABS(SIGN(deptno-10))),0) "10",
NULLIF(sal * (1-ABS(SIGN(deptno-20))),0) "20",
NULLIF(sal * (1-ABS(SIGN(deptno-30))),0) "30"
FROM emp
```

#2: Using NULLIF and INSTR functions

```SELECT ename, NULLIF(sal * INSTR(deptno, 10), 0) "10",
NULLIF(sal * INSTR(deptno, 20), 0) "20",
NULLIF(sal * INSTR(deptno, 30), 0) "30"
FROM emp```

#3: Using NVL2 and NULLIF functions

```SELECT ename, NVL2(NULLIF(deptno, 10), NULL, 1) * sal "10",
NVL2(NULLIF(deptno, 20), NULL, 1) * sal "20",
NVL2(NULLIF(deptno, 30), NULL, 1) * sal "30"
FROM emp```

#4: Using PIVOT clause

```SELECT *
FROM  (SELECT deptno, ename, sal
FROM emp)
PIVOT (MAX(sal)
FOR deptno IN (10, 20, 30)
);```

#5: Using Scalar SELECT statements in SELECT clause

```SELECT ename,
(SELECT sal FROM emp WHERE empno=e.empno AND deptno=10) "10",
(SELECT sal FROM emp WHERE empno=e.empno AND deptno=20) "20",
(SELECT sal FROM emp WHERE empno=e.empno AND deptno=30) "30"
FROM emp e;```

#6: Using UNION (different sort order)

```SELECT ename, sal "10", NULL "20", NULL "30"
FROM emp
WHERE deptno=10
UNION
SELECT ename, NULL, sal, NULL
FROM emp
WHERE deptno=20
UNION
SELECT ename, NULL, NULL, sal
FROM emp
WHERE deptno=30;

ENAME              10         20         30
---------- ---------- ---------- ----------
ALLEN                                  1600
BLAKE                                  2850
CLARK            2450
FORD                        3000
JAMES                                   950
JONES                       2975
KING             5000
MARTIN                                 1250
MILLER           1300
SCOTT                       3000
SMITH                        800
TURNER                                 1500
WARD                                   125

```

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

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the Week #19:

Produce the department salary report (shown below) with the following  assumptions/requirements:

• Use Single SELECT statement only
• DECODE and CASE functions are not allowed
• An employee’s salary is shown in the corresponding department column (10, 20 or 30), all other department columns should contain NULLs.
• The query should work in Oracle 11g.

Expected Result:

```ENAME              10         20         30
---------- ---------- ---------- ----------
SMITH                        800
ALLEN                                  1600
WARD                                   1250
JONES                       2975
MARTIN                                 1250
BLAKE                                  2850
CLARK            2450
SCOTT                       3000
KING             5000
TURNER                                 1500
JAMES                                   950
FORD                        3000
MILLER           1300```

A correct answer (and workarounds!) will be published here in a week.

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

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the Week #18:

There are gaps in values of empno column in emp table. The challenge is to find all the gaps within the range of employee numbers already in use. All numbers should be grouped in ranges (see expected result section below). A single SELECT statement against emp table is expected.

Expected Result:

```Avail. Emp Numbers
------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933
```

Solutions

#1: Using GROUP BY Over ROWNUM expression

```WITH x AS (
SELECT MIN(empno) min_no, MAX(empno) max_no
FROM emp
), y AS (
SELECT min_no+LEVEL-1 empno
FROM x
CONNECT BY min_no+LEVEL-1<=max_no
MINUS
SELECT empno
FROM emp
)
SELECT MIN(empno) || ' - ' || MAX(empno) "Avail. Emp Numbers"
FROM y
GROUP BY empno-ROWNUM
ORDER BY empno-ROWNUM;

Avail. Emp Numbers
--------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933```

#2: Using MATCH_RECOGNIZE (Oracle 12c and up;  credit to Zohar Elkayam)

```WITH x AS (
SELECT MIN(empno) min_no, MAX(empno) max_no
FROM emp
), y AS (
SELECT min_no+LEVEL-1 empno
FROM x
CONNECT BY min_no+LEVEL-1<=max_no
MINUS
SELECT empno
FROM emp
)
SELECT firstemp || ' - ' || lastemp "Avail. Emp Numbers"
FROM y
MATCH_RECOGNIZE (
ORDER BY empno
MEASURES
A.empno firstemp,
LAST(empno) lastemp
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A B*)
DEFINE B AS empno = PREV(empno)+1
);```

#3: Using LEAD Analytic function (credit to Krishna Jamal)

```WITH x AS
(
FROM emp
)
SELECT (empno+1) || ' - ' || (lead_empno-1) "Avail. Emp Numbers"
FROM x

Also, see a very similar Puzzle of the Week #15 for more workarounds.

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

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the Week #18:

There are gaps in values of empno column in emp table. The challenge is to find all the gaps within the range of employee numbers already in use. All numbers should be grouped in ranges (see expected result section below). A single SELECT statement against emp table is expected.

Expected Result:

```Avail. Emp Numbers
------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933```

A correct answer (and workarounds!) will be published here in a week.

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

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the Week #17:

Write a single SELECT statement that would show the years of hire in each department. The result should have 3 columns (see below): deptno, year1, and year2. If a department only hired during 1 calendar year, this year should be shown in year1 column (see deptno 30) and year2 column should be blank. If a department hired during 2 calendar years, the first year should be should be shown in year1 column, and the 2nd year should be shown in year2 column (see deptno 10). In all other cases, show 1st year in year1 column and “More (N)” where N is the number of years that department did the hiring (see deptno 20).

Expected Result:

```DEPTNO Year 1   Year 2
------ -------- --------
10 1981     1982
20 1980     More (3)
30 1981

```

Solutions

#1: Using COUNT(DISTINCT ..)

```SELECT deptno, MIN(EXTRACT(YEAR FROM hiredate)) AS "Year 1",
CASE COUNT(DISTINCT EXTRACT(YEAR FROM hiredate))
WHEN 1 THEN ''
WHEN 2 THEN TO_CHAR(MAX(EXTRACT(YEAR FROM hiredate)))
ELSE 'More (' || COUNT(DISTINCT EXTRACT(YEAR FROM hiredate)) || ')'
END AS "Year 2"
FROM emp
GROUP BY deptno
ORDER BY 1;

DEPTNO     Year 1 Year 2
------ ---------- --------
10       1981 1982
20       1980 More (3)
30       1981```

#2: Using DENSE_RANK Analytic Function

```WITH x AS (
SELECT deptno, EXTRACT(YEAR FROM hiredate) hire_year,
DENSE_RANK()OVER(PARTITION BY deptno ORDER BY EXTRACT(YEAR FROM hiredate)) rk
FROM emp
)
SELECT deptno, MIN(hire_year) "Year 1",
CASE MAX(rk) WHEN 1 THEN ''
WHEN 2 THEN CAST(MAX(hire_year) AS CHAR(4))
ELSE 'More (' || MAX(rk) || ')'
END AS "Year 2"
FROM x
GROUP BY deptno
ORDER BY 1;

DEPTNO     Year 1 Year 2
------ ---------- --------
10       1981 1982
20       1980 More (3)
30       1981```

#3: Using PIVOT clause

```SELECT deptno, Y1 "Year 1",
CASE WHEN cnt>2 THEN 'More (' || cnt || ')'
ELSE TO_CHAR(Y2)
END "Year 2"
FROM
(
SELECT deptno, EXTRACT(YEAR FROM hiredate) yr,
CASE DENSE_RANK()OVER(PARTITION BY deptno ORDER BY EXTRACT(YEAR FROM hiredate))
WHEN 1 THEN 'Y1'
WHEN 2 THEN 'Y2'
END AS Y,
COUNT(DISTINCT EXTRACT(YEAR FROM hiredate))OVER(PARTITION BY deptno) cnt
FROM emp
)
PIVOT
(
MAX(yr)
FOR y IN ('Y1' y1,'Y2' y2)
);

DEPTNO     Year 1 Year 2
------ ---------- --------
10       1981 1982
20       1980 More (3)
30       1981```

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