I agree, this has to be a bug since the returned data all starts with a B such as the following:
Client.............. Name. C438333-8 BRUCE C593410 BRUCE C593410O BRUCE C595181 BRUCE C595181O BRUCE C595238 BRUCE C595238O BRUCE L000001 BRUCE L000001O BRUCE L000004 BRUCE L000004O BRUCE Absolutely no values are before the B's there'sfore I definately shouldn't need the wildcard. I think I may use John's approash of setting up a new I-descriptor and then having that apply the MCU or upper case command. Thanks for the help today everyone! Chris > Date: Tue, 26 Jul 2011 14:58:57 -0400 > From: [email protected] > To: [email protected] > Subject: Re: [U2] CONV MCU on indexed field? > > Probably a bug. Should be reported to rocket... Although I have no > idea how to do that. I'm sure someone on the list does though. > > On 7/26/11, Chris Austin <[email protected]> wrote: > > > > 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: [email protected] > >> Date: Tue, 26 Jul 2011 14:44:00 -0400 > >> To: [email protected] > >> 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: [email protected] > >> >>> To: [email protected] > >> >>> 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: [email protected] > >> >>> [mailto:[email protected]] On Behalf Of Chris > >> >>> Austin > >> >>> Sent: Tuesday, July 26, 2011 9:36 AM > >> >>> To: [email protected] > >> >>> 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: [email protected] > >> >>>> To: [email protected] > >> >>>> 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: [email protected] > >> >>>> [mailto:[email protected]] On Behalf Of Chris > >> >>>> Austin > >> >>>> Sent: Tuesday, July 26, 2011 9:25 AM > >> >>>> To: [email protected] > >> >>>> 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 > >> >>>> [email protected] > >> >>>> http://listserver.u2ug.org/mailman/listinfo/u2-users > >> >>>> _______________________________________________ > >> >>>> U2-Users mailing list > >> >>>> [email protected] > >> >>>> http://listserver.u2ug.org/mailman/listinfo/u2-users > >> >>> > >> >>> _______________________________________________ > >> >>> U2-Users mailing list > >> >>> [email protected] > >> >>> http://listserver.u2ug.org/mailman/listinfo/u2-users > >> >>> _______________________________________________ > >> >>> U2-Users mailing list > >> >>> [email protected] > >> >>> http://listserver.u2ug.org/mailman/listinfo/u2-users > >> >> > >> >> _______________________________________________ > >> >> U2-Users mailing list > >> >> [email protected] > >> >> http://listserver.u2ug.org/mailman/listinfo/u2-users > >> > > >> > _______________________________________________ > >> > U2-Users mailing list > >> > [email protected] > >> > http://listserver.u2ug.org/mailman/listinfo/u2-users > >> > >> _______________________________________________ > >> U2-Users mailing list > >> [email protected] > >> http://listserver.u2ug.org/mailman/listinfo/u2-users > > > > _______________________________________________ > > U2-Users mailing list > > [email protected] > > http://listserver.u2ug.org/mailman/listinfo/u2-users > > > > -- > Sent from my mobile device > > John Thompson > _______________________________________________ > U2-Users mailing list > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
