Re: Weird SQL error

On 4/29/2013 3:20 PM, James H. H. Lampert wrote:

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.

Use your favourite editor. Paste the behemoth into it and format it so
you can grok it better. Sometimes what helps me is to look at each JOIN
as a separate query, one at a time. With a decent editor, it's pretty
simple to pick them out separately and paste them into iSQL if you want
to see what gets returned.

I try to convince the team to put the really complex stuff into a view.
With expository comments in the source. Instead of

-- join X to Y via customer
select ... from x left join y on x.custID=y.custID

I try to get them to say

-- every customer has a sales office
-- append sales office info to customer info
-- customers without a sales office have a problem!
-- the programs that find null will handle it as an the error
select ... from x left join y on x.custID=y.custID

I like Notepad++ for this sort of picking apart, but use whatever works
for your fingers.

--buck