Left outer join, select first rec only from detail
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=2991
Printed Date: 20 May 2024 at 8:41am
Topic: Left outer join, select first rec only from detail
Posted By: zulfe
Subject: Left outer join, select first rec only from detail
Date 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. |
Replies:
Posted By: yggdrasil
Date 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
 |
Posted By: zulfe
Date 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. |
Posted By: jkwrpc
Date 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.
http://www.CustomReportWriters.net - www.CustomReportWriters.net
|