× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.