Re: Can I replace RPG with SQL?

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.