Re: Group by error!!!

I suggest you add an inline view -
(
SELECT p.pub_nm, b.ean_no FROM publisher p, book b

    WHERE p.pub_id=b.pub_id ) publisher

Then outer join publisher and merchandise publisher.ean_no (+) = m.ean_no

And instead of the case statement in the select, simply put -

nvl(publisher.pub_nm, 'n/a') "Publisher",

Then group by nvl(publisher.pub_nm, 'n/a')

Hope this helps. Next time post the table creation scripts also, so that the error can be reproduced at my end.

Pratap

PS -
CASE
  WHEN m.merch_type_cd='BOOK'
   THEN( SELECT p.pub_nm FROM publisher p, book b     WHERE p.pub_id=b.pub_id AND b.ean_no=m.ean_no AND ROWNUM =1     GROUP BY p.pub_nm)
   ELSE 'n/a'

You do not need a GROUP BY p.pub_nm here. Received on Thu Sep 02 2004 - 06:02:20 CEST