• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Oracle - Not a Group By Expression

 
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello Friends:

I have this query which is common to SQL as well as Oracle. Unfortunately, one of my clients complained that this query failed against their database. What actually surprised me that if I run this query against some test database in Oracle, it works fine (in the sense, returns result if any) but running this on their database gives me an error
ORA-00979: not a GROUP BY expression with the second select statement highlighted.
Any insights on this?

select a.AccountID, a.AccountNumber,
(select AccountNumber from Account where AccountID =
a.MasterAccountID) MasterAccountNumber,
(select count(AccountID) from Account where MasterAccountID =
a.AccountID) hasChildren,
(select bs.Name from BillingSystem bs where bs.BillingSystemID =
a.BillingSystemID) BillingSystemName,
(select c.Name from Carrier c, BillingSystem bs where bs.BillingSystemID
= a.BillingSystemID and c.CarrierID = bs.CarrierID) CarrierName,
(select max(InvoiceNumber) from Invoice i, InvoiceAccount ia where
i.InvoiceID = ia.InvoiceID and ia.AccountID = a.AccountID
and i.InvDate = acct.maxdate) LastInvoice,
acct.maxdate as InvoiceDate

from Account a , (
select ia.accountid as acc, max(i.InvDate) maxdate from
InvoiceAccount ia, invoice i where ia.InvoiceID=i.InvoiceID
group by ia.accountid
) acct

where a.Accountid=acct.acc
and a.AccountID = 5 and a.Accepted = 0

I would be more than happy to offer any further details.

Sandeep
 
Ranch Hand
Posts: 166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
this says to me

select AccountNumber from Account where AccountID =
a.MasterAccountID

returns a single row on one database, I am assuming this is a primary key but on another database it returns multiple rows, possibly not set as a primary key or unique
 
Sandeep Advani
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the reply. I created another temp table on the fly to access the records, worked fine.

I shall post that new query once I reach my office. I still have no idea about the reason for the error.

Data base is same everywhere, thats for sure.

See you tomorrow.
 
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is the only query with a group by clause.
Could there be a function missing in the space before ia.accountid? MAX( ... )?
(
select ia.accountid as acc
, max( i.InvDate ) as maxdate
from InvoiceAccount ia
, invoice i
where ia.InvoiceID = i.InvoiceID
group by ia.accountid
) acct
 
Mike Rainville
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is the only query with a group by clause.
Could there be a function missing in the space before ia.accountid? MAX( ... )?
...

[ October 14, 2004: Message edited by: Mike Rainville ]
 
reply
    Bookmark Topic Watch Topic
  • New Topic