Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: sql to Crystal reports Post Reply Post New Topic
Author Message
colman1212
Newbie
Newbie


Joined: 08 Jul 2010
Location: Ireland
Online Status: Offline
Posts: 36
Quote colman1212 Replybullet Topic: sql to Crystal reports
     Posted: 21 Jul 2010 at 4:31am

Ok so I want to pull back all the information from one table that doesn't have a matching row on the other table.

So for example in sql I would write it like.
 
select * from inventory_table
where inventory_id not in
(
select inventory_id
 
from customer_inventory
)
;
 
I just have no idea how to put this in crystal terms, Like should I click something in link options?
 
Any help would be much appreciated!
Thanks,
Colman
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Jul 2010 at 4:47am
do an outer join between the 2 tables then in your select expert
isnull(customer_inventory.inventoryid)
IP IP Logged
colman1212
Newbie
Newbie


Joined: 08 Jul 2010
Location: Ireland
Online Status: Offline
Posts: 36
Quote colman1212 Replybullet Posted: 21 Jul 2010 at 4:56am
Originally posted by DBlank

do an outer join between the 2 tables then in your select expert
isnull(customer_inventory.inventoryid)
 
HHmmmm...tried that and my report brings back nothing.
Are you sure thats correct?!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Jul 2010 at 5:00am
make sure your doing a outer join from inventory table to customer inventory
Also make it enforced from both just to make sure
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Jul 2010 at 5:06am
also make sure you only use fields from inventory in the report.
If you were dropping the customer_inventory fields onto the convas they will all be 'blank'
IP IP Logged
colman1212
Newbie
Newbie


Joined: 08 Jul 2010
Location: Ireland
Online Status: Offline
Posts: 36
Quote colman1212 Replybullet Posted: 21 Jul 2010 at 10:54pm
Still cannot get this to work, I've tried doing left outer join and full outer join between the tables and still no luck. Page is still showing up blank...
The only thing in my forumula section is isnull(customer_inventory.inventoryid)
IP IP Logged
colman1212
Newbie
Newbie


Joined: 08 Jul 2010
Location: Ireland
Online Status: Offline
Posts: 36
Quote colman1212 Replybullet Posted: 22 Jul 2010 at 3:02am
Actually I got this sorted. thanks a million.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 Jul 2010 at 3:18am

If you had your report setting set to use default values for null youi would have had to change that back to using Nulls for null.

Can you post the solution you figured out for others to learn from?
Thanks.
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.