This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Mar 4, 2013 5:47 AM by Purvesh K RSS

Pl Sql Block Logic to get dates in ASC

Shoaib581 Newbie
Currently Being Moderated
Hi All,

We Have,

TABLE:HEAD (CONTRACT VARCHAR2(200), STARTDATE DATE, EXPIRYDATE DATE)

DATA:
('A',TO_DATE('01-01-12','dd-mm-yy'),TO_DATE('31-12-12','dd-mm-yy'));
('B',TO_DATE('01-02-12','dd-mm-yy'),TO_DATE('31-12-12','dd-mm-yy'));
('C',TO_DATE('01-03-12','dd-mm-yy'),TO_DATE('31-12-12','dd-mm-yy'));
('D',TO_DATE('01-02-12','dd-mm-yy'),TO_DATE('31-12-12','dd-mm-yy'));

QUERY:

DECLARE

ln_level NUMBER;
BEGIN
LN_LEVEL := 3;
FOR H IN (SELECT DISTINCT STARTDATE LD_CSDATE,EXPIRYDATE LD_EXPDATE FROM
HEAD)
LOOP
FOR i IN
( WITH DATES AS
(SELECT TO_DATE(H.LD_CSDATE, 'DD-MM-YYYY') STARTDATE ,
to_date(H.ld_expdate+1, 'DD-MM-YYYY') enddate
FROM dual
)
SELECT TO_CHAR(add_months(startdate, LEVEL *LN_LEVEL-LN_LEVEL), 'DD-MM-YY') sd,
to_date(add_months(TO_CHAR(add_months(startdate, LEVEL*LN_LEVEL-LN_LEVEL), 'DD-MON-YY'),LN_LEVEL),'DD-MM-YY')-1 endd
FROM dates
CONNECT BY LEVEL <= months_between(enddate, startdate)/LN_LEVEL
)
LOOP

FOR J IN (SELECT CONTRACT FROM HEAD WHERE STARTDATE = TO_DATE(H.LD_CSDATE,'DD-MM-YY')
AND (STARTDATE BETWEEN TO_DATE(I.SD,'DD-MM-YY') AND TO_DATE(I.ENDD,'DD-MM-YY')
OR STARTDATE < TO_DATE(I.SD,'DD-MM-YY'))
)
LOOP
DBMS_OUTPUT.PUT_LINE(I.SD||'-'||I.ENDD||'-'||J.CONTRACT);
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE(chr(10));
END LOOP;
END;



OUTPUT:
01-01-12-31-MAR-12-A
01-04-12-30-JUN-12-A
01-07-12-30-SEP-12-A
01-10-12-31-DEC-12-A

01-02-12-30-APR-12-B
01-02-12-30-APR-12-D
01-05-12-31-JUL-12-B
01-05-12-31-JUL-12-D
01-08-12-31-OCT-12-B
01-08-12-31-OCT-12-D

01-03-12-31-MAY-12-C
01-06-12-31-AUG-12-C
01-09-12-30-NOV-12-C

Can you please alter my query to get the below result, as I am not able to understand where to start to get the desired output.

DESIRED OUPTUT:
01-01-12-31-MAR-12-A

01-02-12-30-APR-12-B
01-02-12-30-APR-12-D

01-03-12-31-MAY-12-C

01-04-12-30-JUN-12-A

01-05-12-31-JUL-12-B
01-05-12-31-JUL-12-D

01-06-12-31-AUG-12-C

01-07-12-30-SEP-12-A

01-08-12-31-OCT-12-B
01-08-12-31-OCT-12-D

01-09-12-30-NOV-12-C



Thanks In Advance,
Shoaib

Edited by: Shoaib581 on Mar 4, 2013 3:34 AM
  • 1. Re: Pl Sql Block Logic to get dates in ASC
    Lakshmipathi Journeyer
    Currently Being Moderated
    Hi,

    You need to use
    SELECT DISTINCT STARTDATE LD_CSDATE,EXPIRYDATE LD_EXPDATE FROM HEAD order by 1 /Add order by startdate in this query.

    Regards,
    Lakshmipathi.
  • 2. Re: Pl Sql Block Logic to get dates in ASC
    Shoaib581 Newbie
    Currently Being Moderated
    Hi Lakshmipathi,

    The issue is not selecting the dates in Asc order but to show them in Asc order which should be handled in the Loops(i guess, which i am unable to handle)

    Please check the desired output.

    Regards,
    Shoaib

    Edited by: Shoaib581 on Mar 4, 2013 3:52 AM
  • 3. Re: Pl Sql Block Logic to get dates in ASC
    Lakshmipathi Journeyer
    Currently Being Moderated
    Hi,

    What ever the record selected by the for loop, the inner loops will be executed for that record, so the order by clause need to be there inorder to select the records in startdate ASC order.

    If record 1 is executed by the for loop, the inner loops will perform action on that record.So, how can we ask the inner loops to perform action on Record 2 and give that output first as they don't know any other record at that time?

    Regards,
    Lakshmipathi
  • 4. Re: Pl Sql Block Logic to get dates in ASC
    Shoaib581 Newbie
    Currently Being Moderated
    Hi Lakshmipathi,

    Exactly that is what confusing me. Even though we mention order by startdate asc, it will perform the same action(as in output) how do i ask the inner loops to perform the action to get the desired output :(

    I know it is very very Challenging task, but i have to do it and that is why i have posted this on the forum so that some one can help me with this :)

    Thanks,
    Shoaib

    Edited by: Shoaib581 on Mar 4, 2013 4:03 AM
  • 5. Re: Pl Sql Block Logic to get dates in ASC
    jeneesh Guru
    Currently Being Moderated
    Is this you want?
    select h.contract,add_months(h.startdate,(rn-1)*3) startdate,
           add_months(h.startdate,rn*3)-1 expirydate
    from head h,
         (
         select rownum rn
         from dual
         connect by level <= 3
         ) rn
    order by 2,3,1 ;
    
    CONTRACT STARTDATE EXPIRYDATE
    -------- --------- ----------
    A        01-JAN-12 31-MAR-12  
    B        01-FEB-12 30-APR-12  
    D        01-FEB-12 30-APR-12  
    C        01-MAR-12 31-MAY-12  
    A        01-APR-12 30-JUN-12  
    B        01-MAY-12 31-JUL-12  
    D        01-MAY-12 31-JUL-12  
    C        01-JUN-12 31-AUG-12  
    A        01-JUL-12 30-SEP-12  
    B        01-AUG-12 31-OCT-12  
    D        01-AUG-12 31-OCT-12  
    C        01-SEP-12 30-NOV-12  
    
     12 rows selected
  • 6. Re: Pl Sql Block Logic to get dates in ASC
    Purvesh K Guru
    Currently Being Moderated
    Perhaps, you do not need loops at all.

    See the solution using pure SQL, which will scale better than your PL/SQL counterpart.
    alter session set nls_date_format = 'DD-Mon-YYYY';
    
    create table test_table (contract char(1), st_date date, ex_date date);
    
    insert into test_table values ('A', to_date('01-Jan-2013', 'DD-Mon-YYYY'), to_date('31-Dec-2013', 'DD-Mon-YYYY'));
    insert into test_table values ('B', to_date('01-Feb-2013', 'DD-Mon-YYYY'), to_date('31-Dec-2013', 'DD-Mon-YYYY'));
    insert into test_table values ('C', to_date('01-Mar-2013', 'DD-Mon-YYYY'), to_date('31-Dec-2013', 'DD-Mon-YYYY'));
    insert into test_table values ('D', to_date('01-Feb-2013', 'DD-Mon-YYYY'), to_date('31-Dec-2013', 'DD-Mon-YYYY'));
    
    with data as
    (
      select contract, add_months(st_date, (level -1) * 3) st_date, last_day(add_months(st_date, level * 2)) exp_date
        from test_table
      connect by level <= 4
             and prior contract = contract
             and prior dbms_random.value is not null
    )
    select contract, to_char(st_date, 'DD-Mon-YYYY') st_date, to_char(exp_date, 'DD-Mon-YYYY') exp_date
      from data
     order by st_date, exp_date, contract;
    
    CONTRACT ST_DATE              EXP_DATE           
    -------- -------------------- --------------------
    A        01-Apr-2013          31-May-2013          
    B        01-Aug-2013          31-Aug-2013          
    D        01-Aug-2013          31-Aug-2013          
    C        01-Dec-2013          30-Nov-2013          
    B        01-Feb-2013          30-Apr-2013          
    D        01-Feb-2013          30-Apr-2013          
    A        01-Jan-2013          31-Mar-2013          
    A        01-Jul-2013          31-Jul-2013          
    C        01-Jun-2013          31-Jul-2013          
    C        01-Mar-2013          31-May-2013          
    B        01-May-2013          30-Jun-2013          
    D        01-May-2013          30-Jun-2013          
    B        01-Nov-2013          31-Oct-2013          
    D        01-Nov-2013          31-Oct-2013          
    A        01-Oct-2013          30-Sep-2013          
    C        01-Sep-2013          30-Sep-2013          
    
     16 rows selected 
    Edited by: Purvesh K on Mar 4, 2013 5:57 PM
    Ordered on Contract as well to match results posted by OP.
  • 7. Re: Pl Sql Block Logic to get dates in ASC
    980503 Newbie
    Currently Being Moderated
    Purvesh K wrote:
    Perhaps, you do not need loops at all.

    See the solution using pure SQL, which will scale better than your PL/SQL counterpart.
    What if OP want to pass this data to front end?He is forced to use procedure or function and hence loop
  • 8. Re: Pl Sql Block Logic to get dates in ASC
    Purvesh K Guru
    Currently Being Moderated
    Oracle_Monkey wrote:
    Purvesh K wrote:
    Perhaps, you do not need loops at all.

    See the solution using pure SQL, which will scale better than your PL/SQL counterpart.
    What if OP want to pass this data to front end?He is forced to use procedure or function and hence loop
    How exactly using a Loop can data be passed to a Front End system? Would you like to demonstrate how you pass data to Front End system using Loops (of course in a separate thread of yours)?

    OP does not mention it, so a discussion over this will be a waste. However, if there be need, you always have Ref cursors for your assistance; All you need to do is just populate them and pass on the Front End system. I hope this answers your question.
  • 9. Re: Pl Sql Block Logic to get dates in ASC
    Shoaib581 Newbie
    Currently Being Moderated
    Hi Jeenesh,

    Thanks for your awesome query which has solved half of my problem as i thought getting this was the most tricky part which you handled so easily :)

    I will work on the remaining requirment and will get back to you(Forum) if i have any queries.

    Thanks alot,
    Shoaib
  • 10. Re: Pl Sql Block Logic to get dates in ASC
    Shoaib581 Newbie
    Currently Being Moderated
    Thanks Purvesh,


    Your query is my second option if i cant get what i want from jeneesh's query i will go for yours. As Oracle M told i do want to get my output into excel(Download from Oracle Application Express).

    Thanks alot,
    Shoaib
  • 11. Re: Pl Sql Block Logic to get dates in ASC
    980503 Newbie
    Currently Being Moderated
    I know ref cursors will be required to return the data set .But to implement ref cursors i would need to use procedures.
    I am just clarifying if we want to return this data set i would need to use pl/sql .pure sql will not work.
    and for other conditions Sql is more than enough and better solution
  • 12. Re: Pl Sql Block Logic to get dates in ASC
    BluShadow Guru Moderator
    Currently Being Moderated
    Oracle_Monkey wrote:
    I know ref cursors will be required to return the data set .But to implement ref cursors i would need to use procedures.
    I am just clarifying if we want to return this data set i would need to use pl/sql .pure sql will not work.
    and for other conditions Sql is more than enough and better solution
    Yes, but the procedure would just be a wrapper for creating the ref cursor. There is no need to use PL/SQL loops as you indicated.

    And please do not consider that ref cursors return data sets, they don't. There is no set of data in a ref cursor, it's just a pointer to a query that has yet to return any data.
  • 13. Re: Pl Sql Block Logic to get dates in ASC
    jeneesh Guru
    Currently Being Moderated
    Oracle_Monkey wrote:
    I know ref cursors will be required to return the data set .But to implement ref cursors i would need to use procedures.
    I am just clarifying if we want to return this data set i would need to use pl/sql .pure sql will not work.
    and for other conditions Sql is more than enough and better solution
    No - He is not talking about not to use PL/SQL..

    Wherever procedures are required you have to use that..

    The thing is that - Say inside procedure : You should maximize SQL.. Use PL/SQL constructs like LOOP only when it is necessary..

    And as shown in previous posts - LOOPs are very rarely required as SQL is very powerful with analytic functions, hierarchical queries, MODEL.....
    And, if LOOP is mandatory, you could use BULK COLLECT and do your processing..
  • 14. Re: Pl Sql Block Logic to get dates in ASC
    Rahul_India Journeyer
    Currently Being Moderated
    >
    And please do not consider that ref cursors return data sets, they don't. There is no set of data in a ref cursor, it's just a pointer to a query that has yet to return any data.
    Blu quite aware of you ref cursors and "Pl/Sql belongs to Ada family of language" (something like that) rants ;)
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points