Ed, You're right on the money here ( I believe). The difference seems to be with the wildcards:
Before indexing the field NAME I could use the following command with no trouble: SELECT CLIENT WITH NAME CONV MCU = "BRUCE]" After I indexed the field NAME I can ONLY use the following command to retrieve the same results: SELECT CLIENT WITH NAME CONV MCU = "[BRUCE]" notice the difference in the way I'm using the wildcards. It appears when you use CONV MCU with an indexed field you have to use wildcards on the front and back of the item you're searching for. I could not get it to work with wildcards just on the back. If anyone has a reason why this is like this, I would be very interested to know why. Chris > From: u...@edclark.net > Date: Tue, 26 Jul 2011 14:44:00 -0400 > To: u2-users@listserver.u2ug.org > Subject: Re: [U2] CONV MCU on indexed field? > > 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 _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
