sql question

[jayjaybigs]

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

 

[knowlgo]

It might be useful to check your column sizes for these datatypes if they're of the Varchar variety. If you've got leading or trailing spaces you'll need to trim() them. Id's can be tricky that way.

You're getting the 12490 rows returned because it doesn't have that key piece of data, being n_id to match against the emp.

I'd try the trim() function on all of your id fields and go from there.