View help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: View help

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    I have a table attribute which has following columns
    attribute_id, work_id, res_id, attr_start_date, attr_finish_date, attr_amount
    where attribute_id is Primary key of this table and work_id, res_id are FKs and work_id, res_id makes a unique key.
    There are many other columns but I am keeping it simple here.

    Here is how the data looks -

    attribute_id, work_id, res_id, attr_start_date, attr_finish_date, attr_amount

    1 1 2 10/10/01 10/18/01 10
    2 2 3 10/13/01 10/15/01 20
    3 3 3 10/09/01 10/20/01 11
    4 3 2 10/10/01 10/20/01 16

    I want to create a view which will give me results like this...
    I will just do it for 1 st row.

    attribute_id, work_id, res_id, date , amount

    1 1 2 10/10/01 10
    1 1 2 10/11/01 10
    1 1 2 10/12/01 10
    1 1 2 10/13/01 10
    1 1 2 10/14/01 10
    1 1 2 10/15/01 10
    1 1 2 10/16/01 10
    1 1 2 10/17/01 10
    1 1 2 10/18/01 10

    So in the view I want to see the key columns and amount repeated for date between attr_start_date and attr_finish_date
    (inclusive).

    How do I write this view.. I know this can be done with stored procedure with while loop and/or cursor. But I am interested in doing this as a view rather than PL?SQL block. IS this possible in a view ?
    We use Oracle 8.1.7 here.

    Can you have a cursor or while loop in Case statement or in creating a view ?

    Is this possible by doing joins/ subquery ?

    Thanks
    Sonali



    Sonali

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    Hey, how serious are you @ this ?

    How much is going to be the difference between the start_date and finish_date values in your attr table records ? If not too much, read this :

    Create a table (say XYZ) with a numeric column (say I). Assuming that the difference in the dates will not be more than 30 days, put 30 rows in this XYZ tables with values 1 to 30.

    And then you are all set to fire the following query to get the required output :

    select attr_id,work_id,res_id,
    attr_start_date+i curr_date,attr_amount
    from attr a,xyz b
    where attr_start_date+i<=attr_finish_date
    order by 1,2,3,4;

    (Don't laugh...just , I'm sure someone can post a better solution or even you yourself can engineer one. And I remember a similar issue having gotten discussed before in this forum...)
    svk

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    Thanks a lot, the idea worked..
    Sonali

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