3 Replies Latest reply on Jul 28, 2010 5:42 AM by Rakesh Balguri

    Error in creating the Updatable View Model

    Rakesh Balguri Expert

      Hi all,

       

      I am creating a view model using two tables from a Single source.

      And I want the view model to be updatable.

       

      Using the Designer I had created a view model and made it updatable by selecting the check box in the Transformation Editor.

      The table name in the view is Jobs_Regions

       

      I am getting the following error

       

      For Update

       

      The Default UPDATE Procedure could not be generated from the SELECT statement.
      You may uncheck the 'Use Default' checkbox to type in a procedure.
      The query defining an updatable virtual group cannot have more than one group in its FROM clause Oracle.HR.REGIONS

       

      For Insert


      The Default UPDATE Procedure could not be generated from the SELECT statement.
      You may uncheck the 'Use Default' checkbox to type in a procedure.
      The query defining an updatable virtual group cannot have more than one group in its FROM clause Oracle.HR.REGIONS

       

      For Delete

      The Default UPDATE Procedure could not be generated from the SELECT statement.
      You may uncheck the 'Use Default' checkbox to type in a procedure.
      The query defining an updatable virtual group cannot have more than one group in its FROM clause Oracle.HR.REGIONS

       

       

      Do we need to create any Procedures for Inserting, Updating and Deleting.

       

      When I try to insert into the view after making the View Updataable I am getting the following error

       

      Error Code:ERR.015.008.0009 Message:Remote org.teiid.api.exception.query.QueryResolverException: Error Code:ERR.015.008.0009 Message:Insert is not allowed on the virtual group Job_Region: no Insert procedure was defined.

       

      Please help me in solving this problem.

       

       

      Thanks

      Rakesh.

        • 1. Re: Error in creating the Updatable View Model
          John Doyle Apprentice

          Rakesh,

           

          You will need to write the SQL for each operation (update/insert/delete) that maps the values coming into the virutal view table to the physical tables.  In the simplest example, if your virutal view table has columns A and B coming from two different physical models, you need to create the SQL that updates/inserts/deletes those values in correct physical table.  Teiid and Teiid Designer can't figure out what the logic required to perform these operations is, it can be complex, so you have to provide it.

           

          ~john

          • 2. Re: Error in creating the Updatable View Model
            Ramesh Reddy Master

            To add to John's comment..

             

            Also these updates can be performed transactional context. If your underlying sources are XA capable and you created the data sources using the XA data source, these updates are performed using a Transaction Manager. If you used single source under view then you do not need XA, however if used more than one source to define the transformation XA transactions can be used.

             

            Ramesh..

            • 3. Re: Error in creating the Updatable View Model
              Rakesh Balguri Expert

              Hi,

               

              Can you please tell me how do we map/get the values coming into the virtual view table.

               

              For example I have a view table which contains Columns A and B.

               

              Column A is from Basetable1 and column B is from Basetable2

               

              I need to insert the row into the Base table1 with column A's value and  insert the row into the Base table2 with column B's value.

               

              For this I need to write queries like

               

              insert into BaseTable1 values(ColumnA's Value, ....);

               

              insert into BaseTable2 values(ColumnB's Value, ....);

               

               

              Can you tell me how do I get the values of ColumnA and ColumnB from the insert query.

               

              Thanks

              Rakesh