RE: RPG vs SQL

Elvis,

Many thanks for posting that link on SQL joined updates. Our shop has used
ASC's "Sequel" product for a decade or more and I thought I was getting
fairly good at SQL - blithely assuming that the differences between Sequel
and standard SQL were trivial ones of syntax.

The article shows, however, that standard SQL requires a rather
non-intuitive syntax for joined UPDATE statements, i.e.: you can't just wrap
a "FROM TableA a INNER JOIN TableB b ON a.key_field = b.key_field" with an
UPDATE/SET and expect it to work correctly.

The article mentions that SQL Server's T-SQL extensions do allow the JOIN
to be inside the FROM but that standard SQL does not. I bring this up
because the equivalent "Sequel" statement allows a standardized syntax and
performs exactly the way you'd wish: updates TableA only when the keys
match. No worries about SQL0811 and no unwanted nulls. See below example.

UPDATE SET((a.update_field b.update_field))
SQL('FROM TableA a, TableB b INNER JOIN
a.key_field=b.key_field')

Question: Is it possible that iSeries SQL allows non-standard SQL
extensions or have the ASC programmers cleverly parsed and reformatted the
statement to allow their own syntax? I'm making a note for next week to ask
their tech-support about this, but thought I'd see whether anyone on this
list has already investigated this.

JK

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Friday, February 15, 2008 11:12 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: RPG vs SQL

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%20200
6.
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