Error in creating the Updatable View Model

[rakeshsagar]

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.

[jdoyle]

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

[rareddy]

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..

[rakeshsagar]

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