Re: Query/400 Upper-Lower Case

Hi, all:

This is a problem that seems to come up all the time with various databases
and query tools, etc., and not just on the iSeries or DB2, either!

Here is a simple "solution" that works on ANY platform, with ANY database,
with ANY query tool:

For those fields that contain "mixed case" and that you need to use as a
"key" or to be able to query against, create another field in the database
table that contains the same data, but is always translated to ALL
UPPERCASE. Then, when you need to do any of these kinds of queries, you
would use that field for the selection criteria, but use the original field
(with mixed case) for printing/display purposes.

This technique does require a little more storage space per row, but it
really simplifies query logic, and avoids some overhead of translating the
field to all uppercase dynamically during the query, etc., and also, if it
happens to be a "key" field (with an SQL index or logical file view over
it), this also ensures a more consistent sorting/collating sequence.

And, with "native" DB2 triggers, you can even take care of "maintaining"
this "extra" field automagically, "under the covers"... an *INSERT and
*UPDATE trigger can read the data from the original field(s), translate to
UPPERCASE, and populate the "new" fields. This means you do not have to
change any of the existing programs, except of course to recompile to avoid
level-checks, and you could even get away with LVLCHK(*NO) if you are
"careful" and always add all these new fields at the end of the record.

I like to think of this approach as one application of the KISS principle
;-)

Hope that helps?

Mark S. Waterbury

----- Original Message -----
> From: <rob@xxxxxxxxx>
> To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
> Sent: Friday, June 11, 2004 9:57 AM
> Subject: RE: Query/400 Upper-Lower Case
>

> Anyone know if a QAQQINI option will do this for him?
>
> Or, instead of using RUNQRY *N, have you thought about using RUNSQL or
> it's cohorts to do something like, (from the command line):
> RUNSQL REQUEST('select * from qtemp/myfile where upper(myfield)=''TEST
> THIS''')
> ....+....1...
>   MYFIELD
>   ----------
>   Test this
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "Shannon ODonnell" <sodonnell@xxxxxxxxxxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 06/11/2004 10:25 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
> To
> "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
> cc
>
> Fax to
>
> Subject
> RE: Query/400 Upper-Lower Case
>
>
>
>
>
>
> As I said, I was just curious about this.  If there was even a way to do
> it
> with that command.  I use RUNQRY *N all the time from a command line to
> peek
> into a database, so that I don't have to fire up Query/400, or write a
> program.  It's not a big deal either way.  I just wondered if anyone knew
> if
> it could be done or not.
>
>
> Shannon O'Donnell
>
>
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
> Sent: Friday, June 11, 2004 10:06 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: Query/400 Upper-Lower Case
>
> Most people using the RUNQRY command bury it in a program.  Simply convert
> the search text to upper case in the program prior to passing it to the
> RUNQRY command.  If, however, the file data may be mixed case it gets a
> bit
> trickier.
>
> How's about converting it to a query management query and using the UPPER
> function?  No 57xx-ST1 is required for your system just to do this.
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "Shannon ODonnell" <sodonnell@xxxxxxxxxxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 06/11/2004 09:54 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
> To
> "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
> cc
>
> Fax to
>
> Subject
> RE: Query/400 Upper-Lower Case
>
>
>
>
>
>
> I should have been more specific.  My fault.  What I meant was, is there a
> way to do the upper/lower case thing using ONLY the RUNQRY *N MYLIB/MYFILE
> RCDSLT(*YES) command?
>
> Thanks for the tips on how to do this with the full Query/400 tool though.
>
> I
> did not know that either and it will come in very handy sometime, I'm
> sure!
>
>
> Shannon O'Donnell
>
>
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Lance Gillespie
> Sent: Friday, June 11, 2004 9:34 AM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Re: Query/400 Upper-Lower Case
>
> > Using Query/400  (i.e., Runqry *n MyLib/MyFile
> > RCDSLT(*YES))  on the query selection...is it possible to search for
> > both upper and lower case letters  in a single pass?
>
> Yes. Use the collation sequence option in the query definition
> and change it to  4 (Translation table).  Then use
> QSYSTRNTBL in library  QSYS.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> unsubscribe,
> or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
> moment to review the archives at http://archive.midrange.com/midrange-l.
>
>
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>