dbTalk Databases Forums  

Re: Group by error!!!

comp.databases.oracle comp.databases.oracle


Discuss Re: Group by error!!! in the comp.databases.oracle forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Pratap
 
Posts: n/a

Default Re: Group by error!!! - 09-01-2004 , 02:06 AM






You should put the entire case statment for "Publisher" in the group by.

Pratap

Reply With Quote
  #2  
Old   
mark anthony
 
Posts: n/a

Default Re: Group by error!!! - 09-01-2004 , 11:06 AM






pratap_fin (AT) rediffmail (DOT) com (Pratap) wrote in message news:<830861d2.0408312306.661a50c3 (AT) posting (DOT) google.com>...
Quote:
You should put the entire case statment for "Publisher" in the group by.

Pratap
Thanks Pratap, but i forgot to mention that i tried that already, and
it didn't work.

Anyone else?


Reply With Quote
  #3  
Old   
Pratap
 
Posts: n/a

Default Re: Group by error!!! - 09-01-2004 , 11:02 PM



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.

Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.