MacLochlainns Weblog

It’s interesting to watch people try to solve problems. For example, the student is required to use a scalar subquery in a SQL lab exercise that I wrote. It should be a simple fix. The problem is structured with an incorrect foreign key value in an external CSV file and the restriction that you can not replace the value in the external CSV file. I hoped that students would see the easiest option was to write a scalar subquery in the SELECT clause to replace the value found in the external file. There’s even a hint about how to use a scalar subquery.

Students who are new to SQL can take very interesting approaches to solve problems. The flexibility of SQL can lead them to solve problems in interesting ways. While the following solution worked to solve the problem, it’s wrong on two levels:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

INSERT INTO TRANSACTION
(SELECT   transaction_s1.NEXTVAL
 ,        tr.transaction_account
 ,        CASE
            WHEN NOT tr.transaction_type =
             (SELECT common_lookup_id
              FROM   common_lookup
              WHERE  common_lookup_table = 'TRANSACTION'
              AND    common_lookup_column = 'TRANSACTION_TYPE'
              AND    common_lookup_type = 'CREDIT') THEN
              cl.common_lookup_id
          END AS transaction_type
 ,        tr.transaction_date
 ,       (tr.transaction_amount / 1.06) AS transaction_amount
 ,        tr.rental_id
 ,        tr.payment_method_type
 ,        tr.payment_account_number
 ,        tr.created_by
 ,        tr.creation_date
 ,        tr.last_updated_by
 ,        tr.last_update_date
 FROM     transaction_reversal tr CROSS JOIN common_lookup cl
 WHERE    cl.common_lookup_table = 'TRANSACTION'
 AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
 AND      cl.common_lookup_type = 'CREDIT');

The CASE statement on lines 4 through 12 substitutes a value only when the source value is not a match. That means if the source file is ever correct a null value would become the transaction_type column value, which would make the statement fail because the transaction_type column is NOT NULL constrained in the target transaction table. Therefore, the logic of the student’s approach requires adding an ELSE clause to the CASE statement for the event that the source file is ever corrected. The modified CASE statement would be =the following:

4
5
6
7
8
9
10
11
12
13
14

 ,        CASE
            WHEN NOT tr.transaction_type =
             (SELECT common_lookup_id
              FROM   common_lookup
              WHERE  common_lookup_table = 'TRANSACTION'
              AND    common_lookup_column = 'TRANSACTION_TYPE'
              AND    common_lookup_type = 'CREDIT') THEN
              cl.common_lookup_id
          ELSE
            tr.transaction_type
          END AS transaction_type

The second element of student thought at issue is the CROSS JOIN to the in-line view. It does one thing right and another wrong. It uses the unique key to identify a single row, which effectively adds all the columns for that one row to all rows returned from the external transaction_reversal table. The CROSS JOIN is a correct approach to adding values for computations to a query when you need those columns for computations. The problem with this CROSS JOIN logic may not be immediately obvious when you write it in ANSI SQL 1992 syntax, but it should become obvious when you replace the inline view with a Common Table Expression (CTE) in ANSI SQL 1999 syntax, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

INSERT INTO TRANSACTION
(WITH cte AS
 (SELECT *
  FROM   common_lookup
  WHERE  common_lookup_table = 'TRANSACTION'
  AND    common_lookup_column = 'TRANSACTION_TYPE'
  AND    common_lookup_type = 'CREDIT')
 SELECT   transaction_s1.NEXTVAL
 ,        tr.transaction_account
 ,        cte.common_lookup_id AS transaction_type
 ,        tr.transaction_date
 ,       (tr.transaction_amount / 1.06) AS transaction_amount
 ,        tr.rental_id
 ,        tr.payment_method_type
 ,        tr.payment_account_number
 ,        tr.created_by
 ,        tr.creation_date
 ,        tr.last_updated_by
 ,        tr.last_update_date
 FROM     transaction_reversal tr CROSS JOIN cte);

Unfortunately, you would discover that Oracle Database 11g does not support the use of an ANSI SQL 1999 WITH clause inside as the source for an INSERT statement. Oracle Database 12c does support the use of the ANSI SQL 1999 WITH clause inside a subquery of an INSERT statement. That’s an “Oops!” for Oracle 11g because that means the Oracle database fails to meet the ANSI SQL 1999 compliance test. 😉 Great that they fixed it in Oracle 12c. While the nested query would work in Oracle as an ordinary query (outside of an INSERT statement). It raises the following error when you embed it in an INSERT statement:

ERROR AT line 20:
ORA-32034: unsupported USE OF WITH clause

The WITH clause does highlight a key problem with the idea of a CROSS JOIN in this situation. You don’t need all the columns from the common_lookup table. You only need the common_lookup_id column. That make the CROSS JOIN approach suboptimal if it worked.

The complex logic in the original approach is wasted. That’s true because the common_lookup_id value can be supplied to each row as the value from a scalar subquery. The scalar query runs once and the result is placed in the return set for each row. You implement the scalar subquery in the SELECT clause, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

INSERT INTO TRANSACTION
(SELECT   transaction_s1.NEXTVAL
 ,        tr.transaction_account
 ,       (SELECT common_lookup_id
          FROM   common_lookup
          WHERE  common_lookup_table = 'TRANSACTION'
          AND    common_lookup_column = 'TRANSACTION_TYPE'
          AND    common_lookup_type = 'CREDIT') AS transaction_type
 ,        tr.transaction_date
 ,       (tr.transaction_amount / 1.06) AS transaction_amount
 ,        tr.rental_id
 ,        tr.payment_method_type
 ,        tr.payment_account_number
 ,        tr.created_by
 ,        tr.creation_date
 ,        tr.last_updated_by
 ,        tr.last_update_date
 FROM     transaction_reversal tr);

There really was no intent or logical outcome where the value from the original CASE statement would be different than the subquery’s common_lookup_id value. That fact makes adding an ELSE clause useless, and the solution viable though inefficient. Also, there was no need for the additional columns from the common_lookup table because they are unused. The subquery on lines 4 through 8 provides the optimal solution and improved efficiency.

Developers should ask themselves two questions when they write SQL:

• If my logic is so elegant why do I need it to be so elegant?
• Is there a simpler solution to provide the desired result set?

If there aren’t good answers to both questions, they should re-write it. I hope the examples answer questions and help folks solve problems.