Problem with linking tables

[ianoctdec]

Hi everyone,
I'm using Crystal Reports 10, Oracle database with an ODBC connection.
I have 2 tables: inv_pcs (part_id, length, whse, loc, qty) and phys_inv_pcs (phys_ct_id, tag_no, part_id, qty, length, whse, loc). I have to create a report that will show all the records that match in both tables and also the records from inv_pcs table that are not in phys_inv_pcs and those from the phys_in_pcs that are not in inv_pcs.
inv_pcs LOJ phys_inv_pcs gave me all the matches in both tables and what is in inv_pcs and not in phys_inv_pcs. How can I get the records from phys_inv_pcs that are not in inv_pcs? Can this be done in the same report or if not, how the query should look like to obtain those records? Crystal won't let me to do a full outer join on the tables. Data sample:
inv_pcs table
part_id length qty whse loc qty
1 5 12 a b
1 7 10 a b
2 8 20 a b
6 5 15 a b

phy_inv_pcs
part_id length tag_no qty whse loc phys_ct_id
1 5 1 12 a b abc
2 10 3 5 a b abc
6 5 5 13 a b abc

On the report I should have:
part_id length inv_qty whse loc count_qty phys_ct_id
1 5 12 a b 12 abc
1 7 10 a b
2 8 20 a b
2 10 a b 5 abc
6 5 15 a b 13 abc

Thank you for your help and time!
Dana

 

[synapsevampire]

You'll get much better performance by using Oracle native, and if you're using ODBC, use the CR supplied ODBC for Oracle, not Oracles (at least in previous versions you want the CR supplied ODBC).

You have options in addressing this. I would consider creating a Union Query here.

select 'inv_pcs' Source, field1, field2,etc...
union all
select 'phys_inv_pcs' Source, field1, field2,etc...

Now you have all the rows as a single data source, and you can display accordingly.

Now if you group by the fields which make the data match between the 2 tables, presumably:
part_id length qty whse loc

Then in the group header or group footer you'll have all of the distinct combinations, plus the benefit of where it's source is, though your output doesn't demonstrate that.

You can use either a View on the database (makes more sense), or an Add Command in Crystal to build the Union Query.

-k

-k

 

[ianoctdec]

Thank you SV for your quick replay. I will try that!