Re: [U2] CONV MCU on indexed field?
On 26/07/11 20:56, Chris Austin wrote: George, I just rebuilt the indices on the CLINE table, then tried the LIKE using the '...' as a wildcard, so I tried: SELECT CLIENT WITH NAME LIKE BRUCE... and unfortunately that worked the exact same way as the [] wildcards, returning 1 result. The approach I'm going to use is the one you suggested (to create an I-descriptor to do an UPCASE(NAME) ) and then index that. The only part I'm not sure about is since this descriptor is using the field NAME, should I also index NAME? This imho is what should have been done right from the start. Bear in mind, MCU as applied to a D-descriptor only affects WHAT YOU SEE. If you index that field, is it going to index the *lower* *case* data in the record (which imho it should) or the *upper* *case* version that you see? The only way to be sure that U2 is doing what you want is to force the data into the form you want before it gets indexed. And the only way to be certain that's what's happening is to use an i-descriptor, not a d-descriptor. I've indexed NAME already and seems to be working great, just not sure if indexing NAME should be done for performance. If you're not selecting on it, why bother? Chris The alternative is to do a SELECT WITH EVAL UPCASE(NAME) ... but given what appears to be happening for you at the moment, my mind can't get round the myriad ways this doesn't seem to make sense ... :-) Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
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
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
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
Re: [U2] CONV MCU on indexed field?
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
Re: [U2] CONV MCU on indexed field?
Did you create the index for the field like John Thompson suggested? -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 10:37 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? 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
Re: [U2] CONV MCU on indexed field?
I could be wrong, but if there is an existing index on the dictionary 'NAME' you will need to rebuild the index after you change it for it to take affect with the index. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Lunt, Bruce Sent: Tuesday, July 26, 2011 11:42 AM To: 'U2 Users List' Subject: Re: [U2] CONV MCU on indexed field? Did you create the index for the field like John Thompson suggested? -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 10:37 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? 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
Re: [U2] CONV MCU on indexed field?
Basically what I tried was editing the existing DICT entry for the field NAME, by placing the value MCU in line #3. I don't think this is what John referred to though. Should I keep the field NAME like it is now (without the MCU) and create another DICT entry? Chris From: bl...@shaklee.com To: u2-users@listserver.u2ug.org Date: Tue, 26 Jul 2011 10:41:40 -0700 Subject: Re: [U2] CONV MCU on indexed field? Did you create the index for the field like John Thompson suggested? -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 10:37 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? 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
Re: [U2] CONV MCU on indexed field?
That is up to you. Try rebuilding the index on NAME and see what that gets you. -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 10:51 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? Basically what I tried was editing the existing DICT entry for the field NAME, by placing the value MCU in line #3. I don't think this is what John referred to though. Should I keep the field NAME like it is now (without the MCU) and create another DICT entry? Chris From: bl...@shaklee.com To: u2-users@listserver.u2ug.org Date: Tue, 26 Jul 2011 10:41:40 -0700 Subject: Re: [U2] CONV MCU on indexed field? Did you create the index for the field like John Thompson suggested? -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 10:37 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? 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
Re: [U2] CONV MCU on indexed field?
Could you create another dict item called NAME.UPCASE and put the MCU in that definition? I guess I'm not sure what the above dictionary entry would look like. Would this be a dictionary entry that's just used to do the conversion on NAME? Currently this is what I have for field name: NAME 0001 D 0002 5 0003 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 So I would be creating another DICT entry called NAME.UPCASE, the part I don't understand is #1) what that DICT would look like #2) how that DICT entry would be used Sorry for the numerous questions. Chris From: bl...@shaklee.com To: u2-users@listserver.u2ug.org Date: Tue, 26 Jul 2011 10:41:40 -0700 Subject: Re: [U2] CONV MCU on indexed field? Did you create the index for the field like John Thompson suggested? -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 10:37 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? 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
Re: [U2] CONV MCU on indexed field?
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
Re: [U2] CONV MCU on indexed field?
Assuming you have a DICT item called NAME. Assuming this is not a live system that queries are happening :) Then you changed the conversion on it to MCU. Just do the following: DELETE.INDEX filename NAME CREATE.INDEX filename NAME BUILD.INDEX filename NAME If you wanted to create another DICT item (i.e. column in the flat file world) And each flavor of Universe account has a little different COPY syntax (fun isn't it?) Just do: COPY DICT filename NAME Then type in a new name NAME.UPCASE Then make your changes. Then build an index on NAME.UPCASE CREATE.INDEX filename NAME.UPCASE BUILD.INDEX filename NAME.UPCASE Make sense? On Tue, Jul 26, 2011 at 1:54 PM, Chris Austin cjausti...@hotmail.comwrote: Could you create another dict item called NAME.UPCASE and put the MCU in that definition? I guess I'm not sure what the above dictionary entry would look like. Would this be a dictionary entry that's just used to do the conversion on NAME? Currently this is what I have for field name: NAME 0001 D 0002 5 0003 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 So I would be creating another DICT entry called NAME.UPCASE, the part I don't understand is #1) what that DICT would look like #2) how that DICT entry would be used Sorry for the numerous questions. Chris From: bl...@shaklee.com To: u2-users@listserver.u2ug.org Date: Tue, 26 Jul 2011 10:41:40 -0700 Subject: Re: [U2] CONV MCU on indexed field? Did you create the index for the field like John Thompson suggested? -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 10:37 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? 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
Re: [U2] CONV MCU on indexed field?
Ed might be right... I was just trying to help you understand what I think to be true: Usually if you change a Dictionary that an index is built on, you have to rebuild the index for that dictionary. This is especially true on any I-type dictionary. On Tue, Jul 26, 2011 at 2:09 PM, Ed Clark u...@edclark.net 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 -- John Thompson ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] CONV MCU on indexed field?
This is UniVerse 10.x (newer flavor). This command has been working flawless until we indexed that field. If I remove the index it works using the CONV MCU, perhaps maybe you cannot use the CONV MCU on an indexed field? I tried to delete the index, rebuild it (with the MCU conv), and I ran into the same issue. It's almost like you can't index a field and use MCU on it at the same time.. Chris From: u...@edclark.net Date: Tue, 26 Jul 2011 14:09:13 -0400 To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? 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
Re: [U2] CONV MCU on indexed field?
Chris, The problem is that your index was built case sensitive and now you're requests are upper case only so it will only get upper case indexes. You need to rebuild the index with the dictionary item that does the MCU, that way all of your indexes are upper case. Hth, Curt Stewart Chris Austin cjausti...@hotmail.com wrote: Could you create another dict item called NAME.UPCASE and put the MCU in that definition? I guess I'm not sure what the above dictionary entry would look like. Would this be a dictionary entry that's just used to do the conversion on NAME? Currently this is what I have for field name: NAME 0001 D 0002 5 0003 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 So I would be creating another DICT entry called NAME.UPCASE, the part I don't understand is #1) what that DICT would look like #2) how that DICT entry would be used Sorry for the numerous questions. Chris From: bl...@shaklee.com To: u2-users@listserver.u2ug.org Date: Tue, 26 Jul 2011 10:41:40 -0700 Subject: Re: [U2] CONV MCU on indexed field? Did you create the index for the field like John Thompson suggested? -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 10:37 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? 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
Re: [U2] CONV MCU on indexed field?
Chris, I would create a new i-descriptor dictionary called UNAME. The formula would do the uppercase logic like: OCONV(NAME, MCU) or UPCASE(NAME). Then build your index on UNAME. And use UNAME in your SELECT statements for a filter and display NAME for output. David A. Green (480) 813-1725 DAG Consulting -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
Re: [U2] CONV MCU on indexed field?
Curt, I deleted the index completely (DELETE.INDEX CLIENT NAME). Then I created the DICT for NAME: NAME 0001 D 0002 5 0003 MCU 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 Then I created the INDEX for NAME (CREATE.INDEX CLIENT NAME) Finally I built the indices for NAME (BUILD.INDEX CLIENT NAME) I then tried to to SELECT CLIENT WITH NAME CONV MCU = BRUCE] and only return 1 value (with all caps) instead of about 152. Does the order I'm doing everything appear ok? I'm not sure how to validate the indexes are in upper case, does anyone know how to validate that? And when you refer to rebuilding the index using upper case I assume you mean putting the MCU in the DICT on CLIENT and then doing the CREATE.INDEX/BUILD.INDEX? Chris Date: Tue, 26 Jul 2011 13:27:14 -0500 From: cstew...@tri-sysconsulting.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? Chris, The problem is that your index was built case sensitive and now you're requests are upper case only so it will only get upper case indexes. You need to rebuild the index with the dictionary item that does the MCU, that way all of your indexes are upper case. Hth, Curt Stewart Chris Austin cjausti...@hotmail.com wrote: Could you create another dict item called NAME.UPCASE and put the MCU in that definition? I guess I'm not sure what the above dictionary entry would look like. Would this be a dictionary entry that's just used to do the conversion on NAME? Currently this is what I have for field name: NAME 0001 D 0002 5 0003 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 So I would be creating another DICT entry called NAME.UPCASE, the part I don't understand is #1) what that DICT would look like #2) how that DICT entry would be used Sorry for the numerous questions. Chris From: bl...@shaklee.com To: u2-users@listserver.u2ug.org Date: Tue, 26 Jul 2011 10:41:40 -0700 Subject: Re: [U2] CONV MCU on indexed field? Did you create the index for the field like John Thompson suggested? -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 10:37 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? 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
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 PAGE1 TESTFILE.. D1 D1MCU. 1 AB AB 1 records listed. LIST TESTFILE WITH D1 = AB] D1 D1MCU 08:03:47pm 25 Aug 2010 PAGE1 TESTFILE.. D1 D1MCU. 1 AB AB 1 records listed. LIST TESTFILE WITH D1MCU = AB D1 D1MCU 08:04:19pm 25 Aug 2010 PAGE1 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 PAGE1 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 PAGE1 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
Re: [U2] CONV MCU on indexed field?
It looks like you are doing it in the right order... I wonder if that character limit thing is messing you up in attribute 8? (just a shot in the dark) Also, what flavor of Universe account are you running? You can find this out by: CT VOC cataloged.program.name Look in attribute 6 and it will tell you. That CONV query feature might not work all that well in a PICK flavor account on an indexed field? If all else fails... you can do a work around in BASIC (if this is in a BASIC program) Using OCONV() UPCASE() OR Try the previous posters I type suggestion and see if that works. On Tue, Jul 26, 2011 at 2:38 PM, Chris Austin cjausti...@hotmail.comwrote: Curt, I deleted the index completely (DELETE.INDEX CLIENT NAME). Then I created the DICT for NAME: NAME 0001 D 0002 5 0003 MCU 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 Then I created the INDEX for NAME (CREATE.INDEX CLIENT NAME) Finally I built the indices for NAME (BUILD.INDEX CLIENT NAME) I then tried to to SELECT CLIENT WITH NAME CONV MCU = BRUCE] and only return 1 value (with all caps) instead of about 152. Does the order I'm doing everything appear ok? I'm not sure how to validate the indexes are in upper case, does anyone know how to validate that? And when you refer to rebuilding the index using upper case I assume you mean putting the MCU in the DICT on CLIENT and then doing the CREATE.INDEX/BUILD.INDEX? Chris Date: Tue, 26 Jul 2011 13:27:14 -0500 From: cstew...@tri-sysconsulting.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? Chris, The problem is that your index was built case sensitive and now you're requests are upper case only so it will only get upper case indexes. You need to rebuild the index with the dictionary item that does the MCU, that way all of your indexes are upper case. Hth, Curt Stewart Chris Austin cjausti...@hotmail.com wrote: Could you create another dict item called NAME.UPCASE and put the MCU in that definition? I guess I'm not sure what the above dictionary entry would look like. Would this be a dictionary entry that's just used to do the conversion on NAME? Currently this is what I have for field name: NAME 0001 D 0002 5 0003 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 So I would be creating another DICT entry called NAME.UPCASE, the part I don't understand is #1) what that DICT would look like #2) how that DICT entry would be used Sorry for the numerous questions. Chris From: bl...@shaklee.com To: u2-users@listserver.u2ug.org Date: Tue, 26 Jul 2011 10:41:40 -0700 Subject: Re: [U2] CONV MCU on indexed field? Did you create the index for the field like John Thompson suggested? -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 10:37 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? 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
Re: [U2] CONV MCU on indexed field?
also, if that doesn't worktry it this way as well NAME.UP 0001 I 0002 UPCASE(NAME) 0003 0004 Name 0005 25L 0006 S Then do a CREATE and BUILD index on NAME.UP and make your QUERY against NAME.UP, instead of NAME You can have as many DICT items as you want point to the same attribute number This is common in UV, as you can have data be displayed in multiple formats and have a unique name for each display format. -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 2:38 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? Curt, I deleted the index completely (DELETE.INDEX CLIENT NAME). Then I created the DICT for NAME: NAME 0001 D 0002 5 0003 MCU 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 Then I created the INDEX for NAME (CREATE.INDEX CLIENT NAME) Finally I built the indices for NAME (BUILD.INDEX CLIENT NAME) I then tried to to SELECT CLIENT WITH NAME CONV MCU = BRUCE] and only return 1 value (with all caps) instead of about 152. Does the order I'm doing everything appear ok? I'm not sure how to validate the indexes are in upper case, does anyone know how to validate that? And when you refer to rebuilding the index using upper case I assume you mean putting the MCU in the DICT on CLIENT and then doing the CREATE.INDEX/BUILD.INDEX? Chris Date: Tue, 26 Jul 2011 13:27:14 -0500 From: cstew...@tri-sysconsulting.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? Chris, The problem is that your index was built case sensitive and now you're requests are upper case only so it will only get upper case indexes. You need to rebuild the index with the dictionary item that does the MCU, that way all of your indexes are upper case. Hth, Curt Stewart Chris Austin cjausti...@hotmail.com wrote: Could you create another dict item called NAME.UPCASE and put the MCU in that definition? I guess I'm not sure what the above dictionary entry would look like. Would this be a dictionary entry that's just used to do the conversion on NAME? Currently this is what I have for field name: NAME 0001 D 0002 5 0003 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 So I would be creating another DICT entry called NAME.UPCASE, the part I don't understand is #1) what that DICT would look like #2) how that DICT entry would be used Sorry for the numerous questions. Chris From: bl...@shaklee.com To: u2-users@listserver.u2ug.org Date: Tue, 26 Jul 2011 10:41:40 -0700 Subject: Re: [U2] CONV MCU on indexed field? Did you create the index for the field like John Thompson suggested? -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 10:37 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? 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
Re: [U2] CONV MCU on indexed field?
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 PAGE1 TESTFILE.. D1 D1MCU. 1 AB AB 1 records listed. LIST TESTFILE WITH D1 = AB] D1 D1MCU 08:03:47pm 25 Aug 2010 PAGE1 TESTFILE.. D1 D1MCU. 1 AB AB 1 records listed. LIST TESTFILE WITH D1MCU = AB D1 D1MCU 08:04:19pm 25 Aug 2010 PAGE1 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
Re: [U2] CONV MCU on indexed field?
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 PAGE1 TESTFILE.. D1 D1MCU. 1 AB AB 1 records listed. LIST TESTFILE WITH D1 = AB] D1 D1MCU 08:03:47pm 25 Aug 2010 PAGE1 TESTFILE.. D1 D1MCU. 1 AB AB 1 records listed. LIST TESTFILE WITH D1MCU = AB D1 D1MCU 08:04:19pm 25 Aug 2010 PAGE1 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 PAGE1 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 PAGE1 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
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 cjausti...@hotmail.com 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: 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 PAGE1 TESTFILE.. D1 D1MCU. 1 AB AB 1 records listed. LIST TESTFILE WITH D1 = AB] D1 D1MCU 08:03:47pm 25 Aug 2010 PAGE1 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
Re: [U2] CONV MCU on indexed field?
I agree, this has to be a bug since the returned data all starts with a B such as the following: Client.. Name. C438333-8BRUCE C593410 BRUCE C593410O BRUCE C595181 BRUCE C595181O BRUCE C595238 BRUCE C595238O BRUCE L01 BRUCE L01O BRUCE L04 BRUCE L04O 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: jthompson...@gmail.com To: u2-users@listserver.u2ug.org 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 cjausti...@hotmail.com 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: 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 PAGE1 TESTFILE.. D1 D1MCU. 1 AB AB 1 records listed. LIST TESTFILE WITH D1 = AB] D1 D1MCU 08:03:47pm 25 Aug 2010 PAGE1 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
Re: [U2] CONV MCU on indexed field?
Just curious and maybe I missed it... Has anyone tried LIKE and ... instead? I wouldn't think it would be any different, but then I would have thought the ] select would have worked. Robert Porter, MCSE, CCNA, ZCE, OCP-Java Lead Sr. Programmer / Analyst Laboratory Information Services Ochsner Health System This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful. Chris Austin cjausti...@hotmail.com 7/26/2011 1:57 PM I'm on UniVerse 10.3.3, the problem I was having seems to be with the use of wildcards. SELECT CLIENT WITH NAME CONV MCU = BRUCE] -- DOESN'T WORK SELECT CLIENT WITH NAME CONV MCU = [BRUCE] -- WORKS! I have no idea why this is such, it has to do with an indexed field + MCU + wildcards. Chris Date: Tue, 26 Jul 2011 14:44:59 -0400 From: jthompson...@gmail.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? It looks like you are doing it in the right order... I wonder if that character limit thing is messing you up in attribute 8? (just a shot in the dark) Also, what flavor of Universe account are you running? You can find this out by: CT VOC cataloged.program.name Look in attribute 6 and it will tell you. That CONV query feature might not work all that well in a PICK flavor account on an indexed field? If all else fails... you can do a work around in BASIC (if this is in a BASIC program) Using OCONV() UPCASE() OR Try the previous posters I type suggestion and see if that works. On Tue, Jul 26, 2011 at 2:38 PM, Chris Austin cjausti...@hotmail.comwrote: Curt, I deleted the index completely (DELETE.INDEX CLIENT NAME). Then I created the DICT for NAME: NAME 0001 D 0002 5 0003 MCU 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 Then I created the INDEX for NAME (CREATE.INDEX CLIENT NAME) Finally I built the indices for NAME (BUILD.INDEX CLIENT NAME) I then tried to to SELECT CLIENT WITH NAME CONV MCU = BRUCE] and only return 1 value (with all caps) instead of about 152. Does the order I'm doing everything appear ok? I'm not sure how to validate the indexes are in upper case, does anyone know how to validate that? And when you refer to rebuilding the index using upper case I assume you mean putting the MCU in the DICT on CLIENT and then doing the CREATE.INDEX/BUILD.INDEX? Chris Date: Tue, 26 Jul 2011 13:27:14 -0500 From: cstew...@tri-sysconsulting.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? Chris, The problem is that your index was built case sensitive and now you're requests are upper case only so it will only get upper case indexes. You need to rebuild the index with the dictionary item that does the MCU, that way all of your indexes are upper case. Hth, Curt Stewart Chris Austin cjausti...@hotmail.com wrote: Could you create another dict item called NAME.UPCASE and put the MCU in that definition? I guess I'm not sure what the above dictionary entry would look like. Would this be a dictionary entry that's just used to do the conversion on NAME? Currently this is what I have for field name: NAME 0001 D 0002 5 0003 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 So I would be creating another DICT entry called NAME.UPCASE, the part I don't understand is #1) what that DICT would look like #2) how that DICT entry would be used Sorry for the numerous questions. Chris From: bl...@shaklee.com To: u2-users@listserver.u2ug.org Date: Tue, 26 Jul 2011 10:41:40 -0700 Subject: Re: [U2] CONV MCU on indexed field? Did you create the index for the field like John Thompson suggested? -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 10:37 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? 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
Re: [U2] CONV MCU on indexed field?
I've never used either of those commands, if you can give me an example using each on a select I would be more than happy to test it out: SELECT CLIENT WITH NAME = BRUCE (use this select to modify with the 2 commands) Also, We're using this with a dojo filteringSelect + QueryReadStore. Essentially what happens is our client types a 'payee' letter by letter and the AJAX communicates with our server and sends a request to UniVerse to process these select statements by every keystroke so you actually have a real time autocomplete which is very fast that also acts as a dropdown menu that will eventually be paginated. Chris Date: Tue, 26 Jul 2011 14:11:33 -0500 From: ropor...@ochsner.org To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? Just curious and maybe I missed it... Has anyone tried LIKE and ... instead? I wouldn't think it would be any different, but then I would have thought the ] select would have worked. Robert Porter, MCSE, CCNA, ZCE, OCP-Java Lead Sr. Programmer / Analyst Laboratory Information Services Ochsner Health System This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful. Chris Austin cjausti...@hotmail.com 7/26/2011 1:57 PM I'm on UniVerse 10.3.3, the problem I was having seems to be with the use of wildcards. SELECT CLIENT WITH NAME CONV MCU = BRUCE] -- DOESN'T WORK SELECT CLIENT WITH NAME CONV MCU = [BRUCE] -- WORKS! I have no idea why this is such, it has to do with an indexed field + MCU + wildcards. Chris Date: Tue, 26 Jul 2011 14:44:59 -0400 From: jthompson...@gmail.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? It looks like you are doing it in the right order... I wonder if that character limit thing is messing you up in attribute 8? (just a shot in the dark) Also, what flavor of Universe account are you running? You can find this out by: CT VOC cataloged.program.name Look in attribute 6 and it will tell you. That CONV query feature might not work all that well in a PICK flavor account on an indexed field? If all else fails... you can do a work around in BASIC (if this is in a BASIC program) Using OCONV() UPCASE() OR Try the previous posters I type suggestion and see if that works. On Tue, Jul 26, 2011 at 2:38 PM, Chris Austin cjausti...@hotmail.comwrote: Curt, I deleted the index completely (DELETE.INDEX CLIENT NAME). Then I created the DICT for NAME: NAME 0001 D 0002 5 0003 MCU 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 Then I created the INDEX for NAME (CREATE.INDEX CLIENT NAME) Finally I built the indices for NAME (BUILD.INDEX CLIENT NAME) I then tried to to SELECT CLIENT WITH NAME CONV MCU = BRUCE] and only return 1 value (with all caps) instead of about 152. Does the order I'm doing everything appear ok? I'm not sure how to validate the indexes are in upper case, does anyone know how to validate that? And when you refer to rebuilding the index using upper case I assume you mean putting the MCU in the DICT on CLIENT and then doing the CREATE.INDEX/BUILD.INDEX? Chris Date: Tue, 26 Jul 2011 13:27:14 -0500 From: cstew...@tri-sysconsulting.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? Chris, The problem is that your index was built case sensitive and now you're requests are upper case only so it will only get upper case indexes. You need to rebuild the index with the dictionary item that does the MCU, that way all of your indexes are upper case. Hth, Curt Stewart Chris Austin cjausti...@hotmail.com wrote: Could you create another dict item called NAME.UPCASE and put the MCU in that definition? I guess I'm not sure what the above dictionary entry would look like. Would this be a dictionary entry that's just used to do the conversion on NAME? Currently this is what I have for field name: NAME 0001 D 0002 5 0003 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 So I would be creating another DICT entry called NAME.UPCASE, the part I don't understand is #1) what that DICT would look like #2) how that DICT entry
Re: [U2] CONV MCU on indexed field?
I think your running in PICK mode, and I know that LIKE works in PICK flavor as well. LIKE is the equivilent to [ and ] SELECT FILE WITH NAME = [TEST SELECT FILE WITH NAME LIKE ...TEST SELECT FILE WITH NAME = TEST] SELECT FILE WITH NAME LIKE TEST... SELECT FILE WITH NAME = [TEST] SELECT FILE WITH NAME LIKE ...TEST... REPLACE THE [ OR ] WITH ... AND REPLACE THE = WITH LIKE George -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 3:23 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? I've never used either of those commands, if you can give me an example using each on a select I would be more than happy to test it out: SELECT CLIENT WITH NAME = BRUCE (use this select to modify with the 2 commands) Also, We're using this with a dojo filteringSelect + QueryReadStore. Essentially what happens is our client types a 'payee' letter by letter and the AJAX communicates with our server and sends a request to UniVerse to process these select statements by every keystroke so you actually have a real time autocomplete which is very fast that also acts as a dropdown menu that will eventually be paginated. Chris Date: Tue, 26 Jul 2011 14:11:33 -0500 From: ropor...@ochsner.org To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? Just curious and maybe I missed it... Has anyone tried LIKE and ... instead? I wouldn't think it would be any different, but then I would have thought the ] select would have worked. Robert Porter, MCSE, CCNA, ZCE, OCP-Java Lead Sr. Programmer / Analyst Laboratory Information Services Ochsner Health System This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful. Chris Austin cjausti...@hotmail.com 7/26/2011 1:57 PM I'm on UniVerse 10.3.3, the problem I was having seems to be with the use of wildcards. SELECT CLIENT WITH NAME CONV MCU = BRUCE] -- DOESN'T WORK SELECT CLIENT WITH NAME CONV MCU = [BRUCE] -- WORKS! I have no idea why this is such, it has to do with an indexed field + MCU + wildcards. Chris Date: Tue, 26 Jul 2011 14:44:59 -0400 From: jthompson...@gmail.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? It looks like you are doing it in the right order... I wonder if that character limit thing is messing you up in attribute 8? (just a shot in the dark) Also, what flavor of Universe account are you running? You can find this out by: CT VOC cataloged.program.name Look in attribute 6 and it will tell you. That CONV query feature might not work all that well in a PICK flavor account on an indexed field? If all else fails... you can do a work around in BASIC (if this is in a BASIC program) Using OCONV() UPCASE() OR Try the previous posters I type suggestion and see if that works. On Tue, Jul 26, 2011 at 2:38 PM, Chris Austin cjausti...@hotmail.comwrote: Curt, I deleted the index completely (DELETE.INDEX CLIENT NAME). Then I created the DICT for NAME: NAME 0001 D 0002 5 0003 MCU 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 Then I created the INDEX for NAME (CREATE.INDEX CLIENT NAME) Finally I built the indices for NAME (BUILD.INDEX CLIENT NAME) I then tried to to SELECT CLIENT WITH NAME CONV MCU = BRUCE] and only return 1 value (with all caps) instead of about 152. Does the order I'm doing everything appear ok? I'm not sure how to validate the indexes are in upper case, does anyone know how to validate that? And when you refer to rebuilding the index using upper case I assume you mean putting the MCU in the DICT on CLIENT and then doing the CREATE.INDEX/BUILD.INDEX? Chris Date: Tue, 26 Jul 2011 13:27:14 -0500 From: cstew...@tri-sysconsulting.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? Chris, The problem is that your index was built case sensitive and now you're requests are upper case only so it will only get upper case indexes. You need to rebuild the index with the dictionary item that does the MCU, that way all of your
Re: [U2] CONV MCU on indexed field?
It's actually all just part of the SELECT SELECT FILENAME WITH FIELD LIKE (something)... (similar to = something ]) SELECT FILENAME WITH FIELD LIKE ...(something) [(something) SELECT FILENAME WITH FIELD LIKE ...(something)... [(something)] Chris Austin cjausti...@hotmail.com 7/26/2011 2:22 PM I've never used either of those commands, if you can give me an example using each on a select I would be more than happy to test it out: SELECT CLIENT WITH NAME = BRUCE (use this select to modify with the 2 commands) Also, We're using this with a dojo filteringSelect + QueryReadStore. Essentially what happens is our client types a 'payee' letter by letter and the AJAX communicates with our server and sends a request to UniVerse to process these select statements by every keystroke so you actually have a real time autocomplete which is very fast that also acts as a dropdown menu that will eventually be paginated. Chris Date: Tue, 26 Jul 2011 14:11:33 -0500 From: ropor...@ochsner.org To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? Just curious and maybe I missed it... Has anyone tried LIKE and ... instead? I wouldn't think it would be any different, but then I would have thought the ] select would have worked. Robert Porter, MCSE, CCNA, ZCE, OCP-Java Lead Sr. Programmer / Analyst Laboratory Information Services Ochsner Health System This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful. Chris Austin cjausti...@hotmail.com 7/26/2011 1:57 PM I'm on UniVerse 10.3.3, the problem I was having seems to be with the use of wildcards. SELECT CLIENT WITH NAME CONV MCU = BRUCE] -- DOESN'T WORK SELECT CLIENT WITH NAME CONV MCU = [BRUCE] -- WORKS! I have no idea why this is such, it has to do with an indexed field + MCU + wildcards. Chris Date: Tue, 26 Jul 2011 14:44:59 -0400 From: jthompson...@gmail.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? It looks like you are doing it in the right order... I wonder if that character limit thing is messing you up in attribute 8? (just a shot in the dark) Also, what flavor of Universe account are you running? You can find this out by: CT VOC cataloged.program.name Look in attribute 6 and it will tell you. That CONV query feature might not work all that well in a PICK flavor account on an indexed field? If all else fails... you can do a work around in BASIC (if this is in a BASIC program) Using OCONV() UPCASE() OR Try the previous posters I type suggestion and see if that works. On Tue, Jul 26, 2011 at 2:38 PM, Chris Austin cjausti...@hotmail.comwrote: Curt, I deleted the index completely (DELETE.INDEX CLIENT NAME). Then I created the DICT for NAME: NAME 0001 D 0002 5 0003 MCU 0004 Name 0005 25L 0006 S 0007 0008 CHARACTER,25 Then I created the INDEX for NAME (CREATE.INDEX CLIENT NAME) Finally I built the indices for NAME (BUILD.INDEX CLIENT NAME) I then tried to to SELECT CLIENT WITH NAME CONV MCU = BRUCE] and only return 1 value (with all caps) instead of about 152. Does the order I'm doing everything appear ok? I'm not sure how to validate the indexes are in upper case, does anyone know how to validate that? And when you refer to rebuilding the index using upper case I assume you mean putting the MCU in the DICT on CLIENT and then doing the CREATE.INDEX/BUILD.INDEX? Chris Date: Tue, 26 Jul 2011 13:27:14 -0500 From: cstew...@tri-sysconsulting.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? Chris, The problem is that your index was built case sensitive and now you're requests are upper case only so it will only get upper case indexes. You need to rebuild the index with the dictionary item that does the MCU, that way all of your indexes are upper case. Hth, Curt Stewart Chris Austin cjausti...@hotmail.com wrote: Could you create another dict item called NAME.UPCASE and put the MCU in that definition? I guess I'm not sure what the above dictionary entry would look like. Would this be a dictionary entry that's just used to do the conversion on NAME? Currently this is what I have
Re: [U2] CONV MCU on indexed field?
George, I just rebuilt the indices on the CLINE table, then tried the LIKE using the '...' as a wildcard, so I tried: SELECT CLIENT WITH NAME LIKE BRUCE... and unfortunately that worked the exact same way as the [] wildcards, returning 1 result. The approach I'm going to use is the one you suggested (to create an I-descriptor to do an UPCASE(NAME) ) and then index that. The only part I'm not sure about is since this descriptor is using the field NAME, should I also index NAME? I've indexed NAME already and seems to be working great, just not sure if indexing NAME should be done for performance. Chris From: ggal...@wyanokegroup.com To: u2-users@listserver.u2ug.org Date: Tue, 26 Jul 2011 14:27:58 -0500 Subject: Re: [U2] CONV MCU on indexed field? I think your running in PICK mode, and I know that LIKE works in PICK flavor as well. LIKE is the equivilent to [ and ] SELECT FILE WITH NAME = [TEST SELECT FILE WITH NAME LIKE ...TEST SELECT FILE WITH NAME = TEST] SELECT FILE WITH NAME LIKE TEST... SELECT FILE WITH NAME = [TEST] SELECT FILE WITH NAME LIKE ...TEST... REPLACE THE [ OR ] WITH ... AND REPLACE THE = WITH LIKE George ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] CONV MCU on indexed field?
I'd only index NAME if you are going to use it in any SELECTs that you want optimized. We can't really answer that question. -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 3:56 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? George, I just rebuilt the indices on the CLINE table, then tried the LIKE using the '...' as a wildcard, so I tried: SELECT CLIENT WITH NAME LIKE BRUCE... and unfortunately that worked the exact same way as the [] wildcards, returning 1 result. The approach I'm going to use is the one you suggested (to create an I- descriptor to do an UPCASE(NAME) ) and then index that. The only part I'm not sure about is since this descriptor is using the field NAME, should I also index NAME? I've indexed NAME already and seems to be working great, just not sure if indexing NAME should be done for performance. Chris From: ggal...@wyanokegroup.com To: u2-users@listserver.u2ug.org Date: Tue, 26 Jul 2011 14:27:58 -0500 Subject: Re: [U2] CONV MCU on indexed field? I think your running in PICK mode, and I know that LIKE works in PICK flavor as well. LIKE is the equivilent to [ and ] SELECT FILE WITH NAME = [TEST SELECT FILE WITH NAME LIKE ...TEST SELECT FILE WITH NAME = TEST] SELECT FILE WITH NAME LIKE TEST... SELECT FILE WITH NAME = [TEST] SELECT FILE WITH NAME LIKE ...TEST... REPLACE THE [ OR ] WITH ... AND REPLACE THE = WITH LIKE George ___ 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
Re: [U2] CONV MCU on indexed field?
I believe a long time Universe support person once told me that secondary indices are essentially just Type 25 files, or B-Tree files. So when you build one, its essentially taking output from the query processor and squirreling that away in a Type 25 file at the OS level. So for example, I have an index on our CUSTOMER file for the Dictionary CUST-NAME At the OS level (assuming you are on a *nix system) The CUSTOMER file lives: /uvdata/accts/ACCOUNT/CUSTOMER And then the type 25 files just reside in: /uvdata/accts/ACCOUNT/I_CUSTOMER/INDEX.001 /uvdata/accts/ACCOUNT/I_CUSTOMER/INDEX.002 /uvdata/accts/ACCOUNT/I_CUSTOMER/INDEX.003 /uvdata/accts/ACCOUNT/I_CUSTOMER/INDEX.004 /uvdata/accts/ACCOUNT/I_CUSTOMER/INDEX.MAP and so on... So if you have two Dictionaries like you have. One called NAME One called NAME.UPCASE which is an I-Type that just references NAME You should be just fine on building the index on NAME.UPCASE. I would not think you would have to build the index on both of them. You just need to build the index on the one you are referencing in your SELECT statement. The only disadvantage to going crazy with indexes is that it will take a longer time to do a bunch of writes or copies to the file because all of the indexes have to be updated. So for example, lets say you have to bulk load a ton of clients all at once (a few thousand lets say)... That process will increase with the amount of indexes you have, but, once the data is there, it can be retrieved very fast. Of course, I'm no U2 University Admin graduate, so don't quote me on this... but it seems in my experience that the above is true. On Tue, Jul 26, 2011 at 4:00 PM, George Gallen ggal...@wyanokegroup.comwrote: I'd only index NAME if you are going to use it in any SELECTs that you want optimized. We can't really answer that question. -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 3:56 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] CONV MCU on indexed field? George, I just rebuilt the indices on the CLINE table, then tried the LIKE using the '...' as a wildcard, so I tried: SELECT CLIENT WITH NAME LIKE BRUCE... and unfortunately that worked the exact same way as the [] wildcards, returning 1 result. The approach I'm going to use is the one you suggested (to create an I- descriptor to do an UPCASE(NAME) ) and then index that. The only part I'm not sure about is since this descriptor is using the field NAME, should I also index NAME? I've indexed NAME already and seems to be working great, just not sure if indexing NAME should be done for performance. Chris From: ggal...@wyanokegroup.com To: u2-users@listserver.u2ug.org Date: Tue, 26 Jul 2011 14:27:58 -0500 Subject: Re: [U2] CONV MCU on indexed field? I think your running in PICK mode, and I know that LIKE works in PICK flavor as well. LIKE is the equivilent to [ and ] SELECT FILE WITH NAME = [TEST SELECT FILE WITH NAME LIKE ...TEST SELECT FILE WITH NAME = TEST] SELECT FILE WITH NAME LIKE TEST... SELECT FILE WITH NAME = [TEST] SELECT FILE WITH NAME LIKE ...TEST... REPLACE THE [ OR ] WITH ... AND REPLACE THE = WITH LIKE George ___ 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
