Pushing selection to server for array of values

[tekniks]

My report CR 8.5 with Oracle 7.3.4 has one mandatory (date) and 3 other optional parameters.

I am able to push all other selections to the server except this:

Parameter -- Country

(
If {?Country} <> '' then
{CL_REQUEST_ST.REQUEST_TYPE}="PSR"
and
{MM_LOT_ST.RETEST_DATE} <= ({?Recert date})
and
{CL_REQUEST_ST.COUNTRY_CODE} like UpperCase({?Country}) +'*' -- problem line
else if {?Country} = '' then
True
)

The field COUNTRY_CODE in the Database has values like this - CA, US, MX

And the user can key in any value for the parameter - Country

For now the report only picks up the first value i.e. CA and doesn't return any records for other values.

I tried doing
UpperCase({?Country}) +'*' in [{CL_REQUEST_ST.COUNTRY_CODE}]
but it doesn't work either.

Any suggestions ...?

Thanks

Tek

 

[lyanch]

You need to add a * to both sides and use like:

{CL_REQUEST_ST.COUNTRY_CODE} like '*' + UpperCase({?Country}) +'*'

Check database-> show SQL to make sure it is sending it correctly to the database.

Lisa

 

[GJParker]

Is UpperCase() a valid Oracle function ?

It's been a while since I used Oracle so not sure. You could try UCASE()

HTH

Gary Parker
MIS Data Analyst
Manchester, England