As a suggestion for readability, you might want to take the time to change these nested table expressions to common table expressions.
With frobozztable as (select foo, bar, baz . . . from ham, eggs, spam where .
. . group by . . .),
Table2 as (...)
Select ... from ... left join frobozztable on ... left join ...
Once you do that (easier said than done), you can comment out your main select and just run a select on each individual CTE to check for syntax.
-Tom Stieger
IT Manager
California Fine Wire
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of James H. H. Lampert
Sent: Monday, April 29, 2013 12:21 PM
To: Midrange Systems Technical Discussion
Subject: Re: Weird SQL error
On 4/29/13 12:04 PM, rob@xxxxxxxxx wrote:
I agree with Buck. If you have
select customernumber, customerName, sum(orderDollars) as
TotalOrderDollars from orderheader group by customernumber
you will get that error because customerName is not in the group by,
nor is it in any summary functions. Is this why?
It could very well be. The query in question is over 170 lines long, and has multiple constructs of the form
. . .left join (select foo, bar, baz . . . from ham, eggs, spam where .
. . group by . . . ) as frobozztable on . . . left join . . .
and with my meager understanding of SQL, I'm having trouble breaking the thing down into individually testable pieces. Unfortunately, there "ACCOUNT_ID" appears 62 different times in the query, both as a field being referenced and as a field being defined in the temporary table declarations (or whatever those constructs are called).
Do I sound frustrated?
Further advice would be appreciated.
--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
