Using parameters in SQL query 1

[ANCDEV]

I would like to use parameters directly in the sql query without having to use a formula in the select expert.

example: select a from tableb where tableb.a = ?parm

The end users of the report would need to be prompted to enter the parameter values when the report runs.

How do I do this? It seems that I am forced to use the select expert every time and that it generates a formula.

Thanks...

 

[tseaman]

Make your SQL into a stored procedure that accepts a parameter, then base your report on it. Crystal automatically creates a parameter field matching the one in the stored procedure and will prompt the user. Crystal passes the user's response to the stored procedure without needing a selection formula.

Given your example, I see no benefit in using the parameter directly in the SQL over the Crystal selection formula. All the formula does is allow Crystal to build the SQL select statement with the explicit value entered by the user. After entering a value, if you look under "Database" -> "Show SQL Query", you will see that value in the SQL statement. The net effect is the same, and in either case the SQL is pushed down to the database so performance should be the same.

 

[yvm066]

I have a further question with regards to this issue. I am new to CR and I have to modify an existing report here that is basically an invoice. The current .rpt uses the "Edit Selection Formula: Record" in order to filter the information that appears in the invoice.

However, this has resulted in that entries are missed. The SQL Select statement had to be modified so that the parameters are used in an embedded SELECT statement in the WHERE clause of the .qry file. I couldn't figure out a way to do this in the .rpt so it ended up in the query. The problem is that the user is no longer prompted to change parameters and this is a required feature. Should I replace the .qry with a stored procedure as you suggested above? If so, how would I do this?

Thank you!

 

[tseaman]

CR cannot create embedded select statements from the selection formula builder. In order to get this to work with a parameter, I think you will have to go with a stored procedure. What database are you using? I can give you some pointers if you are using Microsoft SQL Server or Oracle.

One drawback to converting a report from using tables to using a stored procedure is that the fields do not map over to the stored procedure when you do a "Database" -> "Set Location" then "Database" -> "Verify Database" to get it to recognize the stored procedure. You pretty much have to rebuild the report. You can see both sets of fields at the same time, so I go through the process of putting the new field on an empty area of the report, formatting and sizing it like the original field, write down the original field's position, then delete the original field and move the new to its place. This is tedious but works. Of course, any formulas using the original fields also must be changed. Then you can do "Database" -> "Remove from Report" to get rid of the original tables.

The best thing is to develop the stored procedure before you start the report. Of course, as in your case, you can't always do that.

 

[yvm066]

Thanks for the prompt reply and excellent tips - I will give your suggestions a try later today.

We are using Oracle 8.1 - any pointers you might be able to supply on using Oracle stored procs with CR would be appreciated.

 

[tseaman]

The Crystal Decisions support site has a technical brief named "scr_oracle_stored_procedures.pdf" that you can donwload. It contains very important information about which versions of the database drivers work with which version of CR and how to set up the DSN. It also gives you some sample code. Read carefully to avoid a lot of unnecessary frustration. ("Been there, done that.&quot

It tells you you need to use a REF cursor declared in a separate package. The stored procedure must be stand-alone and not defined inside a package.

You can create a simple generic REF cursor usable by any stored procedure you write like this:

CREATE OR REPLACE PACKAGE CR_GENERIC_CURSOR AS
TYPE GenericCurType IS REF CURSOR;
END CR_GENERIC_CURSOR;
/

It would be referenced as an IN OUT parameter in your stored procedure something like this (along with any other needed parameters):

CREATE OR REPLACE PROCEDURE Test_Procedure(
P_Cursor IN OUT CR_GENERIC_CURSOR.GenericCurType,
P_Parm IN VARCHAR2)
AS
BEGIN
OPEN P_Cursor FOR
SELECT ...

Hope this helps.