Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: How can I update the area automatically?

    Hi,
    I've a view LOG_AREA:
    CODE......NAME......ID........AREA
    001.........Tom.........1...........200
    001.........Tom.........2...........300
    124.........John........5...........1000
    124.........Tom.........2...........2000
    124.........Sam.........3...........300
    004.........Sam.........5...........600


    The area of view LOG_AREA change every day.

    for example if this view the day after has this values:

    CODE......NAME......ID........AREA
    001.........Tom.........1..........300
    001.........Tom.........2..........800
    124.........John........5..........300
    124.........Tom.........2..........2000
    124.........Sam.........3..........300
    004.........Sam.........5..........800

    I'd like to get a table "AUDIT_AREA" which does the difference between the current area and the previous area:


    CODE......NAME......ID........AREA_OLD...AREA_NEW. ...DIFF
    001.........Tom.........1...........200........... ..300...........100
    001.........Tom.........2...........300........... ..800...........500
    124.........John........5...........1000.......... .300.........-700
    124..........Tom........2...........2000.......... .2000...........0
    124..........Sam........3...........300........... ..300.............0
    004..........Sam........5..........600............ 800.............200

    How can I create a statement sql (or procedure) that does this update of area?

    Thanks
    Raf

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How can I update the area automatically?

    Originally posted by raf
    Hi,
    I've a view LOG_AREA:
    CODE......NAME......ID........AREA
    001.........Tom.........1...........200
    001.........Tom.........2...........300
    124.........John........5...........1000
    124.........Tom.........2...........2000
    124.........Sam.........3...........300
    004.........Sam.........5...........600


    The area of view LOG_AREA change every day.

    for example if this view the day after has this values:

    CODE......NAME......ID........AREA
    001.........Tom.........1..........300
    001.........Tom.........2..........800
    124.........John........5..........300
    124.........Tom.........2..........2000
    124.........Sam.........3..........300
    004.........Sam.........5..........800

    I'd like to get a table "AUDIT_AREA" which does the difference between the current area and the previous area:


    CODE......NAME......ID........AREA_OLD...AREA_NEW. ...DIFF
    001.........Tom.........1...........200........... ..300...........100
    001.........Tom.........2...........300........... ..800...........500
    124.........John........5...........1000.......... .300.........-700
    124..........Tom........2...........2000.......... .2000...........0
    124..........Sam........3...........300........... ..300.............0
    004..........Sam........5..........600............ 800.............200

    How can I create a statement sql (or procedure) that does this update of area?

    Thanks
    Raf
    Is it possible to derive yesterday's values today? e.g. if view is summing records and if by ignoring today's records you get yesterday's values, then it presents no problem.

    On the other hand, if yesteday's values have been overwritten or deleted, then there is no way to do it - other than save a copy of the view to a table each day ready for use tomorrow.

  3. #3
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    write an "after update" trigger that updates your audit table,when any value in the original table is updated.

    you can use the ld and :new variables to o get the old and new values of the updated row.

    I think ur problem can be solved by this .

    give a try...

    mickykt

  4. #4
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    sorry I ment use the : old and : new variables to get the values before and after updates.

    ....mickykt

Posting Permissions

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