I have a report in SAP Crystal Report that uses an Oracle SQL with parameters. One of the parameters a user could possibly enter lower case which would not return any values. I would like to force the parameter entered to be upper case. EXAMPLE:
User enters p_Parameter something like: userid1, UserID1, UserId1, Userid1 But in the database the FieldName is USERID1 I can prompt the user to enter as UPPER CASE; but they could still miss it.
I have tried:
Works as is requiring user to enter p_Parameter in UPPER CASE:
WHERE FieldName IN {?p_Parameter}
Returns errors: missing right parenthesis.
WHERE FieldName IN UPPER({?p_Parameter})
WHERE FieldName IN (UCASE({p_Parameter})
I got some more feedback on this elsewhere that I don't believe this is possible, because FieldName is a list in this case so it can't be wrapped in upper().. when it comes into the sql it looks like ('name1', 'name2', 'name3')
I'm still leaving this as un-answered in case some Crystal Reports expert out there knows of a possibility of making this work.
I'd recommend to create a new formula field, which returns an array based on the values of your {?p_Parameter} array, and change all contained elements to upperstring. Then you can use the new formula field in your record selection instead of the parameter field.