Logical File, record lock

[sara]

I am trying to accomplish 2 things. I need to change all email addresses in a file to lower case. Easy enough, you'd think so. Problem comes in when I try to update. There is another record out there with a duplicate key. In other words, some email addresses are in the file in uppercase, and lower case. Rather than manual trying to fix each duplicate record, I am trying to chain out to the logical and update it, and delete the input record. I am getting a record lock.
I have prefixed all the fields for the logical and called the PF input. Any idea. I'm sure I am overlooking something, but I don't know what.
Help

[GLS400]

Re: Logical File, record lock

Hi Sara:

Use the PF as the update file.

Convert the PF address to lower case and use THAT field to chain to the logical file.
If found delete the logical then update the physical.

Best of Luck
GLS

[sara]

Re: Logical File, record lock

I'll try that. I think the problem is that when I change it to a lower case, then chain, it finds itself. If I could check for any uppercase before the chain I think it would work. I don't know how to check for any uppercase anywhere within a field.
Anyone?
Thanks

[GLS400]

Re: Logical File, record lock

Hi Sara:

It can only find itself if it is already in lower case. Make sure the lower case version is NOT the same as the origional version before deleting or updating anything.

Best of Luck
GLS

[sara]

Re: Logical File, record lock

Thats right. How do I do that? I want to remove dups, and only leave the lowercase version of the record.

[jamief]

Re: Logical File, record lock

We need to call Birgitta -- she can whip up an sql statement
that will UPPER case all the emails and delete the one with the
higher RRN.

then just UPPER the remaining email addresses..
and stop lowercase from being entered.

jamie

[sara]

Re: Logical File, record lock

We want the lower case ones. I think I'm on the right track here. Is there another approach to this. The input programs have been changed to only allow lowercase, that's why we are cleaning up the files now.
Thanks!

[GLS400]

Re: Logical File, record lock

Hi Sara:

pseudo code:

     NewEmail= %XLATE(Upper : Lower : OldEmail);
     If NewEmail <> OldEmail;
       Chain (NewEmail) Logical;
          If %found(Logical);
                delete logical;
          EndIf
       OldEmail = NewEmail;
       Update Physical;
      EndIf

[sara]

Re: Logical File, record lock

Genius! I was just trying to use an array to check the email for all uppercase, but you are the best today! Many thanks. I can always count on one of you!

[FaStOnE]

Re: Logical File, record lock

Oh just freaking GREAT! Now, GLS is gonna have the big head all day thinking he's SOMETHING SPECIAL!!

[tomholden]

Re: Logical File, record lock

Hi Sara:

pseudo code:

     NewEmail= %XLATE(Upper : Lower : OldEmail);
     If NewEmail <> OldEmail;
       Chain (NewEmail) Logical;
          If %found(Logical);
                delete logical;
          EndIf
       OldEmail = NewEmail;
       Update Physical;
      EndIf

sometimes the simple solutions are the ones you can't remember...what was i saying?