SQL and Multiple Values Parameters

[Former Member]

Hi,

I've pretty much exhausted my search (google and in this forum) with finding a solution to my problem... So now I'm hoping that any of you will be able to help me out.

I am writing an Oracle SQL query to get the data for a Crystal Report (Product Ver. 11.5.8.826).

What I am trying to do is to allow a parameter to be entered by the user based on three possibilities:

1. user enters a single value (eg. '66200')

2. user enters multiple values (eg. '66200, 66201')

or

3. user leaves the parameter alone (eg. '' )

The query works up to when I try do the following...

Select

.........

From

........

Where

.......

and ({?param_id} IS NULL OR table1.ID IN ({?param_id}))

{?param_id} settings:

'List of Values' = Static

'Allow multiple values' = False

'Value Type' = String

with no Default Value

table1.ID is of type Integer

Using the above query, it works when I enter a single value such as '66200', but I get the following error when I leave the param_id blank:

"Failed to retrieve data from the database.

Details: HY000:(Oracle)(ODBC)(Ora)ORA-00936: missing expression

(Database Vendor Code: 936 )"

When I try to enter the value '66200, 66201' (yes, I know I have Allow multiple values set to False, and I do want the user to be able to enter more than one ID by using comma as a separator), I get the following error:

"Failed to retrieve data from the database.

Details: HY000:(Oracle)(ODBC)(Ora) ORA-00920: invalid relational operator

(Database Vendor Code: 920 )"

But when I have the following query:

Select

.........

From

........

Where

.......

and (table1.ID IN ({?param_id}))

and enter the value '66200' it works, or if I enter the value '66200, 66201' this works too ('66200, 66201' will return the correct data result).

However, if I leave the param_id to be blank, I get the error:

"Failed to retrieve data from the database.

Details: HY000:(Oracle)(ODBC)(Ora)ORA-00936: missing expression

(Database Vendor Code: 936 )"

What I want to try and do is to allow the user to leave the param_id empty and have the SQL query return all ID values by checking if that param_id is empty.

I understand that I can just add a Value to the param_id to have "ALL" and just let the user select it, but the problem with that is then I will need to have the SQL Query potentially return thousands of data before I can even do the Record Selection - obviously not good on report performance and all. I do have four (4) other parameters that I am using for the report, with two of them being required Date parameters:

{?param_startdate}

{?param_enddate}

{?param_title}

{?param_refnum}

These four parameters are also in the SQL query, and returns with no problem at all:

WHERE

....

and ( {?param_startdate} IS NULL OR {?param_startdate}='' OR table.startdate >= {?param_startdate} )

and ( {?param_enddate} IS NULL OR {?param_enddate}='' OR table.enddate <= {?param_enddate} +1)

and ( '{?param_refnum}' IS NULL OR table.refnum= TO_NUMBER('{?param_refnum}') )

and ( '{?param_title}' IS NULL OR UPPER(table.title) = UPPER('{?param_title}) )

......

So, my question is:

Is there a way to have the param_id be left blank thus allowing the SQL query to return all the ids?

Any help would be much appreciated... thanks in advance.

Edited by: Suolon Hu on Oct 16, 2008 6:13 PM

Edited by: Suolon Hu on Oct 16, 2008 7:28 PM

[Former Member · Accepted solution]

Hi Soulon,

You can check for blank condition in the Record selection formula and let the remaining parameters be part of the SQL query.

It will be easy to handle the blank condition in the record selection formula.

Specify the following code :

if length(trim({?param_id})) > 0 then

totext(table1.ID,0) in ({?param_id})

else

true;

Hope this helps.

[Former Member · Accepted solution]

HI Mohamed,

Thank you for your suggestion.

So you mean in the SQL query, I should return all the IDs first, and then use the Record Selection that you have suggested?

The problem with that is when the report runs, I would have to enter a value first. So with the Record selection, it will be based on what the first value I've entered and what the queries return.

[Former Member · Accepted solution]

Hi,

You can remove the param_id parameter from the SQL query. The param_id should appear only in the record selection. Remaining parameters will be part of the SQL query.

The record selection formula will check whether a value has been entered for the param_id, if yes it will fetch those values along with the SQL query parameter conditions.

If no, it will fetch all the ids along with the SQL query parameter conditions.

Hope this answers your question.

[Former Member · Accepted solution]

Hi Mohamed,

Thanks again! This does the job, but I find that it runs much slower than if I were to have it in the SQL query instead. I guess there isn't a way to have a check condition in the SQL query instead of having it in the Record Selection?

I was trying the CASE WHEN in the WHERE clause of the SQL query, but not being a SQL expert, I wasn't successful with this method - I was able to get it to work with one entry, but not multiple values '66200, 66201' or blank (all values))

WHERE

....

and table.ID IN

(CASE

WHEN {?param_ID} IS NULL THEN table.ID

ELSE {?param_ID}

END)