Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Left outer join, select first rec only from detail Post Reply Post New Topic
Author Message
zulfe
Newbie
Newbie


Joined: 04 Apr 2008
Location: India
Online Status: Offline
Posts: 5
Quote zulfe Replybullet Topic: Left outer join, select first rec only from detail
    Posted: 21 Apr 2008 at 11:40pm

Hi,

 

I have two files A (master) & B (detail). And there is one common field vendor code in both file which are selected as left outer join. From detail file B I want to select always first record only. If record is available more then one then the amount is multiplying by no of records available in detail B.

Then I put condition select record sq# = 1 then its works fine till now every thing is OK.

Incase record is not available in B then report is blank.

Appreciate your valuable comments/solution.

I want to show the total amount from A and Vendor details from B.

 

 Thanks in advance.
IP IP Logged
yggdrasil
Senior Member
Senior Member
Avatar

Joined: 19 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 150
Quote yggdrasil Replybullet Posted: 22 Apr 2008 at 7:18am
Left outer joins stop working as you'd expect as soon as you put a condition on the second table, which you have done with the selection of the first record.
One solution is to bring back all the records as you were originally, then group on vendor code. If these are sorted so the record you want is last, you can transfer it to the group footer and suppress the detail rows you don't want.
If this isn't possible, there are other much more complicated ways of doing it, with subreports or SQL commands, which I have been forced to use recently in my reports Smile
IP IP Logged
zulfe
Newbie
Newbie


Joined: 04 Apr 2008
Location: India
Online Status: Offline
Posts: 5
Quote zulfe Replybullet Posted: 03 May 2008 at 10:52pm

Thanks a lot for your message, I regret to inform you that it’s not working, if you had done some other way please let me know. Thanks in advance.
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 04 May 2008 at 6:17pm
You might try creating 2 command objects to hold yourSQL statements in the database expert. In the A side Command Object - Select all the records and the specific data fields you want. Be sure to include the vendorid field.
 
In the second object use the Select Distinct logic and be sure to include the vendorid field.  This logic will eliminate any duplicate entries.
 
Then use the database expert to link the two command objects on the vendorid field. Link the A Command Object to the B Command Object. Set the link options to Left Outer join (A->B).
 
You can then use grouping in the report if needed to sort out the data presentation.
 
Hope this helps.
 
Regards,
 
John W.
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.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.