What am I doing wrong? Alias ... [message #356420] |
Thu, 30 October 2008 12:33  |
imdreaming
Messages: 11 Registered: April 2008
|
Junior Member |
|
|
This is not my day. I got the inline SQL select working, thanks to help from here.
Now, I am folding that back into my overall query and having trouble:
SELECT count(qt.body_id) field1,
(SELECT SUM (adjustment)
FROM end e
WHERE e.body_id = qt.body_id
AND e.status_id(+)=1302) as nfield
FROM body rb, quote qt
WHERE rb.body_id = qt.body_id
group by nfield;
The group by is not recognizing the alias. I've also tried it with just (subquery text) nfield
Thanks in advance.
|
|
|
|
Re: What am I doing wrong? Alias ... [message #356425 is a reply to message #356423] |
Thu, 30 October 2008 12:58   |
imdreaming
Messages: 11 Registered: April 2008
|
Junior Member |
|
|
When I change the query to this (i.e., remove the count(...) from qt.body_id)
SELECT qt.body_id field1,
(SELECT SUM (adjustment)
FROM end e
WHERE e.body_id = qt.body_id
AND e.status_id(+)=1302) as nfield
FROM body rb, quote qt
WHERE rb.body_id = qt.body_id;
It works.
But my requirement is that I get a count of qt.body_id. The way my data is structured, this subquery the only way I am able to get valid sum from table end.
ETA: I have another value I can add in to group on. I have posted a very simplified version of what I'm doing.
Thanks in advance.
[Updated on: Thu, 30 October 2008 13:02] Report message to a moderator
|
|
|
|
Re: What am I doing wrong? Alias ... [message #356435 is a reply to message #356425] |
Thu, 30 October 2008 13:16   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
How about you posting some test data to play with and ofcourse with the expected output. It will be lot more easier for someone to help you with other alternative if feasible.
Regards
Raj
|
|
|
|