Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: How to Select Non matching rows between 2 tables Post Reply Post New Topic
Author Message
AK-Wy
Newbie
Newbie


Joined: 11 Mar 2008
Location: United States
Online Status: Offline
Posts: 3
Quote AK-Wy Replybullet Topic: How to Select Non matching rows between 2 tables
    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.. Ouch 
 
 
I was expecting only this : 1001                     <Blank> 
 
But I get this as a result :
1001                     <Blank>
1002                     <Blank>
 
Any ideas... Please help.
 
Dead
IP IP Logged
fusion
Groupie
Groupie


Joined: 12 Nov 2007
Location: United States
Online Status: Offline
Posts: 93
Quote fusion Replybullet 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
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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 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>
IP IP Logged
AK-Wy
Newbie
Newbie


Joined: 11 Mar 2008
Location: United States
Online Status: Offline
Posts: 3
Quote AK-Wy Replybullet 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..
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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>
IP IP Logged
AK-Wy
Newbie
Newbie


Joined: 11 Mar 2008
Location: United States
Online Status: Offline
Posts: 3
Quote AK-Wy Replybullet 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! Clap
 
AK
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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>
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.