1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Five tables that are dependent on one table?

Discussion in 'Oracle Forms and Reports' started by Urosh, Apr 25, 2013.

  1. Urosh

    Urosh Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hi everybody,

    Lets call this main table(block) - table A, and the other tables that are dependent on it - tables(blocks) A1, A2, A3, A4, A5 (these five tables take their primary key from the table A and that is the only item those tables have, table A is a table that contains resources data and the other five explain what kind of resource it is - tool, energetic, material, work object and side-resource )

    So, I've made a form just to query block A (in a tabular way) and I've made non-database item within that block in which I want to show what kind of resource is it (tool, energetic....that is to say, does it belong to block A1 or A2...). I've tried to do it with post_query trigger using else if command but I can not find my way around it.
    Help please!

    Thanks!
     
  2. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Urosh,

    Plz upload an expanded screenshot of your object navigator [design time], output[run-time] and the post-query trigger code.

    And also i got a clarification to make here, .. in your logic;
    Is it possible that there exists multiple parent-child linkages in b/w the back-end architecture you described? .. For E.g: As u said "A1 or A2", so guessing by that .. can i say that A1 or A2 or A3 or A4 or A5 wouldn't be containing redundant data? ... And one item'd b stored in either of these 5 tables? .. Plz confirm.
     
  3. Urosh

    Urosh Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hi Ari,

    as you can see in the picture in object navigator, table A - RESURSI and A1, A2, A3... follows; primary key for table A is ID_RES and it has one non-database item (NONDB_TIP_RESURSA) in which I want to say what kind of resource it is; as you can see primary keys of all the other tables are RES_ID_RES (taken from table A)
    This is my inital idea for a post_query trigger in table A - RESURSI

    BEGIN
    IF :RESURSI.ID_RES = :ENERGENTI.RES_ID_RES THEN
    :RESURSI.NONDB_TIP_RESURSA := 'energent';
    ELSIF :RESURSI.ID_RES = :ALATI.RES_ID_RES THEN
    :RESURSI.NONDB_TIP_RESURSA := 'alat';
    ELSIF :RESURSI.ID_RES = :pRATECI_RES.RES_ID_RES THEN
    :RESURSI.NONDB_TIP_RESURSA := 'prateci resurs';
    ELSIF :RESURSI.ID_RES = :pRIBORI.RES_ID_RES THEN
    :RESURSI.NONDB_TIP_RESURSA := 'pribor';
    ELSE
    :RESURSI.NONDB_TIP_RESURSA := 'predmet rada';
    END IF;
    END;

    but I'm not sure how to proceed, how to make it work; and as you can see in run-time pic where it says "Tip resursa" (last column) it shows "predmeta rada" for every record although that is not correct, it is because of the badly written, unfinished trigger (only ELSE :RESURSI.NONDB_TIP_RESURSA := 'predmet rada'; is executed)

    Thanks for your help!
     

    Attached Files:

  4. Urosh

    Urosh Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    hmm I didn't understand you at first, yes you are right A1,2,3,4,5 wouldn't be containing redundant data and one item is stored only in one of these five tables
     
  5. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    I would try a couple of things...first, run the form query in a SQL session to ensure the query is returning what you think it's returning.

    Second, change your ELSE leg :

    ELSE
    :RESURSI.NONDB_TIP_RESURSA := 'predmet rada';

    to an ELSIF like the other and include the specific condition needed for the assignment to happen. After this, include the ELSE condition to assign the ID VALUE from Table A. This will tell you whether your code is doing what you think it's doing.

    I suspect you would have better luck putting ITEM level triggers on each of the items but the trade off will be in performance depending on the # of records.

    HTH

    CJ
     
  6. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Urosh,

    Apologies for the delay !! I was a bit busy and couldn't follow the thread.

    Ok, Firstly u're doing it all wrong. The Query here runs for the block RESURSI and in its Post-Query u're comparing the values with a different block's values (which is also multi-line)? .. ohho ..blunderrr :D

    Here goes the Post-Query:
    Code (SQL):

    DECLARE
        Resource_Type   Varchar2(30);    
    BEGIN
        SELECT  'energent'
        INTO    Resource_Type
        FROM    Sys.dual
        WHERE   EXISTS
                (SELECT res_id_res
                 FROM   <table_name1>
                 WHERE  res_id_res  =   :resursi.id_res
                );
               
        IF  Resource_Type   IS  NULL    THEN
            SELECT  'alat'
            INTO    Resource_Type
            FROM    Sys.dual
            WHERE   EXISTS
                    (SELECT res_id_res
                     FROM   <table_name2>
                     WHERE  res_id_res  =   :resursi.id_res
                    );
        END IF;

        IF  Resource_Type   IS  NULL    THEN
            SELECT  'prateci resurs'
            INTO    Resource_Type
            FROM    Sys.dual
            WHERE   EXISTS
                    (SELECT res_id_res
                     FROM   <table_name3>
                     WHERE  res_id_res  =   :resursi.id_res
                    );
        END IF;

        IF  Resource_Type   IS  NULL    THEN
            SELECT  'pribor'
            INTO    Resource_Type
            FROM    Sys.dual
            WHERE   EXISTS
                    (SELECT res_id_res
                     FROM   <table_name4>
                     WHERE  res_id_res  =   :resursi.id_res
                    );
        END IF;

        IF  Resource_Type   IS  NULL    THEN
            SELECT  'predmet rada'
            INTO    Resource_Type
            FROM    Sys.dual
            WHERE   EXISTS
                    (SELECT res_id_res
                     FROM   <table_name5>
                     WHERE  res_id_res  =   :resursi.id_res
                    );
        END IF;

        resursi.nondb_tip_resursa   :=  Resource_Type;
    END;
     
     
    Urosh likes this.
  7. Urosh

    Urosh Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hi Ari,

    I used the trigger you've written and couldn't compile it cause of this line of code
    Code (Text):
    resursi.nondb_tip_resursa   :=  Resource_Type;
    so I've just added this
    Code (Text):
    :resursi.nondb_tip_resursa   :=  Resource_Type;
    yes - just those two dots at the beginning, so it compiled it but when I ran the form and query I've got this error
    "FRM-40735:pOST-QUERY trigger raised unhandled exception ORA-01403" which would show up for almost every record that is there and I would click ok every time, so I would end up with just a few records after the query, those are 'energent' (only the first lines of code were executed properly). But I'm a beginner (obviously :) and your code helped me a lot and eventually I made it work as I wanted with these code lines:
    Code (Text):

    DECLARE
      Resource_Type_1   Varchar2(30) := 'alat';
      Resource_Type_2   Varchar2(30) := 'energent';
      Resource_Type_3   Varchar2(30) := 'prateci resurs';
      Resource_Type_4   Varchar2(30) := 'pribor';
      Resource_Type_5   Varchar2(30) := 'predmet rada';
     
      CURSOR tip_resursa_1 IS
      SELECT Resource_Type_1
      FROM Sys.dual
      WHERE EXISTS
          (SELECT res_id_res
           FROM alati
           WHERE  res_id_res  =   :resursi.id_res
           );
     
      CURSOR tip_resursa_2 IS
      SELECT Resource_Type_2
      FROM Sys.dual
      WHERE EXISTS
          (SELECT res_id_res
           FROM energenti
           WHERE  res_id_res  =   :resursi.id_res
           );
           
      CURSOR tip_resursa_3 IS
      SELECT Resource_Type_3
      FROM Sys.dual
      WHERE EXISTS
          (SELECT res_id_res
           FROM prateci_res
           WHERE  res_id_res  =   :resursi.id_res
           );
           
      CURSOR tip_resursa_4 IS
      SELECT Resource_Type_4
        FROM Sys.dual
      WHERE EXISTS
          (SELECT res_id_res
           FROM pribori
           WHERE  res_id_res  =   :resursi.id_res
           );
           
      CURSOR tip_resursa_5 IS
      SELECT Resource_Type_5
      FROM Sys.dual
      WHERE EXISTS
          (SELECT res_id_res
           FROM predmeti_rada
           WHERE  res_id_res  =   :resursi.id_res
           );
     
    BEGIN
     
      OPEN tip_resursa_1;
      FETCH tip_resursa_1 INTO :resursi.nondb_tip_resursa;
        CLOSE tip_resursa_1;

      OPEN tip_resursa_2;
      FETCH tip_resursa_2 INTO :resursi.nondb_tip_resursa;
        CLOSE tip_resursa_2;

      OPEN tip_resursa_3;
      FETCH tip_resursa_3 INTO :resursi.nondb_tip_resursa;
        CLOSE tip_resursa_3;

      OPEN tip_resursa_4;
      FETCH tip_resursa_4 INTO :resursi.nondb_tip_resursa;
        CLOSE tip_resursa_4;
       
      OPEN tip_resursa_5;
      FETCH tip_resursa_5 INTO :resursi.nondb_tip_resursa;
        CLOSE tip_resursa_5;

    END;
     
    Thanks a lot!