11/25/2024 12:39:48 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss reports for SalesLogix including Crystal, SQL Reports, etc. View the code of conduct for posting guidelines.
|
|
|
|
Multiple Table Select Problem
Posted: 10 Jan 08 10:25 AM
|
For the sake of simplicity I'm going to keep my table names simple:
Table A -> left join to Table R (on AID)
Table A -> left join to Table T (on AID)
Tables R & T both have data that will need to be displayed in the report, however it's possible that there might not be data in one or the other.
I want the report to display when the following are true (A will always be true)
1) R = True, T = True
2) R = True, T = False
3) R = False, T = True
In the record select I an OR for my selection of both criteria :
({T} = "X")
OR
({R} = "Y")
In this example, T is first. Using the numbers above, #1 will display and #3, but #2 is ignored. I get NO R records if there is no T record.
If I reverse the order of the select:
({R} = "Y")
OR
({T} = "X")
Now R is on top and I'll only get #1 and #2, but no #3.
If I copy the SQL statement out of Crystal and run it against the database directly I can see ALL records that I expect to, so I don't believe this is a join issue.
I'm still running with 8.5 BTW. Thoughts?
|
|
|
|
Re: Multiple Table Select Problem
Posted: 10 Jan 08 11:58 AM
|
One solution might be to remove the selection criteria from the SQL query and conditionally suppress the rows directly in Crystal Reports.
It might have worse performance, depending on how many records you expect the condition to eliminate, but it usually works.
Or try inverting the selection logic:
NOT(
({R} IS NULL )
AND
({T} IS NULL )
) |
|
|
|
Re: Multiple Table Select Problem
Posted: 10 Jan 08 12:31 PM
|
WT_!?
I inverted the logic and it works now. 
I'm glad my report works but that makes absolutely no sense to me. Is this just some kinda weird quirk that Crystal has? |
|
|
|
Re: Multiple Table Select Problem
Posted: 10 Jan 08 12:54 PM
|
| My guess is that--since your SQL worked directly against the database--there is a bug in the interaction between LEFT OUTER JOIN and OR. Using NOT AND is logically identical, but might have avoided the buggy code. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|