Re: SQL using WITH/AS

On 25 Apr 2012 08:40, Buzz Fenner wrote:

I've stared at this simple select statement until I've gotten a
headache. It must be something simple that I've missed. This is the
syntax:

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

The use of the "#" symbol should be avoided; even if delimited, IMO. If the character must be used, e.g. in an existing column name as shown, then use "CUS#" instead. Though even if that were at fault, that might not help overal, because the real problem might be with the language environment [setup] for the client or the server; i.e. the proper conversion between character encoding ASCII and EBCDIC may be getting performed properly according to setup on both ends, but there is a mismatch betwixt.

The processor doesn't like the '>' but I believe it's actually
pointing to a problem with my mapped field - balance. I've
substituted = for>; it still throws the same error (SQL0104).

What is the full text for the SQL0104? That should be telling, for what the parser is expecting for the given statement.

However, if I run the script eliminating the WHERE clause in the
full-select, it runs nicely returning both columns as I think they
should look.

I hate arguing with script processors. BTW, I'm doing this using
System i Navigator.

The pasted text showed more extra lines than I would expect. Perhaps there is some [even possibly non-visible] text embedded somewhere that might be visible if pasted into a 5250 session; i.e. having copied data even beyond the zero following the greater-than sign?

Regards, Chuck