Home » SQL & PL/SQL » SQL & PL/SQL » seeking help for desired output
seeking help for desired output [message #267774] Fri, 14 September 2007 15:33 Go to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member
Hi Guys!

Could you please help with the following ?



--create test table

create table test1 (num  number);

--populate test table

begin
  for i IN 1..10 LOOP
  insert into test1 (num) values (i);
   end loop;
   commit;
end;
/

select * from test1;



--> Try to get this way but could not acheive desired results.


declare
    TYPE num_t IS TABLE OF test1.NUM%TYPE INDEX BY PLS_INTEGER;
    l_num num_t;
    start_num  test1.NUM%TYPE;
    end_num    test1.NUM%TYPE;
BEGIN


  select num bulk collect into l_num
    FROM test1
    order by num;
  dbms_output.put_line('Start_num' ||'   '||'End_num');

  FOR i IN 1..l_num.COUNT
  LOOP
 
   start_num   := l_num(i);
   end_num     := l_num(i) + 4;
 
   dbms_output.put_line(start_num ||'             '|| end_num );
 
   --  GEN_FLAT_FILE( start_num,end_num);
 
    
  END LOOP;
END;

Start_num   End_num
1             6
2             7
3             8
4             9
5             10
6             11
7             12
8             13
9             14
10            15

PL/SQL procedure successfully completed.



-----Desired output should show 

Start_num   End_num
1             5
6             10
11            15
16            20

...and so on adding 5 to start num and run till through all remaining num values.




Thanking you,


Re: seeking help for desired output [message #267775 is a reply to message #267774] Fri, 14 September 2007 15:54 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Try with this..
declare
    TYPE num_t IS TABLE OF test1.NUM%TYPE INDEX BY PLS_INTEGER;
    l_num num_t;
    start_num  test1.NUM%TYPE;
    end_num    test1.NUM%TYPE;
    last_num   test1.NUM%TYPE;
BEGIN


  select num bulk collect into l_num
    FROM test1
    order by num;
  dbms_output.put_line('Start_num' ||'   '||'End_num');

  FOR i IN 1..l_num.COUNT
  LOOP
   if mod(i,5)=0 then
   start_num   := l_num(i)-4;
   end_num     := l_num(i);

   dbms_output.put_line(start_num ||'             '|| end_num );
   end if;
   last_num := l_num(i);
   --  GEN_FLAT_FILE( start_num,end_num);
  END LOOP;
  if mod(last_num,5) != 0 then
  dbms_output.put_line(floor(last_num/5)*5+1 ||'             '|| last_num );
  end if;
END;


Cheers
Soumen
Re: seeking help for desired output [message #267777 is a reply to message #267775] Fri, 14 September 2007 16:45 Go to previous messageGo to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member

Thanks a lot Soumen!

You made my weekend!

Thanking you again.
Re: seeking help for desired output [message #267791 is a reply to message #267774] Sat, 15 September 2007 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends of what you finally want.
If it is just the output you can do:
SQL> select 1+5*(level-1) start_num, 5*level end_num 
  2  from dual
  3  connect by level <= 10
  4  /
 START_NUM    END_NUM
---------- ----------
         1          5
         6         10
        11         15
        16         20
        21         25
        26         30
        31         35
        36         40
        41         45
        46         50

10 rows selected.

Regards
Michel
Re: seeking help for desired output [message #267857 is a reply to message #267791] Sat, 15 September 2007 14:50 Go to previous message
ashwin_tampa
Messages: 40
Registered: October 2005
Member

Realy excellent solution through SQL, Michael.

Appreciated!
Previous Topic: How to take History for particular table? (merged)
Next Topic: complex analyzing for this code
Goto Forum:
  


Current Time: Thu Dec 08 18:45:31 CST 2016

Total time taken to generate the page: 0.05318 seconds