Home » SQL & PL/SQL » SQL & PL/SQL » Other Way on this (10g)
Other Way on this [message #351390] Tue, 30 September 2008 10:36 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
I have a part of a code where I have to produce the months based on a quarter. A string of quarter in format:


p_quarter := 'Q1 (2008)';



Then I just parse the number after 'Q' then based on that I determine what range of numbers (which is the months on that quarter of the year). e.g. Q1 - Jan-Mar (1-3), Q2 - Apr-Jun(4-6). Here's the current code I have:



                v_qrtr := trim( substr(p_quarter, 2, 1) );
                v_year := trim( substr(p_quarter, 5, 4) );
                
                if v_qrtr = 1 then
                    
                    for i in 1..3 loop
                        a_q_date.extend;
                        a_q_date( a_q_date.last ) := to_date( '01' || lpad(i, 2, '0') || v_year, 'DDMMYYYY');  
                    end loop;
                
                elsif v_qrtr = 2 then
                    
                    for i in 4..6 loop
                        a_q_date.extend;
                        a_q_date( a_q_date.last ) := to_date( '01' || lpad(i, 2, '0') || v_year, 'DDMMYYYY'); 
                    end loop;
                    
                elsif v_qrtr = 3 then
                    
                    for i in 7..9 loop
                        a_q_date.extend;
                        a_q_date( a_q_date.last ) := to_date( '01' || lpad(i, 2, '0') || v_year, 'DDMMYYYY'); 
                    end loop;
                    
                elsif v_qrtr = 4 then
                    
                    for i in 10..12 loop
                        a_q_date.extend;                    
                        a_q_date( a_q_date.last ) := to_date( '01' || lpad(i, 2, '0') || v_year, 'DDMMYYYY');                         
                    end loop;
                    
                end if;



a_q_date is just an array where I store the months. Is there a shorter/easy way of doing this? Just curious for others idea. Thanks!

Re: Other Way on this [message #351391 is a reply to message #351390] Tue, 30 September 2008 10:45 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
FOR i IN 1*v_qrtr..3*v_qrtr LOOP .....
Re: Other Way on this [message #351392 is a reply to message #351391] Tue, 30 September 2008 10:49 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi, like this?

       FOR i IN 1*v_qrtr..3*v_qrtr LOOP 


       for i in 1*2..3*2 loop

       for i in 2..6 loop --shouldn't this be 4..6?
  


Or am I doing it wrong?
Re: Other Way on this [message #351393 is a reply to message #351390] Tue, 30 September 2008 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"for i in 3*(q-1)..3*q-1 loop"

date = "add_months(trunc(to_date(y,'yyyy'),'year'),i)"

For information, "lpad(i, 2, '0')" = "to_char(i,'fm00')"

Regards
Michel
Re: Other Way on this [message #351394 is a reply to message #351393] Tue, 30 September 2008 11:03 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Michel Cadot wrote on Tue, 30 September 2008 11:50
"for i in 3*(q-1)..3*q-1 loop"

date = "add_months(trunc(to_date(y,'yyyy'),'year'),i)"

For information, "lpad(i, 2, '0')" = "to_char(i,'fm00')"

Regards
Michel




Thank you much =) new code:


               for i in ( 3 * (v_qrtr-1) )..( (3 * v_qrtr) - 1 ) loop
                
                    a_q_date.extend;
                    a_q_date( a_q_date.last ) := add_months(trunc(to_date( v_year,'yyyy'),'year'), i);
                
                end loop;



The parenthesis should be in same order as it would have different result. Thanks also on the to_char, forgot that one.
Previous Topic: help in SQL
Next Topic: Create Table
Goto Forum:
  


Current Time: Fri Dec 02 16:46:17 CST 2016

Total time taken to generate the page: 0.22543 seconds