using upper() in @prompt function

[SRIBI]

Hi,

I am new to BO. Trying to use upper() function in @prompt(). It is working fine if i give =,mono,free combination Whereas if i give IN,Multi,free combination it is throwing "Missing right parenthesis" error. Please guide me.

Ex: Working code:
<Database field> = upper(@prompt('prompt description','A','<classname\object',Mono,Free))

Error giving code:
<Database field> IN upper(@prompt('prompt description','A','<classname\object',Multi,Free))

 

[hilfy]

I don't think you'll be able to do it this way because of the way Upper works in SQL.

In a regular SQL statement with IN you would do it this way:

<database field> in (upper('test1'), upper('test2')...)

instead of

<database field> in upper(('test1', 'test2'...))

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[SRIBI]

Hi,

Thank you. Can you please explain me in detail.

Actually my requirement is: We have created condition in universe which will be used in crystal report as input parameters. Users are allowed to select the mulitple values from the list as well as they can key in the input. In this scenario, they might give the input in lower case. So I am trying to handle that using upper() in the universe condition. I want to use upper() in @prompt function. but it is not allowing me to use. It says "missing parenthesis" error.

 

[hilfy]

You CAN'T use it in the prompt unless your users are selecting single value. You cannot it with "In List" because of the way SQL works.

My example above shows the detail of why - the second example is what you get when you try to use Upper with an In List prompt which cannot be parsed in the database.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[SRIBI]

Thanks a lot.