Select statement that return all the month days
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Select statement that return all the month days

  1. #1
    Join Date
    Sep 2003
    Location
    Detroit, MI
    Posts
    41

    Select statement that return all the month days

    Dear Sir,

    How can I write Select statement that return all the days in the current month or year... For example the results can be like:

    1-1-05
    2-1-05
    3-1-05
    4-1-05
    ...
    ...
    ...

    Thank you in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    select trunc(sysdate,'MON')+rownum-1 from all_objects where rownum <= 28

    See here: http://www.dbasupport.com/forums/sho...threadid=44529 for other methods of generating rows.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Maybe generalise that with ...
    Code:
    select trunc(sysdate,'MON')+rownum-1
    from   all_objects
    where  rownum <= Add_Months(trunc(sysdate,'MON'),1)-trunc(sysdate,'MON')
    btw, the all_objects view performs dismally on my 10g system compared to dba_objects or user_objects -- tsh.

    I favour a nice big table of integers for this sort of thing.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    Maybe generalise that with ...
    Sure, but I like to leave the original poster with something to do . . .

  5. #5
    Join Date
    Sep 2003
    Location
    Detroit, MI
    Posts
    41

    Thumbs up Although we can use this

    Thank you sirs for your fast help... Anyhow maybe we can although use the following statement for a year:

    select to_char(trunc(sysdate,'YY') - 1 + rownum, 'dd-mm-yy') from
    (select rownum from all_catalog where rownum < 366);

    Regards,

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Why are you using to_char(), and why use a two-digit year picture? How about making it properly responsive to leap years?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Sep 2003
    Location
    Detroit, MI
    Posts
    41

    Question You're right...

    Yes sir, definitely you're right. When we need a statement like this, absolutely we need it fully dynamic.
    But I need you opinion if we want to use this statement in generating dynamic reports. I mean that I want to join this statement with another table which has column (DAY_NO) and it's values is enumerators: (1,2,3,4,5,6,7) (i.e. Monday is 1, Tuesday is 2 etc...).
    So the join will be by getting the current statement day of the week and comparing it with the table DAY_NO column -The statement could be like this-:
    ********************************************************************
    SELECT trunc(sysdate,'MON')+rownum-1,
    shf.time_in,
    shf.time_out,
    shf.day_no,
    FROM
    all_objects aobj,
    shifts shf
    WHERE
    day_no = to_char(trunc(sysdate,'YY') - 1 + rownum, 'D')
    and
    rownum <= Add_Months(trunc(sysdate,'MON'),1)-trunc(sysdate,'MON')
    *********************************************************************

    Do think that if put the values of the statement which return the days of month in a table is better than using it every time?
    And do you have any suggestions in order to make tuning to the previous example of SQL statement (maybe materialized view or something like this...)?

    Thank you in advance.

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I suspect that will be very slow (and maybe blow your temp TS) - you are making a Cartesian product of all_objects and your table. The optimizer might be clever . . . . but if not, try something like:
    Code:
    SELECT my_date, 
    shf.time_in,
    shf.time_out,
    shf.day_no,
    FROM
    shifts shf,
    (select trunc(sysdate,'MON')+rownum-1 my_date
     from   user_objects
     where  rownum <= Add_Months(trunc(sysdate,'MON'),1)-trunc(sysdate,'MON'))
    WHERE
    day_no = to_char(my_date, 'D')
    I think it is also more understandable.

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    With the "rownum <= ..." clause in the in-line view, is that sufficient to prevent view merging in any circumstances you think? I'm guessing that it would be, but thoughts are welcome of course.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Weeeellllll . . . . the attempt by Lowaiy with a rownum condition on the "join" of the two tables is definitely not what is wanted - it won't give the right answer (as far I understand the question). I don't see how you can write it correctly like that, because you can't refer to alias.rownum

    I've just tried:
    Code:
    select count(*)
    from   my_table r,
    (select trunc(sysdate,'MON')+rownum-1 my_date
     from   user_objects
     where  rownum <= Add_Months(trunc(sysdate,'MON'),1)-trunc(sysdate,'MON'))
    where  mod(myTablePK,10) = to_char(my_date, 'D')
    With tables from 20 to 2'000'000 rows. They all work as I'd like/expect - the only difference for the 20-row table being which set is the driver in the hash join.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width