On 26 Apr 2012 10:01, Buzz Fenner wrote:
<<SNIP>>
What I actually had run through the script processor was this:
WITH temp (current,cus#) AS
(SELECT curelc+curwtr+curswr AS current,cusnbr
FROM adjprjlib.cmstrp1)
SELECT *
FROM temp
WHERE current>0
<<SNIP>> Turns out 'current' is a reserved word used with the
special registers. I'm still learning SQL, so I had no idea. I spent
quite a bit of time banging my head against the wall and if I had
just cut/pasted the code to begin with, no doubt one of you guys
would have seen the problem immediately. <<SNIP>>
Including the details from the SQL0104 would have helped as well.
Knowing that the error was issued per "Token > was not valid. Valid
tokens: DATE PATH TIME SQLID SCHEMA ..." would have subtly implied that
the word CURRENT had been used in place of BALANCE; even as nonsensical
with regard to what was given, the obvious reply, would have been to ask
if what was posted was really a copy\paste.
As with the delimited column name suggestion in my earlier reply,
that assists for reserved words as well. The use of a delimited
identifier can prevent such issues; e.g.:
with
temp (current) as (select intcol from qsys2/qsqptabl)
select * from temp where "CURRENT">0
For consistency however, perhaps better:
with
temp ("CURRENT") as (select intcol from qsys2/qsqptabl)
select * from temp where "CURRENT">0
To prevent ever having the issue arise for a previously functional
statement, due to a newly added reserved word, the delimiters can be
added to column names whenever\wherever. The delimiters are optional in
whatever context the usage as a column identifier can be safely assumed
by the parser\syntax-checker. Always best to review the list of
reserved words from the most recent release, to know what identifiers to
avoid or to delimit; e.g.:
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Reserved schema names and reserved words
_Reserved words_
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzwordsre.htm
"This is the list of currently reserved DB2® for i words.
Words may be added at any time. For a list of additional words that may
become reserved in the future, see the IBM® SQL and ANSI reserved words
in the IBM SQL Reference Version 1 SC26-3255. ..."
Regards, Chuck
midrange.com
