Parsing an input parameter for the where clause or record select value

This question is answered

In my limited CR experience, I've always used a command database connection so that I can write my own SQL. However, now I have to parse a pipe delimited parameter to get my value for the where clause, so I'm selecting several tables and joining them through the Database Expert Links tab. All works fine, but after doing that and then parsing the parameter with the below formula in the Select Expert, I notice that there is no where clause in the SQL query, and although the report eventually displays the proper values, it runs through thousands of records first. Here is my Select Expert - Record formula:

StringVar array Parm1;

Parm1 := Split({?DATA_AREA}, "|");

{SO_ORDERS.CASE_ID} = Parm1[2]

If I change "Parm1[2]" on the last line to a valid Case ID, then there is a where clause in the SQL and the report generates immediately.

It seems like the record select formula is applied AFTER all of the records (without a where clause) are searched when I use the parsed parameter value, but when I hard code a valid value, it places that into the where clause BEFORE the sql is executed. Is there a way to get the parameter parsed first and then use that parsed value in the SQL where clause?

April 09, 2009 at 00:59 AM

0
Likes

Yes crystal will run the query first to get 100% data and then applies record selection condition. To increase the performance you need to pass the where condition at the command level instead of report level. So you need to create a report using add command like this

select * from tablename where field={?Parameter}

{?Parameter} is a command level parameter.

Now insert this report as a subreport in another report which has no connection but has a parameter

{?DATA_AREA} and create a formula like this in the main report

Split({?DATA_AREA}, "|")[2]

Now right click on the subreport and go to change subreport links and add this formula from main report and link this to sub report parameter {?Parameter} without linking any database field from the subreport.