Monday Hiring SQL Puzzle and Lateral View Usage Nuance

Let’s solve a fairly simple SQL problem:

For each department count the number of people hired on Monday

• Use scott.emp table
• Show department number and count columns
• If no employees from a given department was hired on Monday, we should list such
department with 0 in the count column
• Sort the result by the department number

Expected Result:

DEPTNO	MON_HIRES
10	0
20	0
30	1

We will start with the in-line scalar subquery approach as it is probably one of the most intuitive:

Strategy #1: In-Line Scalar Subquery

SELECT deptno, (SELECT COUNT(*)
                FROM scott.emp
                WHERE deptno=e.deptno
                AND TO_CHAR(hiredate, 'DY')='MON') mon_hires
FROM scott.emp e
GROUP BY deptno
ORDER BY 1

When you only need a single value/column/expression from a correlated subquery, the in-line subquery in SELECT clause works just fine. If we needed more than one: count and let say total salary, we would need to use LATERAL view (or a completely different approach – see below).

Strategy #2: Lateral View

WITH x AS (
SELECT DISTINCT deptno 
FROM scott.emp
)
SELECT x.deptno, m.mon_hires, m.total_sal
FROM x, LATERAL (SELECT COUNT(*) mon_hires, SUM(sal) total_sal
                 FROM scott.emp e
                 WHERE e.deptno=x.deptno
                   AND TO_CHAR(e.hiredate, 'DY')='MON') m
ORDER BY 1

Result:

DEPTNO	MON_HIRES	TOTAL_SAL
10	0	–
20	0	–
30	1	1250

So far, all is good. I know that some database developers don’t like using table aliases too much and when an opportunity comes they use ANSI standard JOIN syntax with USING clause. Can it be applied in the lateral view?

WITH x AS (
SELECT DISTINCT deptno 
FROM scott.emp
)
SELECT x.deptno, m.mon_hires
FROM x, LATERAL (SELECT COUNT(empno) mon_hires
                 FROM scott.emp JOIN x USING (deptno)
                 WHERE TO_CHAR(hiredate, 'DY')='MON') m
ORDER BY 1

Result:

DEPTNO	MON_HIRES
10	1
20	1
30	1

The syntax is correct but the result is apparently not! What happened?

We replaced the WHERE clause condition of e.deptno=x.deptno with the JOIN x USING(deptno) – it first looked legitimate to me until I realized that we have just added an extra join instead of a reference to an external table (CTE x). Essentially, our last (incorrect) query is the same as the following:

WITH x AS (
SELECT DISTINCT deptno 
FROM scott.emp
)
SELECT x.deptno, m.mon_hires
FROM x, LATERAL (SELECT COUNT(*) mon_hires
                 FROM scott.emp e, x
                 WHERE e.deptno=x.deptno
                   AND TO_CHAR(e.hiredate, 'DY')='MON') m
ORDER BY 1

We simply introduced a new (and unwanted) join on the CTE x and turned the correlated reference (in the WHERE clause) into an old-style joining condition which did not even require the LATERAL view functionality:

WITH x AS (
SELECT DISTINCT deptno 
FROM scott.emp
)
SELECT x.deptno, m.mon_hires
FROM x, (SELECT COUNT(*) mon_hires
         FROM scott.emp e, x
         WHERE e.deptno=x.deptno
           AND TO_CHAR(e.hiredate, 'DY')='MON') m
ORDER BY 1

Result:

DEPTNO	MON_HIRES
10	1
20	1
30	1

The above examples prove that we need to be very careful when mixing up different techniques and new syntax options.

Finally, the last approach will show that only a single scan of the emp table is needed to get the result:

Strategy #3: Conditional Counting

SELECT deptno, COUNT(DECODE(TO_CHAR(hiredate, 'DY'), 'MON', 1)) mon_hires
FROM scott.emp
GROUP BY deptno
ORDER BY 1

Result:

DEPTNO	MON_HIRES
10	0
20	0
30	1

This is the best and incidentally the shortest solution that once again demonstrates the power of conditional counting (aggregation) right in SELECT clause.

Likewise we can also show the total salary of those hired on Monday:

SELECT deptno, 
       COUNT(DECODE(TO_CHAR(hiredate, 'DY'), 'MON', 1)) mon_hires,
       SUM(DECODE(TO_CHAR(hiredate, 'DY'), 'MON', sal)) total_sal
FROM scott.emp
GROUP BY deptno
ORDER BY 1

Result:

DEPTNO	MON_HIRES	TOTAL_SAL
10	0	–
20	0	–
30	1	1250

***

If you find this post useful, please press the LIKE button and subscribe.

My Oracle Group on Facebook:

Also, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Suggested Reading:

Would you like to read about many more tricks and puzzles? For more clever tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

How to pass arbitrary number of arguments to a PL/SQL procedure?

I was looking for an answer to this question for quite some time and ended up developing my own approach.

As of version 12.1, Oracle does not offer this feature which is widely available in most modern procedural languages. I found only one Oracle feature that somehow resembles the one in question – a built-in default constructor for PL/SQL collection types. We can pass arbitrary number of values into such constructors.

Here is a simple example:

DECLARE
   TYPE vc_table IS TABLE OF VARCHAR2(30);
   v_table vc_table;
BEGIN
   v_table:=vc_table('ABC', 'DEF', 'GHI');
   FOR i IN 1..v_table.COUNT LOOP
       DBMS_OUTPUT.PUT_LINE(v_table(i));
   END LOOP;
END;
/

Result:

ABC
DEF
GHI

The line that initializes v_table variable references a constructor that takes 3 values as arguments. It can take more (or less) values as well.

How could we exploit this feature to accept variable number of arguments in our procedures/functions?

The problem with the above example is that we have to have a collection type before we can use it and this would make our procedure/function dependent on such custom type.

A necessary help comes from Oracle’s built-in collection types:

sys.odcivarchar2list, sys.odcinumberlist, etc.

Let say we need to mimic Oracle’s built-in GREATEST function for a variable number of numeric arguments. Here is how we could use sys.odcinumberlist type:

CREATE OR REPLACE FUNCTION my_greatest(p_list sys.odcinumberlist)
    RETURN NUMBER
AS
    v_result NUMBER;
BEGIN
    SELECT CASE WHEN SUM(NVL2(COLUMN_VALUE,0,1))>0 THEN TO_NUMBER(NULL)
                ELSE MAX(COLUMN_VALUE)
           END
     INTO v_result
    FROM TABLE(p_list);
    RETURN v_result;
END;
/

Remember, the GREATEST function returns NULL if at least one of its arguments is NULL. That’s why we need to check for NULLs in the p_list collection.

Here is how we could test the function:

SELECT my_greatest(sys.odcinumberlist(45,2,46,65,2,1,0)) "greatest",
       my_greatest(sys.odcinumberlist(45,2,NULL,65,2,1)) "null_greatest"
FROM dual

Result:

greatest	null_greatest
65	–

The use of sys.odcinumberlist constructor is not very elegant as the data type name is very long, but it does do the trick. You can pass as many arguments to the constructor as you wish. To make things look a bit prettier, we can create a short synonym:

CREATE OR REPLACE SYNONYM nl FOR sys.odcinumberlist
/

Now, the last (testing) query will transform to the following:

SELECT my_greatest(nl(45,2,46,65,2,1,0)) "greatest",
       my_greatest(nl(45,2,NULL,65,2,1)) "null_greatest"
FROM dual

It still does not look like true “parameter array” with the arbitrary length, but it is very close.

The following is a short list of Oracle’s built-in collection types that you can use for mimicking “arbitrary number of arguments”:

• sys.odcidatelist
• sys.odciobjectlist
• sys.odcirawlist
• sys.odcinumberlist
• sys.odcivarchar2list

For anything more complex, you may need to create your own collection type.

If you find this post useful, please press the LIKE button and subscribe.

My Oracle Group on Facebook:

Also, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Suggested Reading:

Would you like to read about many more tricks and puzzles? For more clever tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.