0

I'd like to insert the data after unpivoting it. The statement needs to be a merge statemenet. However, I am getting ora-30926 error, and I can't really figure out how to solve it.

Here the data table:

------------------------------------------------------------------------------------
|Employee_id | work_experience_1 | work_experience_2 | work_experience_3 | language |
-------------------------------------------------------------------------------------
| 123        |  C&S              |   Deloitte        |         TCS       |     FI   |
| 211        |   E&Y             |   Microsoft       |                   |     FI   |
| 213        |   C&S             |                   |                   |     FI   |
-------------------------------------------------------------------------------------

So first before entering the data, I need to unpivot it.

---------------------------------- 
|Employee_id | work_experience   |
----------------------------------
| 123        |   C&S             |
| 123        |   Deloitte        |
| 123        |     TCS           |
| 211        |   E&Y             |
| 211        |   Microsoft       |
| 213        |   C&S             |
----------------------------------

Here is what I have done. The inserting part works ok but updating part fails.

MERGE INTO arc_hrcs.user_multi_work_exp work_exp
USING (SELECT user_id, work_experience_lang, work_exp_fi FROM
         (SELECT ext.user_id, tmp_work.employee_id, tmp_work.work_experience_1, tmp_work.work_experience_2, tmp_work.work_experience_3, tmp_work.work_experience_4, tmp_work.work_experience_5, tmp_work.work_experience_6, tmp_work.work_experience_7, tmp_work.work_experience_8, tmp_work.work_experience_9, tmp_work.work_experience_10, tmp_work.work_experience_lang FROM arc_hrcs.hr_extension_data ext
            JOIN aa_work_exp_tmp tmp_work ON tmp_work.employee_id = ext.employee_id) 
            UNPIVOT (work_exp_fi FOR work_code IN (work_experience_1 AS 'a', work_experience_2 AS 'b', work_experience_3 AS 'c', work_experience_4 AS 'd',  work_experience_5 AS 'e', work_experience_6 AS 'f', work_experience_7 AS 'g', work_experience_8 AS 'h', work_experience_9 AS 'i', work_experience_10 AS 'j'))) r
ON (work_exp.user_id = r.user_id AND r.work_experience_lang LIKE '%FI%' )
WHEN NOT MATCHED THEN
     INSERT (work_exp.user_id, work_exp.work_experience_fi)
     VALUES (r.user_id, r.work_exp_fi)
WHEN MATCHED THEN
     UPDATE SET work_exp.work_experience_fi = r.work_exp_fi

What can I do to make it working? Cheers and thx in advance :-)

Jaanna
  • 1,620
  • 9
  • 26
  • 46

2 Answers2

0

afaik, the MERGE statement needs UNIQUE or PRIMARY KEY columns specified in the ON clause and also on the target table. Looking at your data sample you are probably missing it (them) on the source table.

OraNob
  • 684
  • 3
  • 9
0

Essentially, the query in the USING cause is a multiple-row subquery, when it needs to be a single-row subquery. I would try running the subquery in isolation and attempt to fix the logic of the WHERE cause so that you bring back a unique row.

http://blog.mclaughlinsoftware.com/2010/03/05/stable-set-of-rows/

fras
  • 123
  • 1
  • 3
  • 8