Re: SQL using WITH/AS

On 25 Apr 2012 10:55, Buzz Fenner wrote:

Rob on Wednesday, April 25, 2012 10:52 AM wrote:

I pasted that into STRSQL and slightly modified it
WITH temp (balance,iprod) AS
(SELECT iopb+irct+iadj-iiss AS balance,iprod
FROM erplxf.iim)
SELECT *
FROM temp
I did do an F13 and told it to use SQL instead of SYS
Rob Berendt
--

Wow, Rob. On a lark, I ran the same code using interactive SQL, then
re-ran it adding the Where clause, and it worked fine. So, what's
the difference in running the code in a green screen and running it
under i Navigator.
Go figure...

FWiW, the subject "WITH/AS" syntax of a SELECT is commonly referred to as a Common Table Expression (CTE). Additionally, if interested...

The above quoted message was difficult to read for lack of "quoting" [since added in the above]. And its attribution was to a message sent by "midrange-l-request@xxxxxxxxxxxx" instead of the original reply-to value "rob@xxxxxxxxx" [since /corrected/ to show "Rob"]. I do not use the email LIST service, so I do not know why that happens, but I only see that effect pervasively in posts from Jon Paris; possibly an artifact of using the Digest mode.?

I found the message even more difficult to read however, because the reply, being a "bottom reply", was inserted after a *begin signature* line "-- "; i.e. the above reply by Buzz appeared to be part of Rob's signature text instead of as part of the message. Originally I had dismissed the message entirely, because I thought it was an accidental reply with no reply text at all.... because "signature lines" of data are "grayed out" in my email client.

At least, if\when not using the USENET-style quoting, please be so kinds as to remove any lines that begin with and contain only "-- ", esp. if such line(s) precede the reply. If the lines had been /quoted/ using the ">" characters [as done in this message; see the line with and line following, the token "Berendt"], then the "-- " line is not regarded as a "begin signature" indicator by the email and NewsReader clients that honor the convention.

Regards, Chuck