I won't advocate for either solution, but if you're curious about joined SQL
UPDATE syntax, take a look at the "Anatomy of a joined UPDATE" article on
page 9 in this Centerfield newsletter:
http://www.centerfieldtechnology.com/publications/archive%5CDecember%202006.
pdf
It should be enough to get you started.
Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: RPG vs SQL
I have a long meeting scheduled this afternoon. The owner of the MS-SQL
database will likely request that a field in a file on the System i be
changed
to be the same as is the MS-SQL database. Specifically, an item
description.
The file on the System i is hospital wide, but the MS-SQL application is
specific to surgery.
If this change is requested, and authorized, I can see how to do it with
RPG.
Specifically:
Export data from MS-SQL database to include appropriate fields.
Upload the exported file to System i
Run an RPG program that reads the uploaded file and subsequently chains to
the
appropriate record on System i, then update the field.
Seems simple enough to do as is.
I was wondering if using SQL would be better in some way. If so, what would
be
required to do the update.
Part of me thinks that RPG is the better and even simpler route, due to data
field differences. On the MS-SQL database, the common field between the two
systems is defined as character. That is because that field can contain an
inventory number, or the two letters "NC". But, on the System i, that field
is
defined as numeric only. Also, the length of the description may not be the
same. As far as the "NC" stuff, when I extract the data, I can select
records
not containing that value, so that is likely not that big an issue.
Is there a compelling argument to use either SQL or RPG to do this? Only
obvious one that comes to mind is that with the RPG route, I can produce a
report showing what was changed, which would be looked at and ignored.
If SQL is the perferred route, how would the files be joined? I cane see
this:
UPDATE BICMAST AS A, SET A.DESC =
WHERE A.CDM = B.CDM
I don't know the syntax well enough to do the JOIN between the two tables,
which
is the compelling reason to go the RPG route.
But, it seems kind of tedious to write an RPG program for a one time update.
Whether I am told to do this, or not, I would apreciate knowing the SQL
syntax.
John McKee
midrange.com
