Home » SQL & PL/SQL » SQL & PL/SQL » merge issue
merge issue [message #662114] |
Tue, 18 April 2017 06:17 |
|
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 #662121 is a reply to message #662117] |
Tue, 18 April 2017 07:42 |
|
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 #662127 is a reply to message #662123] |
Tue, 18 April 2017 08:55 |
cookiemonster
Messages: 13943 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 |
cookiemonster
Messages: 13943 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 |
cookiemonster
Messages: 13943 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 #662168 is a reply to message #662114] |
Wed, 19 April 2017 21:22 |
|
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 |
cookiemonster
Messages: 13943 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.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:44:30 CDT 2024
|