1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Sql

Discussion in 'SQL PL/SQL' started by sp111347, Nov 28, 2012.

  1. sp111347

    sp111347 Guest

    Dear Experts,

    I writing a one query,that query have two parameter 1.start_date
    2 end_date

    Example :
    select to_date:)start_date,'dd-mon-yyyy')-- + rownum -1 a
    from all_objects
    Where Rownum <=
    to_date:)end_date,'dd-mon-yyyy')-to_date:)start_date,'dd-mon-yyyy')+1;

    Prameter : 01-jan-2012

    :05-jan-2012

    O/p

    Columnwise

    01-jan-2012 02-jan-2012 03-jan-2012 04-jan-2012 05-jan-2012

    each date print in separate column...

    How to frame query..

    Kindly advice me..

    Thanks in advance.....
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Pivot query in Oracle 11g might help you. somthing like below (not tested)

    Code (SQL):
    WITH rec AS (SELECT to_date('01012012','DDMMYYYY') dt
                  FROM dual),
     rec1 AS ( SELECT dt+level-1 dt , level lvl
                    FROM rec
                   CONNECT BY level <=5)
    SELECT *
    FROM  rec1                
    pivot
    (MAX(dt) FOR lv IN (1,2,3,4,5))
     
  4. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    @sp111347

    And please use code tags when you write a script. It makes your code much more readable for those who try to read and understand your problem.


    Regards,
    Dariyoosh
     
  5. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    And if you're using oracle 11gR2 there is a much easier solution by using LISTAGG

    Code (Text):

    SELECT LISTAGG(TO_DATE('01-JAN-2012','dd-mon-yyyy') + ROWNUM -1, ' ')
        WITHIN GROUP (ORDER BY ROWNUM)
    FROM all_objects
    WHERE ROWNUM <= TO_DATE('10-JAN-2012','dd-mon-yyyy') -
                    TO_DATE('01-JAN-2012','dd-mon-yyyy') + 1;



    LISTAGG(TO_DATE('01-JAN-2012','DD-MON-YYYY')+ROWNUM-1,'')WITHINGROUP(ORDERBYROWNUM)
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    01-JAN-12 02-JAN-12 03-JAN-12 04-JAN-12 05-JAN-12 06-JAN-12 07-JAN-12 08-JAN-12 09-JAN-12 10-JAN-12

    SQL>

     

    Regards,
    Dariyoosh
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    LISTAGG will not provide the desired output as it places all values in a single column; PIVOT is the desired solution:

    Code (SQL):
    SQL> variable start_date varchar2(12)
    SQL> variable end_date varchar2(12)
    SQL>
    SQL> BEGIN
      2     :start_date:='01-jan-2012';
      3     :end_date:='05-jan-2012';
      4  END;
      5  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT to_date(:start_date,'dd-mon-yyyy') + rownum -1 a
      2  FROM all_objects
      3  WHERE Rownum <=
      4  to_date(:end_date,'dd-mon-yyyy')-to_date(:start_date,'dd-mon-yyyy')+1;
     
    A
    ---------
    01-JAN-12
    02-JAN-12
    03-JAN-12
    04-JAN-12
    05-JAN-12
     
    SQL>
    SQL> WITH pvt AS(
      2  SELECT to_date(:start_date,'dd-mon-yyyy') + rownum -1 a, rownum rn
      3  FROM all_objects
      4  WHERE Rownum <=
      5  to_date(:end_date,'dd-mon-yyyy')-to_date(:start_date,'dd-mon-yyyy')+1
      6  )
      7  SELECT *
      8  FROM pvt
      9  pivot
     10  (MAX(a) FOR rn IN (1,2,3,4,5));
     
    1         2         3         4         5
    --------- --------- --------- --------- ---------
    01-JAN-12 02-JAN-12 03-JAN-12 04-JAN-12 05-JAN-12
     
    SQL>
     
  7. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    That is true, but at the same time you can use for example LISTAGG with semicolon or pipe as delimiter. This will allow you to split this single column into several columns opening with an appropriate tool such as Excel.

    However, yes, your remark is pertinent if we consider a general solution requiring separated columns to be used in a query.


    Regards,
    Dariyoosh
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "Columnwise

    01-jan-2012 02-jan-2012 03-jan-2012 04-jan-2012 05-jan-2012

    each date print in separate column..."

    How much clearer does his intent need to be?
     
  9. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Ok, you don't need to get angry for that.
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    How did you arrive at the (incorrect) conclusion that I am angry? It was a simple quote, a simiple question more out of curiosity based on your response (that you've edited since I last saw it) which did include this:

    "but at the same time it depends how he treats the output (which as I believe was not clearly specified at the OP)."

    I was simply curious as to how you could have missed that statement I quoted.


    David Fitzjarrell
     
  11. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Well, clearly it was a synchronization problem between messages we posted!
    In fact I wrote " ... which as I believe was not clearly specified at the OP ..." but once I posted I checked once again the OP and I saw that he said in fact "... each date print in separate column... " So I edited the post and I wrote in my edited post "... your remark is pertinent ..." precisely to admit that your solution was the correct and good one.

    If there was a misunderstanding I apologize for that. It was my lack of attention :( and thanks for your correction. :hurray

    Regards,
    Dariyoosh
     
  12. Hamza Bukhari

    Hamza Bukhari Active Member

    Messages:
    25
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Pakistan
    (@ dariyoosh)

    chill Man!!
    its His Old Habit :D