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.