0

Hi I am getting syntax error while running the below historical statement in oracle db , can you please let me know how to fix the below error ?

MERGE INTO d_prod_fld dp USING
  (SELECT stg_prod_fld_id,
          prod_cd_id,
          country_name
   FROM stg_prod_fld_delta pd
   LEFT OUTER JOIN d_loc dl ON (dl.prod_cd_num = lpad(pd.prod_cd_id, 3, '0'))
   WHERE pd.efft_to > trunc(sysdate+1)
     AND pd.prod_cd_id IS NOT NULL 
  ) stg 
ON (dp.cd_id = stg.stg_prod_fld_id) 
WHEN matched THEN
UPDATE SET dl.prod_country=stg.country_name;
  • d_prod_fld - target dimension table ,
  • stg_prod_fld_delta - stage table ,
  • d_loc - look up table

basically when i tried to run the above query in sandbox it is running fine ,but when i tried to run in actual development environment it is showing the above error -

Error starting at line : 1 in command -

Error report - SQL Error: ORA-30926: unable to get a stable set of rows in the source tables 30926. 00000 - "unable to get a stable set of rows in the source tables"

*Cause: A stable set of rows could not be got because of large dml activity or a non-deterministic where clause.

*Action: Remove any non-deterministic where clauses and reissue the dml.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Data2explore
  • 452
  • 6
  • 16
  • Simple "solution" might be using `select distinct stg_prod_fld_id, ...`. See if it helps. – Littlefoot Mar 28 '20 at 20:44
  • Does this answer your question? [ORA-30926: unable to get a stable set of rows in the source tables](https://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables) – Digvijay S Mar 29 '20 at 02:17
  • Hi Littlefoor i have tried using distinct the result expected is 614 but after running the update 1364 rows but when i do the select 614 only but why it is updating 1364 rows ? – Data2explore Mar 30 '20 at 18:06

0 Answers0