Joined: 28 Aug 2009
Online Status: Offline
Posts: 3
Topic: Conditional Left join 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?
Joined: 28 Aug 2009
Online Status: Offline
Posts: 3
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
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
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.
Joined: 28 Aug 2009
Online Status: Offline
Posts: 3
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.
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