RE: SQL Case Sensitive selection

The set options don't work like executable statements.

No matter where they appear in the source, the effect the entire module.

Using the SRTSEQ option is the best way to handle case insensitive
searching, since you can create a case insensitive index that can be
used.  Whereas when you use the UPPER or LOWER functions no indexes may
be used.

I suppose that if you want to be able to switch back and forth between
insensitive / sensitive you'd need to duplicate the select in two
modules.

Not the best answer.

I'd open a call with IBM and see what they say.

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Billy Rowe
> Sent: Sunday, April 02, 2006 11:31 PM
> To: RPG programming on the AS400 / iSeries
> Subject: Re: SQL Case Sensitive selection
> 
>    If you want case-sensitive just use what the user keys to search,
>    otherwise use
>    UPPER(database_table_column_name)    =    UPPER(whatever 
> user keyed)   
>    --OR--
>    LOWER(database_table_column_name)   =    LOWER(whatever user keyed)
> 
>    hth
> 
>    Marco Velazquez wrote:
> 
>  I'm trying to provide users with the ability to toogle 
> between making their
>  search case-sensitive or not by using the following:
> 
>    * Case-sensitive selection
>   c                   if        pCase = Yes
>   C*/EXEC SQL
>   C*+   SET OPTION SRTSEQ = *LANGIDUNQ
>   C*/END-EXEC
>   c                   else
>   C*/EXEC SQL
>   C*+   SET OPTION SRTSEQ = *LANGIDSHR
>   C*/END-EXEC
>   c                   endif
> 
>  However this is not having an effect on the result set...   
> Does anybody has
>  any ideas?
> 
>  Thanks,
> 
>  Marco.
>   
> -- 
> This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
>