Toad World® Forums

TDA 1.1.1 -- SubQuery problem


#1

Have not been able to replicate a Weighted avg query in ver 1.1.1 using a subquery that raun successfully in TDA 1.1.0. After I select one field and choose Group By, I can not repeat on a 2nd field. I get this error: ORA-00979: not a GROUP BY expression.

I have tried using various fields as a 2nd field selection upon which to Group By and get the same error.

Is there a workaround? This is a critical query for our group. Thanks


#2

Hello Michael,
The query you are trying to write is syntactically something like this:

select a.category, a.cost, a.price, c.sum_cost, c.sum_price
from
my_table a,
(select b.category, sum (b.cost) as sum_cost, sum (b.price) as sum_price
from my_table b
group by b.category)
where a.category = b.category ;

I was unable to find a workaround in TDA 1.1.1 (the latest commercial version). You would have to type it in the editor. It does work in TDA 2.0 (latest beta). I am sorry about the regression - if I understand correctly, you are saying that you were able to build this query using the TDA 1.1 or TDA 1.0 commercial version?


#3

Jacques - Yes, I was able to do perform this in 1.1. Will fixes be made to 1.1.1 (including 1.1.1 trial) ? Thanks


#4

Hello Michael, are you sure you were able to do this in TDA commercial 1.1.0?

I tried it on an installation of Toad for Data Analysis commercial 1.1.0.328 and when I tried to create a “from clause subquery” with a group by I saw the same error as I see in Toad for Data Analysis commercial 1.1.1.473, namely “ORA-00979 not a GROUP BY expression”