RE: RPG vs SQL

["JK" <johnking@xxxxxxx>]

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