|
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.