Hi all,

I am facing a big performance issue with one of my report. this is because of one of the filter applied in the report which doesn't get translated to SQL and the query takes a very long time. The where statement is
WHERE UPCASE(100, CONSOLIDATED_GROUP, 'A100') CONTAINS 'ING'

Ideally, we were hoping this to be translated into
where upper(consolidated_group) like '%ING%' but it's not happening.

Can somebody please suggest me any other way of writing the same so that my query get translated into SQL perfectly.

PS: In my procedure, I do not flexibility to write SQL directly as it contains hundreds of dialog manager commands.

Regards,
Chirag

This message has been edited. Last edited by: Kerry,

First things first!
Is yours an Oracle database? If so, do you have a function-based index on UPPER(CONSOLIDATED_GROUP)? If not, I wouldn't see any big performance improvements.

To answer your question, I don't think UPCASE gets converted to UPPER when using a specific iWay Database Adapter. You may want to look for some documentation on the iWay Adapter for your particular database to see exactly what optimization techniques can be used (take a look at IBI's technical support web site). I can see 2 or 3 ways to achieve what want:

1. Create a database view on top of the table you want to query, including a field with the UPPER conversion there. Then create a WebFOCUS synonym on that view and use that one when producing your report.

2. You can also use SQL passthru to run actual SQL against your database structures. You can then use UPPER and any other specific functions your database supports.

3. If your database is Oracle 11g, you can also research about "virtual columns".

You may also want to update you signature to specify which database(s) you're targeting in your reporting environment.

Would this solve the problem? Or does CONSOLIDATED_GROUP contain mixed case values?

WHERE (CONSOLIDATED_GROUP CONTAINS 'ING')
   OR (CONSOLIDATED_GROUP CONTAINS 'ing');

Yes Dan, It may have mixed case values as well. I am researching on njsden solution.

Thanks,
Chirag

njsden solution worked for me. thanks everyone for their valuable suggestion.

If you are using Oracle 10g release 2 or later, there is another option. You can make your queries case-insensitive by using two Oracle session settings (both are necessary) available beginning with 10g R2. These settings will allow a simple CONTAINS/EQ comparison to be translated and passed to Oracle, thereby avoiding the performance degradation associated with use of WebFOCUS case-modifying functions.

SQL SQLORA
ALTER SESSION SET NLS_COMP=LINGUISTIC ;
END
-*
SQL SQLORA
ALTER SESSION SET NLS_SORT=BINARY_CI ;
END
-*
TABLEF FILE <filename>
PRINT/SUM <columns>
WHERE CONSOLIDATED_GROUP CONTAINS 'ING';
END