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 Go to next message
suji6281
Messages: 49
Registered: September 2014
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)
SetID     Vendor ID
ABC       XYZ

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
The output should be:
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 Go to previous messageGo to next message
quirks
Messages: 60
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 #665816 is a reply to message #665814] Fri, 22 September 2017 07:03 Go to previous messageGo to next message
suji6281
Messages: 49
Registered: September 2014
Member
Thanks Quirks for Quick reply.
But this doesn't work for me.
with the given sample data, Select statement should return only one row and that should be Inserted into another table.
Re: Left Outer Join with SubQuery [message #665903 is a reply to message #665816] Fri, 29 September 2017 19:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8779
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.
Re: Left Outer Join with SubQuery [message #666047 is a reply to message #665903] Tue, 10 October 2017 07:12 Go to previous message
suji6281
Messages: 49
Registered: September 2014
Member
thanks for the solution.
Previous Topic: Finding characters other than keyboard character
Next Topic: Show multiple count in one result set
Goto Forum:
  


Current Time: Fri Dec 15 04:00:29 CST 2017

Total time taken to generate the page: 0.07015 seconds