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
