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

    Unanswered: update area with.......

    hi,
    I've a view "SPACE" with this columns:

    COD_ID...........VARCHAR2(32)
    NAME.............VARCHAR2(32)
    DESCRIPTION......VARCHAR2(64)
    AREA.............NUMBER

    AREA in this view change every day.
    I'd like to create one procedures that takes the data from this view , fills a table it and does the difference between the current area (sysdate) and the area of the previous day.

    FOR EXAMPLE:

    VIEW SPACE:

    COD_ID.......NAME.........DESCRIPTION........AREA. ......DATE_TODAY
    101..........Jo............room.............1200.. .......01/07/03
    105..........Tom...........building.........1900.. .......01/07/03


    I'd like to create this table:

    cod_id..name..description...today....area_old...ar ea_new..diff_area
    101.......Jo.....room.......02/07/03...1200......2000.........800
    105......Tom....building....02/07/03...1900......2000.........100


    How can I create this table that update automatically the area?

    I'm trying with trigger but it not run correctly

    Thanks
    Raf

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Try setting up an Oracle job (like crontab on unix) which you can set to run everyday at a set time and then get it to call a stored procedure which will compare and populate the two sets of data.

    Alan

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    I explain to you what I did in detail:

    We suppose that my view's name is log_area (COD_ID, NAME, DESCRIPTION, AREA)

    I created a table "audit_area" with this columns:
    COD_ID
    NAME
    DESCRIPTION
    AREA_OLD
    AREA_NEW
    DIFF_AREA

    I Filled the table "audit_area" with this code:
    insert into audit_area
    select cod_imm, name, description, 0 area, 0 area, 0 area
    from log_area;

    Now I tried to run the code sql that you have written:

    Update (select u.area_old u_area_old,
    u.area_new u_area_new,
    u.diff_area u_diff_area,
    l.area l_area
    from audit_area u, log_area l
    where u.cod_id = l.cod_id)
    set u_area_old = u_area_new,
    u_diff_area = u_area_new - l_area,
    u_area_new = l_area;

    but I get this error:
    ORA-01779: cannot modify a column which maps to a non key-preserved
    table

    What I wrong?
    How can I resolve this problem?

    Thanks
    Raf

Posting Permissions

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