SQL tips: Failsave Inserts – never mix up column orders again!

Imagine you have an Insert statement like this:

INSERT INTO target_table
    (measure_id,
     value_01,
     value_02,
     value_03,
     value_04,
     value_05,
     value_06,
     value_07)
SELECT load_id * 100 AS measure_id,
       val_1 AS value_01,
       CASE
           WHEN val_2 < 10 THEN 1
           WHEN val_2 < 100 THEN 2
           WHEN val_2 < 1000 THEN 3
           ELSE 4 
       END  AS value_02,
       val_3 AS value_03,
       val_4 AS value_04,
       CASE
           WHEN val_5 = 1 THEN 1
           WHEN val_5 = 2 THEN 20
           WHEN val_5 > 2 THEN 300
           ELSE null
       END AS value_05,
       CASE
           WHEN val_5 IN (1, 5, 7, 9, 11) THEN 21
           WHEN val_5 IN (2, 3) THEN 22
           WHEN val_5 IN (4, 6, 8, 10) THEN 23
           ELSE 24
        END AS value_06,
       val_6 * 100 + val_1 AS value_07
  FROM measure_table
 WHERE load_day > trunk(sys_date);

A common mistake

I have already added the names of the target columns, but it is still hard to make sure that all columns in the Select are at the right position!

Now I have added a new row  but made a mistake!
Do you see what went wrong?

INSERT INTO target_table
    (measure_id,
     value_01,
     value_02,
     value_03,
     value_04,
     value_05,
     value_06,
     value_07,
     value_08)
SELECT load_id * 100 AS measure_id,
       val_1 AS value_01,
       CASE
           WHEN val_2 < 10 THEN 1
           WHEN val_2 < 100 THEN 2
           WHEN val_2 < 1000 THEN 3
           ELSE 4 
       END  AS value_02,
       val_3 AS value_03,
       val_4 AS value_04,
       CASE
           WHEN val_5 = 1 THEN 1
           WHEN val_5 = 2 THEN 20
           WHEN val_5 > 2 THEN 300
           ELSE null
       END AS value_05,
       CASE
           WHEN val_5 IN (1, 5, 7, 9, 11) THEN 21
           WHEN val_5 IN (2, 3) THEN 22
           WHEN val_5 IN (4, 6, 8, 10) THEN 23
           ELSE 24
        END AS value_06,
       val_6 * 100 + val_1 + val_02 AS value_08,
       val_6 * 100 + val_1 AS value_07
  FROM measure_table
 WHERE load_day > trunk(sys_date);

I got confused and added the new line above the column for value_07. I should have put it below!
Now the values meant for column value_08 will be inserted into column value_07, and vice versa.

 

Solution

Here is a simple but effective solution, that prevents mistakes like that:

INSERT INTO target_table
    (measure_id,
     value_01,
     value_02,
     value_03,
     value_04,
     value_05,
     value_06,
     value_07,
     value_08)
SELECT measure_id,
       value_01,
       value_02,
       value_03,
       value_04,
       value_05,
       value_06,
       value_07,
       value_08
  FROM (SELECT load_id * 100 AS measure_id,
               val_1 AS value_01,
               CASE
                   WHEN val_2 < 10 THEN 1
                   WHEN val_2 < 100 THEN 2
                   WHEN val_2 < 1000 THEN 3
                   ELSE 4 
               END  AS value_02,
               val_3 AS value_03,
               val_4 AS value_04,
               CASE
                   WHEN val_5 = 1 THEN 1
                   WHEN val_5 = 2 THEN 20
                   WHEN val_5 > 2 THEN 300
                   ELSE null
               END AS value_05,
               CASE
                   WHEN val_5 IN (1, 5, 7, 9, 11) THEN 21
                   WHEN val_5 IN (2, 3) THEN 22
                   WHEN val_5 IN (4, 6, 8, 10) THEN 23
                   ELSE 24
                END AS value_06,
               val_6 * 100 + val_1 + val_02 AS value_08,
               val_6 * 100 + val_1 AS value_07
          FROM measure_table
         WHERE load_day > trunk(sys_date));

I added an outer Select around the values Select and copied the column list from the Insert part right to the outer Select.
Just copy and past – no misplacement possible!

The inner Select still has to deliver all the columns, but now even with the right names assigned.
The order of the columns in the inner Select is now without any meaning, so my “misplacement” is fixed.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s