Results 1 to 4 of 4

Thread: help

  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Unanswered: help

    I am new to oracle programming and I want to create a dynamic view , which is a combination of following tables

    Project Table

    Project F code
    DAA 0012
    DAB 0013
    DAC 0013

    Activity Table

    Activity SSD KFD PFD
    1A 12 12 12
    2A 13 13 13
    3A 14 14 14


    Activity Def Table

    Owner Activity name
    D SSD
    D KFD
    D PFD
    h SSD
    h KFD
    h PFD
    Right now this table has 100 records. In future there is chance it might increase to infinite number of rows. Each row I

    this Table has related value in the Activity Table. I want dynamic code in the View so that when ever this table is updated

    it should reflect in the results.



    The Project Info is coming from Projects Table,
    Activity and SSD,KFD,PFD values are coiming from Activity Table,
    SSD,KFD,PFD Columns Names are coming from Activity Def Table.Right now Activity Def table has 100 records. so 100 column names will appear in the results.This how the Result should look like-

    Project Activity SSD KFD PFD Activity SSD KFD PFD
    DAA 1T 12 12 12 2A 13 13 13
    DAB 1T 12 12 12 2A 13 13 13

    So on for 100 Activities for one particular Project.

    I want the values to be displayed as a cross tab. The rows in the Activity Def and Activity Tables should be displayed as

    columns in the Result. Each Activity Name in the Activity Table will have a record in the Activity Table.


    Thank You Very Much,
    gopi

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

    Re: help

    Views are always dynamic - if you change the data in base tables, the result returned by the view will also change. That is because a view is really just a stored query DEFINITION - it is not a stored query RESULT.

  3. #3
    Join Date
    Dec 2002
    Posts
    2
    But I wnat the Data to be displayed as a crosstab.
    Please look at the below given discription of tables.

    Activity

    PROJ NOT NULL VARCHAR2(16) (Foreign Key)
    ACTIVITY NOT NULL VARCHAR2(12) (Primary keY)
    ASD DATE
    AFD DATE
    PFD DATE
    DS VARCHAR2(80)

    Project

    PROJ NOT NULL VARCHAR2(16)(primary key)
    DS VARCHAR2(80)
    PC01 VARCHAR2(30)



    Activity Def Table

    Owner Activity name
    DS ASD
    DS AFD
    DS PFD

    The actvity name values in Activity Def Table are just names which will be used in the Crosstab as Columns Names and the related records will come from Activity Table.

    I am looking for a Dynamic View. so that whenever new records are entered into Activity Def Table the values should be displayed as Cloumns Names in the Results

    The Project Info is coming from Projects Table,
    Activity and ASD,AFD,PFD values are coiming from Activity Table,
    ASD,AFD,PFD columns Names are coming from Activity Def Table.Right now Activity Def table has 50 Activity Name records. so 50 column names will appear in the results( like a cross tab) -

    Proj Activity SSD KFD PFD Activity ASD AFD PFD Activity ASD AFD PFD
    DAA 1A 12 12 12 2A 13 13 13 3A 14 14 14
    DAB 1A 12 12 12 2A 13 13 13 3A 14 14 14
    So on for 50 Activities for one particular Project.

    Thank You Very Much,

    Gopi

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by reachgopi
    But I wnat the Data to be displayed as a crosstab.
    Please look at the below given discription of tables.

    Activity

    PROJ NOT NULL VARCHAR2(16) (Foreign Key)
    ACTIVITY NOT NULL VARCHAR2(12) (Primary keY)
    ASD DATE
    AFD DATE
    PFD DATE
    DS VARCHAR2(80)

    Project

    PROJ NOT NULL VARCHAR2(16)(primary key)
    DS VARCHAR2(80)
    PC01 VARCHAR2(30)



    Activity Def Table

    Owner Activity name
    DS ASD
    DS AFD
    DS PFD

    The actvity name values in Activity Def Table are just names which will be used in the Crosstab as Columns Names and the related records will come from Activity Table.

    I am looking for a Dynamic View. so that whenever new records are entered into Activity Def Table the values should be displayed as Cloumns Names in the Results

    The Project Info is coming from Projects Table,
    Activity and ASD,AFD,PFD values are coiming from Activity Table,
    ASD,AFD,PFD columns Names are coming from Activity Def Table.Right now Activity Def table has 50 Activity Name records. so 50 column names will appear in the results( like a cross tab) -

    Proj Activity SSD KFD PFD Activity ASD AFD PFD Activity ASD AFD PFD
    DAA 1A 12 12 12 2A 13 13 13 3A 14 14 14
    DAB 1A 12 12 12 2A 13 13 13 3A 14 14 14
    So on for 50 Activities for one particular Project.

    Thank You Very Much,

    Gopi
    Ah, no. A view has a fixed set of columns like a table.
    Maybe something like this would work:
    Create a trigger on ACTIVITY_DEF such that after any insert, update or delete the view definition is regenerated. Something along these lines:

    CREATE OR REPLACE TRIGGER activity_def_aiud
    AFTER INSERT OR UPDATE OR DELETE OF activity_def
    DECLARE
    v_job BINARY_INTEGER;
    BEGIN
    DBMS_JOB.SUBMIT( v_job, 'regenerate_view;' );
    END;

    The job will be submitted once the changes to the table have been committed. The procedure would then construct and run a 'CREATE OR REPLACE VIEW' statement like this:

    PROCEDURE regenerate_view
    IS
    v_sql VARCHAR2(32000) := 'CREATE OR REPLACE VIEW vname AS SELECT ';
    BEGIN
    FOR r IN (SELECT * FROM activity_def ORDER BY activity_name)
    LOOP
    v_sql := v_sql || activity_name || ',';
    END LOOP;
    -- Trim last comma
    v_sql := RTRIM( v_sql, ',' )
    -- Finish the SQL
    v_sql := v_sql || ' FROM activity';
    EXECUTE IMMEDIATE v_sql;
    END;

    (You need to modify the logic here to get the result you want, but you can see the principle now I hope.)

    The reason I suggest using DBMS_JOB is that you cannot do the create view within the trigger itself because the changes have not yet been committed and may be rolled back. Also, you would have to use an autonomous transaction in the trigger, which would mean it could not see the changes anyway.

Posting Permissions

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