Home » SQL & PL/SQL » SQL & PL/SQL » Left Outer Join with SubQuery
| Left Outer Join with SubQuery [message #665812] |
Fri, 22 September 2017 04:56  |
 |
suji6281
Messages: 151
Registered: September 2014
|
Senior Member |
|
|
Hi Team,
We have a scenario where we are using left outer join on Table A & Table B. The issue is when we use a subquery with left outer join in insert statement, it issues an error that sub query can't be used in a left outer join. We need this subquery to retrieve maximum effective dated row from the table. This select statement will be used to insert data into another table. Is there a way where we can include subquery in left outer joins.
Table A Data: (PS_VENDOR)
Table B Data: (PS_VENDOR_CNTCT)
SETID VENDOR ID CNTCT_SEQ_NUM EFFDT EFF_STATUS
ABC XYZ 1 08/01/2017 A
ABC XYZ 1 01/01/2017 A
SETID VENDOR ID CNTCT_SEQ_NUM EFFDT EFF_STATUS
ABC XYZ 1 08/01/2017 A
Subquery that we use for Insert Select statement as below. This Select statement is working fine but whenever use it in Insert statement it doesn't work. Its giving error as sub query can't be used in a left outer join.
select a.setid, a.vendor_id, b.cntct_seq_num, b.effdt, b.eff_status
from ps_vendor a left outer join ps_vendor_cntct b
on b.setid = a.setid
and b.vendor_id = a.vendor_id
and b.effdt = (select max(b_ed.effdt) from ps_vendor_cntct B_ED
where b.setid = b_ed.setid
and b.vendor_id = b_ed.vendor_id
and b.cntct_seq_num = b_ed.cntct_seq_num
and b_ed.eff_status = 'A'
and b_ed.effdt <= sysdate)
If above subquery is included in left outer join the insert statement doesn't work. If it is placed out of left outer join then if no row exists in
table B then no data is retrieved for table A. But left outer join should retrieve data from table A.
below is the insert statement that errors out:
Insert into PS_STAGING_TBL (
select a.setid, a.vendor_id, b.cntct_seq_num, b.effdt, b.eff_status
from ps_vendor a left outer join ps_vendor_cntct b
on b.setid = a.setid
and b.vendor_id = a.vendor_id
and b.effdt = (select max(b_ed.effdt) from ps_vendor_cntct B_ED
where b.setid = b_ed.setid
and b.vendor_id = b_ed.vendor_id
and b.cntct_seq_num = b_ed.cntct_seq_num
and b_ed.eff_status = 'A'
and b_ed.effdt <= sysdate))
CREATE TABLE PS_VENDOR (
SETID varchar(10),
vendorId varchar(6),
);
CREATE TABLE PS_VENDOR_CNTCT (
SETID varchar(10),
vendorId varchar(6),
CNTCT_SEQ_NUM Number(8,2),
EFFDT Date,
EFF_STATUS varchar(3)
);
CREATE TABLE PS_STAGING_TBL (
SETID varchar(10),
vendorId varchar(6),
CNTCT_SEQ_NUM Number(8,2),
EFFDT Date,
EFF_STATUS varchar(3)
);
Please help me with proper Insert Sql. Thank You
Regards
Sekhar
|
|
|
|
| Re: Left Outer Join with SubQuery [message #665814 is a reply to message #665812] |
Fri, 22 September 2017 05:41  |
 |
quirks
Messages: 85
Registered: October 2014
|
Member |
|
|
Well, as the ORA message says:
ORA-01799: a column may not be outer-joined to a subquery
So you need to remove the subquery from the join condition:
WITH
PS_VENDOR(SETID, VENDOR_ID) AS (SELECT 'ABC', 'XYZ' FROM DUAL),
PS_VENDOR_CNTCT(SETID
,VENDOR_ID
,CNTCT_SEQ_NUM
,EFFDT
,EFF_STATUS)
AS
(SELECT 'ABC', 'XYZ', 1, TO_DATE('08/01/2017', 'DD/MM/YYYY'), 'A' FROM DUAL
UNION ALL
SELECT 'ABC', 'XYZ', 1, TO_DATE('01/01/2017', 'DD/MM/YYYY'), 'A' FROM DUAL)
SELECT A.SETID
,A.VENDOR_ID
,B.CNTCT_SEQ_NUM
,B.EFFDT
,B.EFF_STATUS
FROM PS_VENDOR A
LEFT OUTER JOIN (SELECT SETID
,VENDOR_ID
,CNTCT_SEQ_NUM
,MAX(EFFDT) AS EFFDT
,EFF_STATUS
FROM PS_VENDOR_CNTCT
WHERE EFF_STATUS = 'A' AND EFFDT <= SYSDATE
GROUP BY SETID
,VENDOR_ID
,CNTCT_SEQ_NUM
,EFF_STATUS) B
ON (B.SETID = A.SETID AND B.VENDOR_ID = A.VENDOR_ID);
[Updated on: Fri, 22 September 2017 06:34] Report message to a moderator |
|
|
|
|
|
| Re: Left Outer Join with SubQuery [message #665903 is a reply to message #665816] |
Fri, 29 September 2017 19:05 |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Given the sample data that you provided, it looks the method that Quirks provided inserts one row and provides the results you want, as demonstrated below.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE PS_VENDOR (
2 SETID varchar(10),
3 vendorId varchar(6)
4 );
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO ps_vendor SELECT 'ABC', 'XYZ' FROM DUAL;
1 row created.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE PS_VENDOR_CNTCT (
2 SETID varchar(10),
3 vendorId varchar(6),
4 CNTCT_SEQ_NUM Number(8,2),
5 EFFDT Date,
6 EFF_STATUS varchar(3)
7 );
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO ps_vendor_cntct
2 SELECT 'ABC', 'XYZ', 1, TO_DATE('08/01/2017', 'DD/MM/YYYY'), 'A' FROM DUAL
3 UNION ALL
4 SELECT 'ABC', 'XYZ', 1, TO_DATE('01/01/2017', 'DD/MM/YYYY'), 'A' FROM DUAL;
2 rows created.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE PS_STAGING_TBL (
2 SETID varchar(10),
3 vendorId varchar(6),
4 CNTCT_SEQ_NUM Number(8,2),
5 EFFDT Date,
6 EFF_STATUS varchar(3)
7 );
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO ps_staging_tbl
2 SELECT A.SETID
3 ,A.VENDORID
4 ,B.CNTCT_SEQ_NUM
5 ,B.EFFDT
6 ,B.EFF_STATUS
7 FROM PS_VENDOR A
8 LEFT OUTER JOIN (SELECT SETID
9 ,VENDORID
10 ,CNTCT_SEQ_NUM
11 ,MAX(EFFDT) AS EFFDT
12 ,EFF_STATUS
13 FROM PS_VENDOR_CNTCT
14 WHERE EFF_STATUS = 'A' AND EFFDT <= SYSDATE
15 GROUP BY SETID
16 ,VENDORID
17 ,CNTCT_SEQ_NUM
18 ,EFF_STATUS) B
19 ON (B.SETID = A.SETID AND B.VENDORID = A.VENDORID);
1 row created.
SCOTT@orcl_12.1.0.2.0> COMMIT
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM ps_staging_tbl
2 /
SETID VENDOR CNTCT_SEQ_NUM EFFDT EFF
---------- ------ ------------- --------------- ---
ABC XYZ 1 Sun 08-Jan-2017 A
1 row selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri May 16 01:45:57 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
