Re: Query/400 Upper-Lower Case

["Mark S. Waterbury" <mark.s.waterbury@xxxxxxx>]

Cool!  Same idea (more or less); different (better?) implementaiton.

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

> Mark,  instead of that boat why not just use a different logical file? For
> example the following logical file:
> CREATE VIEW QTEMP/myfileview
> (MYFIELD, MYFIELD_UC)
> AS SELECT myfield, upper(myfield)
> FROM qtemp/myfile
>
> Then I can do
> RUNQRY QRYFILE((QTEMP/MYFILEVIEW)) RCDSLT(*YES)
> Field             Test   Value (Field, Number, 'Characters', or ...)
> MYFIELD_UC        EQ     'TEST THIS'
>
>
> Another example:  Suppose you're a BPCS shop.  On hand is not stored in
> the file.  You could do the following:
> CREATE VIEW QTEMP/mybpcs
> (IPROD, IDESC, ONHAND)
> AS SELECT IPROD, IDESC, IOPB+IADJ+IRCT-IISS
> FROM dhtdivf/iim
>
> No triggers or maintenance needed.
> Much easier queries for your users.
> These views can even combine files.  Making even easier queries.
> What makes a bulk of the GUI query tools easier for your users is having
> YOU set up these views.  Now, you can either do it this way.  Or you can
> set them up in the GUI tool and pretty much limit the usefulness from
> within the tool.  Just like you can have some complex Queries that boggle
> your users mind.  Or you can set up some decent views and just have your
> users query those views.
> And if you don't like the field names (IPROD) you can use different field
> names in your view.
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "Mark S. Waterbury" <mark.s.waterbury@xxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 06/11/2004 11:13 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
>
>
>
>
>
>
> 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.
> >
>
> --
> 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.
>