jayjaybigs
IS-IT--Management
Hello All,
I am working with several tables here.
The object here is to extract amounts from both bch_p2k_pr_todate_pc_amounts and bch_temp_todates.
Most importantly extract all data from either table even when there is no corresponding value from other table.
Bcause there is no no mathching columns in the above tables, I had to use intermediary tables to connect and to extract other values. Based on my research with the BA we were expecting 99 rows for this particular employee below. Here is my effort:
select id.person_code,
id.last_name,
id.first_name,
td.UNIT_CODE,
td.GROUP_CODE,
hd.todate_type,
co.pc_code,
co.description,
am.amount p2k_amount,
td.amount adp_amount,
(am.amount - td.amount) Variance
from p2k_hr_identities id,
p2k_hr_employments emp,
p2k_pr_todate_headers hd,
p2k_pr_pay_components co,
bch_temp_todates td,
p2k_pr_todate_pc_amounts am
where id.id = emp.eid_id
and emp.id = hd.eem_id
and co.id = am.ppc_id
and am.ptd_id(+) = hd.id
and hd.todate_type = 'Y'
and hd.calendar_period = 2004
and td.person_code(+) = id.person_code
and td.PC_CODE = co.PC_CODE
and id.person_code = 200095
order by id.last_name
FOR THIS effort I recieved 78 rows, however I decided to create new temp table that would allow me to join the two amounts tables using the NEW ANSI SQL/92 so that I can get all the values that do not have corresponding match in each table. Here is the new effort.
select co.pc_code,
id.person_code,
id.last_name,
id.first_name,
td.UNIT_CODE,
td.GROUP_CODE,
hd.todate_type,
co.description,
am.amount p2k_amount,
td.amount adp_amount,
(am.amount - td.amount) Variance
from (bch_p2k_pr_todate_pc_amounts am full outer join bch_temp_todates td
on am.pc_code = td.pc_code),
p2k_hr_identities id,
p2k_hr_employments emp,
p2k_pr_todate_headers hd,
p2k_pr_pay_components co
where id.id = emp.eid_id
and emp.id = hd.eem_id
and co.id = am.ppc_id
and am.ptd_id = hd.id
and hd.todate_type = 'Y'
and hd.calendar_period = 2004
and td.person_code = id.person_code
and td.PC_CODE = co.PC_CODE
and id.person_code = 200095
order by id.last_name
Based on my research I was expecting 99 rows for this particular employee
When I ran the above code it gives "no row returned"
However, when I comment out line:
--and co.id = am.ppc_id
It gives 16 lines
When I comment out lines
--and co.id = am.ppc_id
--and am.ptd_id = hd.id
It gives 12490 rows
Please Help if there is any modification to do to retrieve the desired 99 rows
I am working with several tables here.
The object here is to extract amounts from both bch_p2k_pr_todate_pc_amounts and bch_temp_todates.
Most importantly extract all data from either table even when there is no corresponding value from other table.
Bcause there is no no mathching columns in the above tables, I had to use intermediary tables to connect and to extract other values. Based on my research with the BA we were expecting 99 rows for this particular employee below. Here is my effort:
select id.person_code,
id.last_name,
id.first_name,
td.UNIT_CODE,
td.GROUP_CODE,
hd.todate_type,
co.pc_code,
co.description,
am.amount p2k_amount,
td.amount adp_amount,
(am.amount - td.amount) Variance
from p2k_hr_identities id,
p2k_hr_employments emp,
p2k_pr_todate_headers hd,
p2k_pr_pay_components co,
bch_temp_todates td,
p2k_pr_todate_pc_amounts am
where id.id = emp.eid_id
and emp.id = hd.eem_id
and co.id = am.ppc_id
and am.ptd_id(+) = hd.id
and hd.todate_type = 'Y'
and hd.calendar_period = 2004
and td.person_code(+) = id.person_code
and td.PC_CODE = co.PC_CODE
and id.person_code = 200095
order by id.last_name
FOR THIS effort I recieved 78 rows, however I decided to create new temp table that would allow me to join the two amounts tables using the NEW ANSI SQL/92 so that I can get all the values that do not have corresponding match in each table. Here is the new effort.
select co.pc_code,
id.person_code,
id.last_name,
id.first_name,
td.UNIT_CODE,
td.GROUP_CODE,
hd.todate_type,
co.description,
am.amount p2k_amount,
td.amount adp_amount,
(am.amount - td.amount) Variance
from (bch_p2k_pr_todate_pc_amounts am full outer join bch_temp_todates td
on am.pc_code = td.pc_code),
p2k_hr_identities id,
p2k_hr_employments emp,
p2k_pr_todate_headers hd,
p2k_pr_pay_components co
where id.id = emp.eid_id
and emp.id = hd.eem_id
and co.id = am.ppc_id
and am.ptd_id = hd.id
and hd.todate_type = 'Y'
and hd.calendar_period = 2004
and td.person_code = id.person_code
and td.PC_CODE = co.PC_CODE
and id.person_code = 200095
order by id.last_name
Based on my research I was expecting 99 rows for this particular employee
When I ran the above code it gives "no row returned"
However, when I comment out line:
--and co.id = am.ppc_id
It gives 16 lines
When I comment out lines
--and co.id = am.ppc_id
--and am.ptd_id = hd.id
It gives 12490 rows
Please Help if there is any modification to do to retrieve the desired 99 rows