Re: SQL using WITH/AS (Solved)

On Thu, 26 Apr 2012 07:19:35, Rob Dekko wrote:

This also just worked fine in iNav's run sql scripts.
7.1 with SI44594.

On starting this post, I must offer two apologies. First, to Chuck, who
pointed out (correctly I might add) that my reply to Rob's earlier post was
lacking a proper format, which could make the post difficult to understand.
I'll strive to do better in the future for I rely on this list often.
Second, to the list participants themselves, for I did something in my
original post I should never have done - cleaned up the code before posting
it up for you guys to spend your precious time parsing through. What I
originally put up to the list was this:

WITH temp (balance,cus#) AS
(SELECT field1+field2+field3 AS balance,cus#
FROM lib.file)

SELECT *
FROM temp
WHERE balance>0

Fields 1, 2, & 3 represented 'current' charges due from a customer, and the
record I'm extracting the information from has past due amounts as well. I
was working out the syntax of the statement first, then I was going to go
forward and include the 'past due' fields as well.

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

After looking at Rob's example and re-keying the script (considering what
Chuck had suggested with random/foo characters), the error still occurred.
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.

Think I've learned my lesson here; my apologies to the list.

Buzz