Can you incorporate Parameter into Command SQL Query?

[plarson3]

We write all our own report SQL queries in order to avoid having the DB schema being appended to the table names. We also use Crystal Parameters quite a bit. Is there any way to incorporate the Parameters into the WHERE portion of the query, in order to improve its efficiency?

Otherwise, we are pulling back all records, and then filtering after the fact within the Crstal Selection Criteria, selecting on the Paramter values.

I tried doing a:
WHERE Table.Field = {?ParameterField}

but got an ORA-24374 error.

 

[vidru]

You can't use parameters created from the 'main' report, but you can create parameters within the 'Add Command' window by clicking on the 'Create' button to the right. After you've created your parameter, you can use it within the Command, and will be prompted for its value when you refresh the report.

-dave

 

[plarson3]

Thanks very much for the quick response. Always wondered what that was for.

I tried it, and can't seem to get it to work. I can set up this Command Parameter, but the query doesn't seem affected good or bad when I add it, and I am not prompted for the value upon refreshing.

When I go back to the Command, the Parameter is no longer there to modify either. What am I missing.

 

[vidru]

Try editing the Command, and follow these steps to get the parameter into the query:

Database menu > Database Expert > right click on the Command in the 'Selected Tables' list > choose 'Edit Command' > click the 'Create' button, and define your parameter > place your cursor at the point in the SQL command that you want the parameter to be placed > double-click on the parameter you just created in the 'Parameter List' listbox.

The end result should look something like:

SELECT FieldList
FROM Table
WHERE Field = {?YourParameter}

-dave

 

[plarson3]

That works. Thank you again. Could be a life saver (assuming it is working the way I had hoped).

Is it your understanding that putting the parameter fields in the query itself allow for the filtering at that point instead of after the fact, improving the efficiency of the report?

 

[vidru]

Absolutely.

-dave

 

[plarson3]

The other side of that question is:

If I use the standard parameter fields within Crystal, outside of the query itself, does it bring back all rows first, and then filter by the parameter after the fact?

 

[vidru]

It depends on the construct of your Record Selection Formula. See faq767-3825 for methods of optimizing record selection.

By doing a 'Show SQL Query', you can usually see if your record selection is being passed as the WHERE clause to the server.

-dave

 

[kenhamady]

plarson,

If you are NOT using a command then the select expert will in many cases be built into the WHERE clause. However, if you are using a command, nothing you do in the select expert has any effect on the SQL query. The select expert criteria is applied on the client side.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com