RE: RPG vs SQL

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Thanks for the update JK.  Their answer makes perfect sense.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com
 

-----Original Message-----
Subject: RE: RPG vs SQL

The HelpSystems support desk confirmed the speculation that the (IMO)
more-intuitive syntax of their Sequel product is possible because the Sequel
statement is parsed and reformatted before being passed to the standard i5
SQL
engine. Their reply is copied below.

"SERVER(*SEQUEL) invokes our Sequel syntax giving us direct control over
field
and expression length and column heading attributes, use of derived fields
in the
JOIN clause and other special derivations such as WDATA and CVTDATE that are
not
available in STANDARD SQL.  These features are supported through our use of
the
Classic Query Engine and the Query api [qqqqry]. 
SERVER(*LOCAL) invokes standard SQL syntax.  Views designated as
SERVER(*LOCAL)
are run using the newer Sequel Query Engine.  While this syntax does not
support
the special features of our special SEQUEL syntax, it gives users all the
benefits of the newer SQE"

JK

-----Original Message-----
Elvis replied:
DB2 for i5/OS sticks to ANSI SQL primarily to maintain compatibility with
DB2 LUW
& DB2 for z/OS. They want IBM customers to be able to port SQL applications
without worrying about incompatibilities between different IBM database
platforms.
I personally would wish they don't have to be such stickler to ANSI SQL...
so
many opportunities out there (i.e. my wish list includes making views keyed
since
we already have keyed LFs).

Based on that, it looks to me that ASC programmers must have their own
parsing
routines. I can't tell for sure as I am unfamiliar with Sequel's internals.
Long
time ago I heard that they used to rely on QQQQry API for execution, but
recently
someone mentioned they don't necessarily do that any longer. 
As you said, best to go to the source and ask them.

Elvis


-----Original Message-----
Subject: 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