Oracle - Not a Group By Expression

[Sandeep Advani]

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

[Jeremy Wilson]

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]

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.

[Mike Rainville]

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]

This is the only query with a group by clause.
Could there be a function missing in the space before ia.accountid? MAX( ... )?
...

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

[ October 14, 2004: Message edited by: Mike Rainville ]