Re: Can I replace RPG with SQL?

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Assuming ClientID is unique in both tables....

update ClientTable
set ClientVersion = ClientVersion + 1
where clientID in (select clientID from NewClientTable);

insert into ClientTable (ClientID, ClientName, ClientVersion)
(select ClientID, ClientName, 0
 from NewClientTable);

HTH,
Charles


On Thu, Sep 16, 2010 at 9:44 AM, David FOXWELL <David.FOXWELL@xxxxxxxxx> wrote:
> I don't think this is easy enough to make it worthwile with sql, but, maybe.....
>
>
>
> Here's my client file that I want to update :
>
> ClientId    ClientName  ClientVersion
> 15              Bigs                    0
>
>
> I receive a file containing clientId and the new name of the client, eg, clientId 15 becomes 'Smith'. But I can't just update the client file from this work file. I need to archive the original name, so I have this kind of representation after the update :
>
> ClientId    ClientName  ClientVersion
> 15              Smith                   0
> 15              Bigs                    1
>
> There's a timestamp field among the other fields. Version 0 is always updated to n+1 and a new line is written to the file. So, in the example, I've updated the line Bigs and I've written the line Smith.
>
> So, version 0 is the current version and 1 is the old one. If I change now to 'Eggs', I want to see :
>
> ClientId    ClientName  ClientVersion
> 15              Eggs                    0
> 15              Bigs                    1
> 15              Smith                   2
>
>
>
> I ask because I get this request now and again, for a few thousand clients at a time. I'm having to use an RPG to write to the client file, but I always end up using SQL to prepare a work file for that RPG.
>
> Thanks.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.