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.