Home » SQL & PL/SQL » SQL & PL/SQL » How to use denserank function
How to use denserank function [message #199956] Fri, 27 October 2006 02:25 Go to next message
arunprabhu29917
Messages: 15
Registered: September 2006
Location: Tamilnadu
Junior Member


I have created one function,

SQL> create or replace function get_tran(t_no in number)
2 return varchar2
3 is
4 str varchar2(2000) default null;
5 sep varchar2(1) default null;
6 BEGIN
7 FOR X IN (SELECT SERIAL_NO serial_no,LABEL label,STARTDATE startdate,STOPDATE stopdate,VISIT
8 visit ,DOV dov FROM TABLE25 WHERE serial_no = t_no)
9 loop
10 str:=str||' '||x.serial_no||' '||x.label||' '||x.startdate||' '||x.stopdate||' '||x.visit||
11 ' '||x.dov;
12 end loop;
13 return str;
14 end;
15 /
Function created.

SQL> select distinct get_tran(serial_no)"result" from table25;


And this the output I got it


--------------------------------------------------------------------------------
1 A 06-SEP-06 08-OCT-06 V1 5-SEP-2006 1 B 09-SEP-06 14-SEP-06 V2 9-SEP-2006
2 A 10-SEP-06 14-SEP-06 V1 9-SEP-2006 2 B 07-SEP-06 10-SEP-06 V2 9-SEP-2006



But I need the following output as

1 A 06-SEP-06 08-OCT-06 V1 5-SEP-2006 1 B 09-SEP-06 14-SEP-06 V2 9-SEP-2006
1 B 09-SEP-06 14-SEP-06 V2 9-SEP-2006 2 A 10-SEP-06 14-SEP-06 V1 9-SEP-2006
2 B 07-SEP-06 10-SEP-06 V2 9-SEP-2006


for that I need to use denserank function.ACTUALLY I DONT HAVE IDEA ABOUT denserank function.

Please help me out solve this problem

Thanks & regards,
Arunprabhu



Re: How to use denserank function [message #199961 is a reply to message #199956] Fri, 27 October 2006 03:15 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's what manuals are for.

[EDIT] Is it necessary to create a function? Couldn't it be done in SQL? Could you provide a CREATE TABLE and INSERT sample data statements so that people may play around?

[Updated on: Fri, 27 October 2006 03:17]

Report message to a moderator

Re: How to use denserank function [message #199970 is a reply to message #199961] Fri, 27 October 2006 04:10 Go to previous message
arunprabhu29917
Messages: 15
Registered: September 2006
Location: Tamilnadu
Junior Member

create table table26(serial_no varchar(20),label varchar(10),startdate date,stopdate date,visit
varchar(10),dov date);
hi got it?
insert into table26 values('&serial_no','&label','&startdate','&stopdate','&visit','&dov');
Previous Topic: Requesting PL/SQL procedure or upgrade
Next Topic: Hi I am fresh to this ..plz help me
Goto Forum:
  


Current Time: Wed Dec 07 03:17:28 CST 2016

Total time taken to generate the page: 0.09855 seconds