Print Page | Close Window

Conditional Left join

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=7528
Printed Date: 06 May 2024 at 4:06pm


Topic: Conditional Left join
Posted By: cfok
Subject: Conditional Left join
Date Posted: 28 Aug 2009 at 12:50pm
Hi there,

Does anyone know if it's possible to do a conditional left join in Crystal?
Example,
select * from A left outer join B on (A.id = B.id and B.colA = '5')

If it's not supported, does anyone know of any tricks to accomplish the same thing?

Thanks!


Replies:
Posted By: DBlank
Date Posted: 28 Aug 2009 at 1:10pm
You should be able to do that as a Command. Although not all datasource types support commands.
If you are coming from a SQL source you can create a view or stored proc in teh DB and use that as the datasource as you would a table.

Posted By: cfok
Date Posted: 28 Aug 2009 at 1:21pm
Thanks for the reply! The only problem is the a Command does a full table lookup and does not filter out my records specified in the Record Selection (it does it in memory instead of at a database level).  We had a lot of performance issues with the use of commands.

The other problem with a view is that the conditional filter is dynamic, meaning that it's not going to be B.colA = '5', but more like B.colA = :PARAM

Posted By: DBlank
Date Posted: 28 Aug 2009 at 1:28pm

Can you go with a stored proc?

You can create the param in the SP, use the SP as the source, crsytal will create a interface for teh param to be passed back to the source and run theSP at the time of the report generation which should greatly improve performance and address the param issue. 

Posted By: cfok
Date Posted: 28 Aug 2009 at 2:45pm
Unfortunately, our application has over 300 tables which now needs this left conditional join so stored proc is most likely not an optimal solution for us.

Posted By: DBlank
Date Posted: 28 Aug 2009 at 3:09pm
I am out of ideas on this.
Perhaps Hilfy or Lockwelle will read this an chime in...


Print Page | Close Window