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

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].