Home » SQL & PL/SQL » SQL & PL/SQL » merge issue
merge issue [message #662114] Tue, 18 April 2017 06:17 Go to next message
JEWEL78
Messages: 6
Registered: April 2017
Junior Member
I have below sql query
select distinct ur.user_role_id
from main.user_roles your
inner join main1.deal d on (ur.deal_id = d.deal_id) and (d.deal_status_id <> 532)
left join main2.tb_edb_employee e on (ur.user_id = e.emp_id)
where (e.employee_status_id <> 1) or (e.emp_id is null)
union
select distinct ur.user_role_id
from main.user_roles your
left join main2.tb_edb_employee e on (ur.user_id = e.emp_id)
where (ur.deal_id is null) and ((e.employee_status_id <> 1) or (e.emp_id is null))


Below is the error while I am merge statement using above statement.
error :ORA-30926: unable to get a stable set of rows in the source tables

merge statement

MERGE INTO user_roles 
    USING (select distinct ur.user_role_id,E.EMP_ID
from main.user_roles your
inner join main1.deal d on (ur.deal_id = d.deal_id) and (d.deal_status_id <> 532)
left join main2.tb_edb_employee e on (ur.user_id = e.emp_id)
where (e.employee_status_id <> 1) or (e.emp_id is null)
union
select distinct ur.user_role_id,E.EMP_ID
from main.user_roles your
left join main2.tb_edb_employee e on (ur.user_id = e.emp_id)
where (ur.deal_id is null) and ((e.employee_status_id <> 1) or (e.emp_id is null)) 
) s
   on (user_id = s.emp_id(+))
  WHEN MATCHED THEN
    UPDATE 
         SET end_date = sysdate,
             audit_who = 'vt',
             audit_emp_id = sa,
             audit_when = sysdate where deal_id is null



--moderator edit: added [code] tags, please do so yourself in futute.

[Updated on: Tue, 18 April 2017 06:25] by Moderator

Report message to a moderator

Re: merge issue [message #662117 is a reply to message #662114] Tue, 18 April 2017 06:33 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Your code is impossible. You have this "...from main.user_roles your..." but then then you refer to tables with the alias "ur" subsequently. What is the code that you are actually running?
Furthermore, you have a horrible combination of ANSI join syntax and Oracle native join syntax. Use ANSI only, and your code will be easier to debug.
Lastly, please format it. I do not know how people can work with unformatted code. If you want it done automatically, try this one, http://www.dpriver.com/pp/sqlformat.htm

[Updated on: Tue, 18 April 2017 06:34]

Report message to a moderator

Re: merge issue [message #662119 is a reply to message #662114] Tue, 18 April 2017 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-30926: 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.
Re: merge issue [message #662121 is a reply to message #662117] Tue, 18 April 2017 07:42 Go to previous messageGo to next message
JEWEL78
Messages: 6
Registered: April 2017
Junior Member
Hi Team,
Need your help below

ERROR details :error :ORA-30926: unable to get a stable set of rows in the source tables

MERGE INTO user_roles
USING (SELECT DISTINCT ur.user_role_id, E.EMP_ID
FROM main.user_roles your
INNER JOIN main1.deal d
ON (ur.deal_id = d.deal_id)
AND (d.deal_status_id <> 532)
LEFT JOIN main3.tb_edb_employee e
ON (ur.user_id = e.emp_id)
WHERE (e.employee_status_id <> 1) OR (e.emp_id IS NULL)
UNION
SELECT DISTINCT ur.user_role_id, E.EMP_ID
FROM main.user_roles your
LEFT JOIN
main3.tb_edb_employee e
ON (ur.user_id = e.emp_id)
WHERE (ur.deal_id IS NULL)
AND ( (e.employee_status_id <> 1) OR (e.emp_id IS NULL))) s
ON (user_id = s.emp_id(+))
WHEN MATCHED
THEN
UPDATE SET end_date = SYSDATE,
audit_who = 'vt',
audit_emp_id = 'sa',
audit_when = SYSDATE
WHERE deal_id IS NULL

[Updated on: Tue, 18 April 2017 07:46]

Report message to a moderator

Re: merge issue [message #662122 is a reply to message #662121] Tue, 18 April 2017 07:45 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You haven't bothered to read the replies you have already, have you?
Re: merge issue [message #662123 is a reply to message #662122] Tue, 18 April 2017 07:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Need your help below
We can't help you if you refuse to help yourself by reading & following Posting Guidelines.
Re: merge issue [message #662127 is a reply to message #662123] Tue, 18 April 2017 08:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Some general comments:
1) An outer-join operator on the ON clause of a merge statement is pointless, that join is always treated as outer, otherwise merge wouldn't be merge.
2) Union does a distinct, so the separate distincts are pointless
3) Outer-joining tb_edb_employee is pointless since you're using tb_edb_employee.emp_id in the on clause of the merge
4) That also means that the OR e.emp_id IS NULL is pointless, if it's null you can't use it to join in the main ON clause
5) The differences between the two selects aren't great enough to need to do a union, you can just combine them into one.
6) Since you don't appear to be using anything from the using section in the update you might find it easier to write this as an update with an exists clause in the where clause.
Re: merge issue [message #662129 is a reply to message #662127] Tue, 18 April 2017 09:02 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
7) What you're doing with deal_id seems pretty suspect as well - you're joining on it and checking it's null.
Re: merge issue [message #662130 is a reply to message #662129] Tue, 18 April 2017 09:05 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Bottom line - blindly sticking that select into a merge without trying to understand what it does makes for over-complicated code that doesn't work.
Re: merge issue [message #662132 is a reply to message #662121] Tue, 18 April 2017 09:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.

Format you SQL, if you don't know how to do it, learn it using SQL Formatter.

(John already told you that but I think it is worth to repeat for you.)

Re: merge issue [message #662168 is a reply to message #662114] Wed, 19 April 2017 21:22 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
The Merge is most likely failing because of a issue with a particular data pattern
in the "user_roles" Table and possibly in the "deal" Table.

Not sure what the Constraints you are using on your Tables whether they are Primary or Unique
Constraints. The DDL would have been helpful along with some test data. Also Formatted Code
makes analysis quicker and easier. There is a nice customizable format option built into
SQL Developer Editor and a website that I listed on a post on this forum which can do a fair job
of formatting code called Instant SQL Formatter at http://www.dpriver.com/pp/sqlformat.htm. Also
read and use the Code Tag Information which will aid you in maintaining a formatted code text in
your post.

Also it appears that this SQL Command may have been part of a larger script because in the
Update Clause there is a reference to a value called "sa" in the line "audit_emp_id = sa,"
(Used a Constant for this item). In addition there were several Table Aliases listed as "your"
which I used the Table Alias of "ur".

One scenario that can cause the error is if query in the "Using" Clause of the Merge Command
returns more than one record with a duplicate search key which is used in the ON Clause.
In this case if "emp_id" appears more than once in the "user_roles" Table with the condition listed below,
the error ORA-30926 would be generated by the Merge Command using the 2nd half of the Using Query:

SELECT DISTINCT
      ur.user_role_id
     ,e.emp_id
FROM   user_roles  your
LEFT JOIN tb_edb_employee e
 ON (ur.user_id = e.emp_id)
WHERE (ur.deal_id is null)
AND ((e.employee_status_id <> 1)
     OR (e.emp_id is null))


Conditions Scenario:
If there are multiple records in the "user_roles" Table with the
- Employee must exist in the "tb_edb_employee" with a "employee_status_id" not equal to 1.
- In the "user_roles" Table.
- 2 or more records with the same "emp_id" (Employee ID).
- "user_role_id"s must be assigned values including possibly Null.
- At least 2 of the records need to have Null "deal_id"s for a given "emp_id".

The Null "deal_id" in the "user_roles" Table could have occurred because there maybe a
record in the "deals" Table that has a Null "deal_id" Record Value.


Merge Query Reformatted:

MERGE INTO user_roles
  USING (
         SELECT DISTINCT
                ur.user_role_id
               ,e.emp_id
         FROM   user_roles  your
         INNER JOIN deal    d
           ON      (ur.deal_id       = d.deal_id)
               AND (d.deal_status_id <> 532)
         LEFT JOIN tb_edb_employee e
           ON  (ur.user_id           = e.emp_id)
         WHERE (e.employee_status_id <>  1)
         OR    (e.emp_id             IS NULL)
        UNION
         SELECT DISTINCT
                ur.user_role_id
               ,e.emp_id
         FROM   user_roles  your
         LEFT JOIN tb_edb_employee e
           ON (ur.user_id = e.emp_id)
         WHERE (ur.deal_id is null)
         AND ((e.employee_status_id <> 1)
               OR (e.emp_id is null)) 
        ) s
   ON (user_id = s.emp_id(+))
  WHEN MATCHED THEN
    UPDATE 
         SET  end_date     = SYSDATE
             ,audit_who    = 'vt'
             ,audit_emp_id = sa
             ,audit_when   = SYSDATE
      WHERE deal_id IS NULL;

Test Setup Used for Issue:
(I made a few assumptions and included only the Tables/Columns listed
in the Merge Command.)

CREATE TABLE deal
(
  deal_id             NUMBER(5)
 ,deal_status_id      NUMBER(5)
);

INSERT INTO deal(deal_id, deal_status_id) VALUES (1, 1);
INSERT INTO deal(deal_id, deal_status_id) VALUES (NULL, NULL);


CREATE TABLE emp
(
  emp_id              NUMBER(5)
);

INSERT INTO EMP (EMP_ID) VALUES   (1);


CREATE TABLE tb_edb_employee
(
  emp_id              NUMBER(5)
 ,employee_status_id  NUMBER(5)
);

INSERT INTO TB_EDB_EMPLOYEE (EMP_ID, EMPLOYEE_STATUS_ID) VALUES (1, 2);


CREATE TABLE user_roles
(
  user_id             NUMBER(5)
 ,user_role_id        NUMBER(5)
 ,deal_id             NUMBER(5)
 ,end_date            DATE
 ,audit_who           VARCHAR2(5)
 ,audit_emp_id        NUMBER(5)
 ,audit_when          DATE
);

INSERT INTO USER_ROLES (user_id, user_role_id, deal_id, end_date, audit_who, audit_emp_id, audit_when)
 VALUES   (1, 1, NULL, NULL, NULL,     NULL, NULL);

INSERT INTO USER_ROLES (user_id, user_role_id, deal_id, end_date, audit_who, audit_emp_id, audit_when)
 VALUES (1, NULL, NULL, NULL, NULL, NULL, NULL);

COMMIT;

Re: merge issue [message #662186 is a reply to message #662168] Thu, 20 April 2017 03:55 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd say the main problem is that the OP took a query that wasn't hugely relevant to the merge the wanted to do.

Applying my points 1-5 above gives
MERGE INTO user_roles
  USING (SELECT ur.user_role_id
               ,e.emp_id
         FROM user_roles  your
         JOIN tb_edb_employee e
         ON ur.user_id = e.emp_id
         WHERE (ur.deal_id is null
                OR ur.deal_id IN (SELECT deal_id
                                  FROM deal
                                  WHERE d.deal_status_id <> 532)
               )
         AND e.employee_status_id <> 1
        ) s
   ON user_id = s.emp_id
  WHEN MATCHED THEN
    UPDATE 
         SET  end_date     = SYSDATE
             ,audit_who    = 'vt'
             ,audit_emp_id = sa
             ,audit_when   = SYSDATE
      WHERE deal_id IS NULL;
Applying point 6 gives:
UPDATE user_roles
SET end_date = SYSDATE
   ,audit_who    = 'vt'
   ,audit_emp_id = sa
   ,audit_when   = SYSDATE
WHERE deal_id IS NULL
AND user_id IN (SELECT e.emp_id
                FROM user_roles  your
                JOIN tb_edb_employee e
                ON ur.user_id = e.emp_id
                WHERE (ur.deal_id is null
                       OR ur.deal_id IN (SELECT deal_id
                                         FROM deal
                                         WHERE d.deal_status_id <> 532)
                      )
                AND e.employee_status_id <> 1
               )

Which would fix the error.
And given point 7, depending on the relationships between the tables, that OR in the sub-query may be pointless as well.
Previous Topic: Regexp magic ! (I hope)
Next Topic: Need help to form Query to split count into multiple rows
Goto Forum:
  


Current Time: Fri Mar 29 02:44:37 CDT 2024