OraFAQ Forum: Reports & Discoverer » Group By clause Issue

Home » Developer & Programmer » Reports & Discoverer » Group By clause Issue

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Group By clause Issue [message #478619]

Mon, 11 October 2010 05:48

lakshmi83
Messages: 12
Registered: August 2008
Location: Hyderabad

Junior Member

Hi,

Can any one advice on the below issue :

I am writing a simple query with 3 columns of which 1 of them has subquery in it and i need to make this query group by count(xyz). when am doing so, its raising error that the subquries cant be used in the group by clause and if i skip that coulmn from the group by , its not accepting.

select distinct count(ORDER_RELEASE) ,order_name,
(SELECT abd||CHR(10)||and FROM LOCATION WHERE LOCATION = DEST_LOCATION)||CHR(10)||
(SELECT WM_CONCAT(address) FROM LOCATIONadd WHERE LOCATION = DEST_LOCATION)||CHR(10)||(SELECT CITY', '||
COUNTRY FROM LOCATION WHERE LOCATION = O.DEST_LOCATION)
from abc su, pqu o
where /* conditions*/
group by ordeR_name

Thanks in Advance.

Report message to a moderator

Re: Group By clause Issue [message #478635 is a reply to message #478619]

Mon, 11 October 2010 06:36

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

The third "column" looks like some kind of an address.

Why did you choose such a design? Every SELECT you used must return a single value (otherwise that query wouldn't work). So, why didn't you create a SIMPLE query (not what you call "simple", but really simple) and put LOCATION table into the WHERE clause, select all columns you need and use them in a GROUP BY clause?

Something like

select 
  order_name,
  l.abd || wm_concat(l.address) || l.city,
  count(*)
from abc su,
     pqu o,
     location l
where l.location = o.dest_location
  and ...
group by order_name, l.abd || wm_concat(l.address) || l.city

Also, PLEASE, learn how to properly format code you post on the forum. As long as it might be understandable to you (as you spent some time working with it), for most of us it is unknown and spending time trying to figure out what you meant to say is time consuming.
If you are unsure of how to do that, check OraFAQ forum guide (you'll find a link at the top of every forum page).

Furthermore, learn how to use table aliases. For example, what table does the "order_release" belong to? It is easier to follow it when you stick to certain "standards".

Report message to a moderator

Re: Group By clause Issue [message #478636 is a reply to message #478619]

Mon, 11 October 2010 06:45

deepakmannazhi
Messages: 137
Registered: February 2010
Location: Dubai, UAE

Senior Member

Dear friend,
please format the code first.

Try this,

SELECT COUNT, order_name,
(SELECT abd || CHR (10) || 'and'
FROM LOCATION
WHERE LOCATION = dest_location)
|| CHR (10)
|| (SELECT wm_concat (address)
FROM locationadd
WHERE LOCATION = dest_location)
|| CHR (10)
|| (SELECT city || ', ' || 'COUNTRY'
FROM LOCATION
WHERE LOCATION = o.dest_location) address
FROM
(SELECT DISTINCT COUNT (order_release) COUNT, order_name
FROM abc su
where /* conditions*/
GROUP BY order_name) a, pqu o

Regards
Deepak

[Updated on: Mon, 11 October 2010 06:59]

Report message to a moderator

Re: Group By clause Issue [message #478638 is a reply to message #478636]

Mon, 11 October 2010 06:54

lakshmi83
Messages: 12
Registered: August 2008
Location: Hyderabad

Junior Member

Sorry Friends for not formatting the code as per standands.
From next time i make sure it is according to the standards.

My Issue was resolved. I resolved this by using the inner queires.

Thanks for you help.

Report message to a moderator

Previous Topic:	Image Issue in HTML Report output
Next Topic:	Oracle 6i Report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 22:55:29 CDT 2024