Does Crystal pushes the where clause to a union query? 2

[northw]

Hello all,
I have a qustion regarding the crystal using union query.
Could anyone tell me, If crystal reports pushes where clause in a union query?

Thanks in advance!

 

[Turkbear]

Hi,
You can see what SQL is being sent to the database..Under the Database tab( I think) in the report designer you can select 'Show Sql'...



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi again,
In addition, if you are creating the query as a Command then the entire command is passed.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[northw]

Hi Turkbear,
Thanks for the reply!
I have looked at the SQL generated by Crystal, Where it is pulling all the data from database and applying filtering the data in the report. I have basically four union queries, even to pull a few records, it is reading entire query from the database, I have tried using the union's as a subquery to see if if the crystal passes where clause, but it's still grabing all data and filtering it on the report.
Any thought's on how to push where to database?

Thanks again!

 

[Turkbear]

Hi,
Try the ideas in this great FAQ:

faq767-3825




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

If you are creating the union query in a command, then you must also build in the selection criteria within each component of the union query if you want all work done on the server. If you instead try to add selection criteria in the Crystal GUI (report->selection formula->record), the criteria will be applied locally, after all records are returned by the command.

-LB

 

[northw]

Sorry for the late reply LBass.
By making changes what you have suggested, I cannot pass multiple values to the sql command and cannot use "ALL" to select all the data.
Any other suggestion/workaround?

Thanks again!

 

[lbass]

You could use the method in faq766-6779.

-LB

 

[northw]

Thanks for the link LBass, But I already have a subreport, which I cannot integrate in the main report. So I can't use the approach mentioned in the FAQ.

Thanks again!

 

[Turkbear]

Hi,
Look into the syntax of your database for using a CASE statement in the Where clauses...You can then pass the multiple parameters to that construction like:

where
CASE {?param} = "ALL": 1 = 1
{?param} <> "ALL" : {Table.Field} In {?param}


This is a very rough example of the idea that, in many database languages, a parameter value or values can be tested for and appropriate actions taken - that way a command can still be used for the UNION




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

You can use more than one sub in a report, you just can't nest them, so I'm not sure why you think having a sub renders the faq unusable.

-LB

 

[northw]

It will be a nested subreport LB, If I follow the method mentioned in the FAQ. So I cannot use that method.

Thank you!

 

[lbass]

Please explain how your existing sub relates to the union command you mentioned earlier. Is the command IN the sub as its sole datasource? Or is the command used for the main report, with the sub linked to it?

-LB