Ah yes... welcome to the Universe query processor. Just try working with exploded sorts with PICK type dictionaires- then you will start to cry...
Chris is this in a BASIC program that this select is happening? Is it using User Input? If so, you can save yourself a TON of trouble, by just doing your case insensitive stuff in BASIC and forgetting about using dictionaries to do it :) ala OCONV(), or UPCASE() On Tue, Jul 26, 2011 at 2:44 PM, Ed Clark <u...@edclark.net> wrote: > universe is doing something fundamentally weird with this. The select works > differently if you use a wildcard or have an index. > > Create a file with some multi-case data: > 0001 EXECUTE 'CREATE-FILE TESTFILE 1 1' > 0002 OPEN 'TESTFILE' TO FP ELSE ABORT > 0003 CLEARFILE FP > 0004 OPEN 'DICT','TESTFILE' TO DFP ELSE ABORT > 0005 WRITE 'D':@AM:1:@AM:@AM:'D1':@AM:'10L' ON DFP,'D1' > 0006 WRITE 'D':@AM:1:@AM:'MCU':@AM:'D1MCU':@AM:'10L' ON DFP,'D1MCU' > 0007 WRITE 'AB' ON FP,1 > 0008 WRITE 'ab' ON FP,2 > 0009 WRITE 'Ab' ON FP,3 > 0010 WRITE 'aB' ON FP,4 > 0011 EXECUTE 'LIST TESTFILE D1 D1MCU' > > Now try some queries: > LIST TESTFILE WITH D1 = "AB" D1 D1MCU 08:03:14pm 25 Aug 2010 PAGE 1 > TESTFILE.. D1........ D1MCU..... > > 1 AB AB > > 1 records listed. > > LIST TESTFILE WITH D1 = "AB]" D1 D1MCU 08:03:47pm 25 Aug 2010 PAGE 1 > TESTFILE.. D1........ D1MCU..... > > 1 AB AB > > 1 records listed. > > LIST TESTFILE WITH D1MCU = "AB" D1 D1MCU 08:04:19pm 25 Aug 2010 PAGE 1 > TESTFILE.. D1........ D1MCU..... > > 1 AB AB > > 1 records listed. > > > The above all make sense. But not try D1MCU with a wildcard: > > LIST TESTFILE WITH D1MCU = "AB]" D1 D1MCU 08:05:43pm 25 Aug 2010 PAGE > 1 > TESTFILE.. D1........ D1MCU..... > > 1 AB AB > 2 ab AB > 3 Ab AB > 4 aB AB > > 4 records listed. > > If you use both the MCU conversion AND a wildcard, universe is using > different rules for selection. > > BUT... it gets even weirder with an index. After building an index on > either D1 or D1MCU: > > LIST TESTFILE WITH D1MCU = "AB]" D1 D1MCU 08:07:39pm 25 Aug 2010 PAGE > 1 > TESTFILE.. D1........ D1MCU..... > > 1 AB AB > 3 Ab AB > > 2 records listed. > > WTF? If you throw on the NO.INDEX keyword, the index is ignored and > universe goes back to selecting all the items. > > So essentially universe is doing some hack so that it can deal with > wildcards on converted data, but the result is inconsistent queries. > Bottom line, if you want to do case-insensitive selects, you need to either > move to D3, or use a calculated attribute. For example: > > A1 > 0001 A > 0002 1 > 0003 A1 > 0004 > 0005 > 0006 > 0007 > 0008 MCU > 0009 L > 0010 10 > > The MCU in the correlative position (line 7) makes sure that your query is > always seeing the upcased data. > > On Jul 26, 2011, at 2:09 PM, Ed Clark wrote: > > > universe or unidata? what flavor? > > > > I don't see how this would have worked in the first place? When you use a > query with literal comparisons, the literal is compared against the > pre-converted value. If the attribute has a reversible conversion then your > literal will be reverse-converted first, for example: > > SELECT INVOICE-FILE WITH INVOICE.DATE = "12/31/2010" > > if INVOICE.DATE has a date conversion then "12/31/2010" will be converted > to an internal value before comparison. But in your example MCU is not > reversible. So you will only find what you ask for literally. > > > > Is it possible that you were using a different attribute that does a > computation? A virtual attribute on unidata or a correlative on universe > with does the upcasing? In that case then your literal in the query would be > compared against the calculated value. An index would also be built on the > calculated value > > > > On Jul 26, 2011, at 1:37 PM, Chris Austin wrote: > > > >> > >> I changed the DICT entry for field NAME on the CLIENT table to the > following: > >> > >> NAME > >> 0001 D > >> 0002 5 > >> 0003 MCU > >> 0004 Name > >> 0005 25L > >> 0006 S > >> 0007 > >> 0008 CHARACTER,25 > >> > >> But it still doesn't seem to work in regards to the case-sensitive. > >> > >> Chris > >> > >> > >> > >>> From: bl...@shaklee.com > >>> To: u2-users@listserver.u2ug.org > >>> Date: Tue, 26 Jul 2011 09:44:35 -0700 > >>> Subject: Re: [U2] CONV MCU on indexed field? > >>> > >>> We are using Unidata and this is a dict for Country: > >>> > >>> CNTRY: > >>> > >>> D > >>> > >>> 4 > >>> > >>> MCU > >>> > >>> Cntry > >>> > >>> 6L > >>> > >>> M > >>> > >>> > >>> You could do the same change to the NAME definition but I was > suggesting > >>> that if you didn't want to or couldn't do that then another definition > could > >>> be created that forced the name to be upper-case. > >>> > >>> -----Original Message----- > >>> From: u2-users-boun...@listserver.u2ug.org > >>> [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Chris > Austin > >>> Sent: Tuesday, July 26, 2011 9:36 AM > >>> To: u2-users@listserver.u2ug.org > >>> Subject: Re: [U2] CONV MCU on indexed field? > >>> > >>> > >>> > >>> I'm not exactly following you. Would this dict item called NAME.UPCASE, > >>> would that be a new field to test this or woudl that be a specific > field > >>> used to do the conversion? What would the dictionary look like. > >>> > >>> Chris > >>> > >>> > >>>> From: bl...@shaklee.com > >>>> To: u2-users@listserver.u2ug.org > >>>> Date: Tue, 26 Jul 2011 09:32:48 -0700 > >>>> Subject: Re: [U2] CONV MCU on indexed field? > >>>> > >>>> Could you create another dict item called NAME.UPCASE and put the MCU > >>>> in that definition? > >>>> > >>>> -----Original Message----- > >>>> From: u2-users-boun...@listserver.u2ug.org > >>>> [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Chris > >>>> Austin > >>>> Sent: Tuesday, July 26, 2011 9:25 AM > >>>> To: u2-users@listserver.u2ug.org > >>>> Subject: [U2] CONV MCU on indexed field? > >>>> > >>>> > >>>> > >>>> We've been using the following command to select a name regardless of > >>>> how it's capitalized: > >>>> > >>>> SELECT CLIENT WITH NAME CONV MCU = "BRUCE]" > >>>> > >>>> However, after I indexed the field NAME on the CLIENT table the same > >>>> command only returns the values that match the case-sensitive. In > >>>> other words after we indexed the field name it doesn't seem that we > >>>> can use CONV MCU, is there a work around to this? > >>>> > >>>> Thanks, > >>>> > >>>> Chris > >>>> > >>>> > >>>> _______________________________________________ > >>>> U2-Users mailing list > >>>> U2-Users@listserver.u2ug.org > >>>> http://listserver.u2ug.org/mailman/listinfo/u2-users > >>>> _______________________________________________ > >>>> U2-Users mailing list > >>>> U2-Users@listserver.u2ug.org > >>>> http://listserver.u2ug.org/mailman/listinfo/u2-users > >>> > >>> _______________________________________________ > >>> U2-Users mailing list > >>> U2-Users@listserver.u2ug.org > >>> http://listserver.u2ug.org/mailman/listinfo/u2-users > >>> _______________________________________________ > >>> U2-Users mailing list > >>> U2-Users@listserver.u2ug.org > >>> http://listserver.u2ug.org/mailman/listinfo/u2-users > >> > >> _______________________________________________ > >> U2-Users mailing list > >> U2-Users@listserver.u2ug.org > >> http://listserver.u2ug.org/mailman/listinfo/u2-users > > > > _______________________________________________ > > U2-Users mailing list > > U2-Users@listserver.u2ug.org > > http://listserver.u2ug.org/mailman/listinfo/u2-users > > _______________________________________________ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > -- John Thompson _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
