Find The Mismatches

[mgrejtak]

I'm using Crystal 8.5 against an Oracle database. I'm trying to improve the integrity of two like tables in our system - but in order to do so need to look for occurrences between two tables where there is not a match on part number.

In other words Table A has
Part A
Part B
Part C
Part D

In Table B we have
Part A
Part B
Part X
Part D


We need to know that Part C does not have a match in Table B and in addition we need to know that Part X does not have a couterpart in Table A. More than likely table A is correct - but Table B is supposed to stay synchronized with Table A - but for some reason does not.

How do we achieve this feat in Crystal?

Any insights would be appreciated!!!

 

[synapsevampire]

Ask you dba to do a simple unmatched query, Crystal is definitely NOT the best tool for such things.

If they use a Minus Query, this should be simple.

You could write it in CR 8.5 as well, it might look something like:

Select 'table 1' tablename, col_1, col_2
From table_1
Minus
Select 'table 2' tablename, col_1, col_2
From table_2
UNION ALL
Select 'table 2' tablename, col_1, col_2
From table_2
Minus
Select 'table 1' tablename, col_1, col_2
From table_1

There are other ways to accomplish this in Crystal as well, but again, this is a relatively simple process within SQL.

-k

 

[lbass]

I had this all written and then got interrupted. This is similar to synapsevampire's suggestion:

One approach would be to use a union all. In 8.5 you would first add Table A to the report and place the part field on the report canvas. Choose another field from Table A, let's say a string field and we'll call it {table.string}, and add that to the report also (this field will help to distinguish tables).

Next go to database->Show SQL query and copy the query. Then add "Union All" and paste the query below it. Then change the table.name in the second part of the query (and the field name if it is different in the second table), and change {table.string} to a constant 'B'. It will look something like this:

Select
tableA.`partno`, tableA.`string`
From
`tableA` tableA
Union All
Select
tableB.`partno`, 'B'
From
`tableB` tableB

Then create a formula {@Table} and place it in your detail section:

if {tableA.string} = 'B' then "Table B" else "Table A"

Insert a group on {tableA.partno} and suppress the group header and footer. Then go to edit selection formula->GROUP and enter:

distinctcount({tableA.string},{tableA.partno}) = 1

This should return those parts that appear in one table and not the other, along with the field that shows which table the part does appear in.

-LB

 

[synapsevampire]

LB: Give it a go using my example, you should see a significant performance increase as the database will do it all.

-k

 

[lbass]

SV,

I tried this in the Xtreme database (CR 8.0):

SELECT
Customer.`Customer ID`
FROM
`Customer` Customer
MINUS
SELECT
Top_Customers.`Customer ID`
FROM
`Top_Customers` Top_Customers

This doesn't seem to work. Is the use of "minus" specific to particular datasources?

-LB

 

[synapsevampire]

Yes, as with many functions, there are specific functions for specific databases.

I test in Oracle or SQL Server, either of which allow you to D/L trial versions that last at least 6 motnhs these days I think.

-k

 

[lbass]

Thanks. I'll try it at work next week with Oracle.

-LB

 

[dfbonilla]

-Create a main report with table A
-Create a subreport with table B linked to main report
-Create a global variable in Main report
-When printing records in the subreport flag the variable in the main report from the subreport if record was found
-If variable flagged in main report there is a match else mismatch.
-Suppress print matches in main report and now you have mismatches

Regards, Diego.

 

[synapsevampire]

Diego: It doesn't work for those in table B not in table A, and it would be much slower.

Explore the alternatives offered.

-k

 

[mgrejtak]

Does the functionality of MINUS only use the 1st table as the reference point? what if there are rows in the 2nd table that are not in the 1st table? Does MINUS not care about those rows?

I'm hoping to give this a try this AM to see if it gets me close to where I need to be.