Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

SQL and Multiple Values Parameters

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

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question