Bill Moran said:
I've hit an SQL problem that I'm a bit mystified by. I have two different questions regarding this problem: why? and how do I work around it?
The following query:
SELECT GCP.id, GCP.Name FROM Gov_Capital_Project GCP, WHERE TLM.TLI_ID = $2 group by GCP.id ORDER BY gcp.name;
Produces the following error:
ERROR: column "gcp.name" must appear in the GROUP BY clause or be used in an aggregate function
Since you're not agregating data, can't you use a select distinct instead?
Not sure. I'll have to get back to the programmer who wrote the orignal SELECT and find out what kind of data he is actually trying to acquire.
SELECT distinct GCP.id, GCP.Name FROM Gov_Capital_Project GCP, {?something missing here?} WHERE TLM.TLI_ID = $2 ORDER BY gcp.name;
(BTW, I wasn't clear if the where clause trying to join to another table?)
Yes, my bad. The actual query causing the problem is a bit longer with about 6 joins to it. I did test:
select id, name from gov_capital_project group by id order by name;
and it causes the same error, so I thought I'd make the question simpler by removing the parts that obviously weren't contributing to the problem.
Doesn't answer your original question, but hope it helps anyway.
It may, thanks for the input!
Like I said, the most important part (to me) is to understand why Postgres refuses to run this. The fact that I don't know why points to an obvious lack of understanding on my account, and I'd like to remedy that :D
To that effect, if anyone can point me to a doc that will help me gain a better understanding of why this error occurs, I'd be happy to read it!
-- Bill Moran Potential Technologies http://www.potentialtech.com
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
