How to Select Non matching rows between 2 tables
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2587
Printed Date: 18 May 2024 at 6:09pm
Topic: How to Select Non matching rows between 2 tables
Posted By: AK-Wy
Subject: How to Select Non matching rows between 2 tables
Date Posted: 11 Mar 2008 at 5:14pm
|
I ran out of ideas on this simple problem and I am turning to you Crystal gurus. I have two tables. I need to select the rows that are not matching between those.
Table1 Table2
Material Cost Material Price
1001 $9 1002 $12
1002 $9 1003 $12
As you see, Materials 1001 and 1003 did not match between the two tables. And that is the list I need. I decided to create two sub reports and combine them. So, I tried this for Report1 : Link material between Table1->Table2 with left outer Join.
I selected the setting, Default values for nulls. I got this as expected :
Table1-Material Table2-Material
1001 <Blank>
1002 1002
When I try to filter out the 1002 - which is common - by using the select expert {Table2.Material} = "", it did not work..
 I was expecting only this : 1001 <Blank> But I get this as a result :
1001 <Blank>
1002 <Blank>
Any ideas... Please help.
 |
Replies:
Posted By: fusion
Date Posted: 11 Mar 2008 at 9:31pm
|
You do not have any rows that have same records. You Price is different. If your price is same for 1002 then you might just have to use minus keyword in your SQL statement. select * from table1 minus select * from table2; you would use the above statement only if your table structure were table1 table2 material Cost Material Price 1001 $9 1002 $9 1002 $9 1003 $12 |
Posted By: BrianBischof
Date Posted: 12 Mar 2008 at 12:54am
I would go into the Data Link window and change the link properties to Full Outer Join. This selects all records even when the two tables don't have a matching key. For the record selection formula I would use
IsNull({Table1.Cost}) OR IsNull({Table2.Price})You can find detailed information on using databases in CR as well as a lot of tips and tricks in my book http://www.amazon.com/exec/obidos/ASIN/0974953601/bischofsystem-20 - Crystal Reports Encyclopedia . ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: AK-Wy
Date Posted: 12 Mar 2008 at 2:31pm
| Thanks for your reply. I linked only table1.Material -> Table2.Material. I did not link cost and price - they are different fields.. |
Posted By: BrianBischof
Date Posted: 12 Mar 2008 at 2:33pm
|
yes, that is correct. Link the Material fields using a Full Outer Join.
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: AK-Wy
Date Posted: 12 Mar 2008 at 2:37pm
|
IsNull({Table1.Cost}) is a wonderful solution !! It worked.
Here is some background - I am using SAP-BW query as my datasource. For some reason, the full outer join option is disabled. However, I am able to create two subreports and combined them. You guys are great !! Thanks much!
 AK
|
Posted By: BrianBischof
Date Posted: 12 Mar 2008 at 3:06pm
|
The full outer join isn't compatible with all data sources. Plus, connecting via OLE DB can limit your SQL functionality as well. When you find that some functionality seems to be missing, use the ODBC connectivity whenever possible. Glad you like the site! Did you find out about it from SDN or some place else? ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |