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

---- Msg sent via Internet America Webmail - http://www.internetamerica.com/