CODEWITHSUNDEEP
oracle-databaseoracle10g
user2247355
user2247355

Reputation: 101

How to join to sub query Inner joins

"%s: invalid identifier" error i am getting with the below query How to join two sub joins on different key values

 select * from 
(select 

b.SYS_ID as SYS_ID1,a.IBNR_CALC_YM,
from
ibnr_pend_claims a Inner Join ibnr_prod_mapping b On a.sys_ID= b.Sys_ID and a.ibnr_prod = b.ibnr_prod)  abc
Inner Join
(select * from 
    (select 
--Count(*)
d.SYS_ID as SYS_ID1,c.IBNR_CALC_YM,
from
ibnr_prod_mapping d Inner Join ibnr_paid_claims_exp c On c.sys_ID= d.Sys_ID and c.ibnr_prod = d.ibnr_prod) def)
on   abc.SYS_ID = def.SYS_ID;

CREATE TABLE "IBNR_PEND_CLAIMS" ( 
  "SYS_ID" CHAR(1 BYTE) NOT NULL ENABLE,
  "IBNR_CALC_YM" CHAR(6 BYTE) NOT NULL ENABLE,
  "IBNR_PROD" CHAR(6 BYTE) NOT NULL ENABLE 
);
CREATE TABLE "IBNR_PAID_CLAIMS" (
  "SYS_ID" CHAR(1 BYTE) NOT NULL ENABLE, 
  "IBNR_CALC_YM" CHAR(6 BYTE) NOT NULL ENABLE, 
  "IBNR_PROD" CHAR(6 BYTE) NOT NULL ENABLE 
);
CREATE TABLE "ibnr_prod_mapping" ( 
  "SYS_ID" CHAR(1 BYTE) NOT NULL ENABLE,
  "IBNR_CALC_YM" CHAR(6 BYTE) NOT NULL ENABLE,
  "IBNR_PROD" CHAR(6 BYTE) NOT NULL ENABLE 
);

Upvotes: 0

Views: 44

Answers (2)

neshkeev
neshkeev

Reputation: 6476

Try this:

select * 
  from 
(select b.SYS_ID as SYS_ID1,a.IBNR_CALC_YM
  from ibnr_pend_claims a 
 Inner Join ibnr_prod_mapping b On a.sys_ID= b.Sys_ID and a.ibnr_prod = b.ibnr_prod
) abc
 Inner Join
(select * from 
    (select d.SYS_ID as SYS_ID1,c.IBNR_CALC_YM
       from ibnr_prod_mapping d 
      Inner Join ibnr_paid_claims_exp c On c.sys_ID= d.Sys_ID and c.ibnr_prod = d.ibnr_prod
    )
) def on abc.SYS_ID1 = def.SYS_ID1

Upvotes: 1

user2247355
user2247355

Reputation: 101

select *  from 
(select b.SYS_ID as SYS_ID1,a.IBNR_CALC_YM from ibnr_pend_claims a 
 Inner Join ibnr_prod_mapping b On a.sys_ID= b.Sys_ID and a.ibnr_prod = b.ibnr_prod) abc
 Inner Join
(select d.SYS_ID as SYS_ID1,c.IBNR_CALC_YM from ibnr_prod_mapping d 
      Inner Join ibnr_paid_claims_exp c On c.sys_ID= d.Sys_ID and c.ibnr_prod = d.ibnr_prod) def on abc.SYS_ID1 = def.SYS_ID1;

Upvotes: 0

Related Questions