Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Unanswered: Create continuous view

    Hi

    I want to create view that contain the days from 1-1-2007 to (current day),
    so I can build a form on this view and make calculation for everyday on (post query)

    thanx in advance

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You are not giving much information, but something like

    select.....
    where the_date_column >= trunc(sysdate,'YEAR');
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Is the view the same for yesterday, today, & tomorrow?
    What is the business reason for having such as view?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What you need is a method for generating (rather than just selecting) data. There are a number of techniques for generating rows. Here are 2 of the simplest:
    Code:
    SQL> select level from dual connect by level <= 10;
    
         LEVEL
    ----------
             1
             2
             3
             4
             5
             6
             7
             8
             9
            10
    
    10 rows selected.
    That only works in Oracle 10G and above I believe. If that doesn't work for you you can always borrow a "big enough" table or view like this:
    Code:
    SQL> select rownum from all_objects where rownum <= 10;
    
        ROWNUM
    ----------
             1
             2
             3
             4
             5
             6
             7
             8
             9
            10
    
    10 rows selected.
    (all_objects is usually big enough!)

    Now all you need to do is convert those numbers into dates like this:
    Code:
    SQL> select date '2007-01-01' + level - 1
      2  from dual
      3  connect by level <= trunc(sysdate) - date '2007-01-01' + 1;
    
    DATE'2007-0
    -----------
    01-JAN-2007
    02-JAN-2007
    03-JAN-2007
    04-JAN-2007
    05-JAN-2007
    06-JAN-2007
    07-JAN-2007
    ...
    26-AUG-2007
    27-AUG-2007
    28-AUG-2007
    
    240 rows selected.
    There is a good guide to all the commonly known techniques for generating rows like these in this SQL Snippets article.

  5. #5
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137
    thanx alot

Posting Permissions

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