Home » SQL & PL/SQL » SQL & PL/SQL » Long data types in Distinct or group by
Long data types in Distinct or group by [message #276287] Wed, 24 October 2007 12:13 Go to next message
vismita
Messages: 4
Registered: October 2007
Junior Member
Hi,

I need to run a query as

select a,b,c,count(*)
from tbl_name
group by a,b,c having count(*)>1

But c is a long data type.
and this is not allowed....Is it possible to achieve this by PL/SQL scripts?

Thanks
Re: Long data types in Distinct or group by [message #276290 is a reply to message #276287] Wed, 24 October 2007 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to convert your long into varchar2 and this is only possible if your long does not exceed 32K.
Of course this is a performance killer.

Regards
Michel
Re: Long data types in Distinct or group by [message #276294 is a reply to message #276290] Wed, 24 October 2007 12:49 Go to previous messageGo to next message
vismita
Messages: 4
Registered: October 2007
Junior Member
Could u plz provide some sample code?
Re: Long data types in Distinct or group by [message #276295 is a reply to message #276287] Wed, 24 October 2007 12:54 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Google is your friend, but only if you use it.
Re: Long data types in Distinct or group by [message #276296 is a reply to message #276295] Wed, 24 October 2007 12:58 Go to previous messageGo to next message
vismita
Messages: 4
Registered: October 2007
Junior Member
I though I could get some advice faster from experienced ppl...Anyways thank u very much...
Re: Long data types in Distinct or group by [message #276298 is a reply to message #276294] Wed, 24 October 2007 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I could if only you read Forum Guide and don't use IM speak.

Regards
Michel
Re: Long data types in Distinct or group by [message #276305 is a reply to message #276298] Wed, 24 October 2007 13:26 Go to previous messageGo to next message
vismita
Messages: 4
Registered: October 2007
Junior Member
Sorry I was not aware of the IM speak rule...Could you please help me?
Re: Long data types in Distinct or group by [message #276306 is a reply to message #276305] Wed, 24 October 2007 13:30 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like:
create or replace to_varchar2 (p_rid rowid)
is
  res varchar(32560);
begin
  select c into res from mytable where rowid=p_rid;
  return res;
end;
/
select a,b,to_varchar2(rowid) c, count(*)
from mytable
group by a,b,to_varchar2(rowid)
/

Regards
Michel
Previous Topic: Need to compare the values with previous recrod with cursor.
Next Topic: how to check if job is running from different session
Goto Forum:
  


Current Time: Fri Dec 09 17:44:31 CST 2016

Total time taken to generate the page: 0.24355 seconds