teiid update using data from other schema

[gadeyne.bram]

Hi,

I'm using Teiid 8.3.

Is it possible to do something like this with teiid?

The query should update values in one table in schema a based on values in an other table in schema b.

update a.table1

set a.table1.col = (select b.table2.val from b.table2 where b.table2.id = a.table1.id)

where a.table1.col is null;

When I execute a simular query, it seems like the query is translated ok but is completely executed agains the database hosting schema a. Therefore an exception is thrown indicating that b.table2 can not be found.

[rareddy]

You need to create a "view" model, separate from both source models that have your tables in, and on this new model either create a stored procedure or view with update semantics to achieve what you are looking for.

note: a source model represents a schema on a physical datasource, a view model represents a logical model. see more info here The Basics

Ramesh..

[xiaoyanjava]

how to create a stored procedure ? create view model ---> as child--->procedure, I can only get virtual procedure, update a set a.col1=* where a.col2=*,this statement is executed successful ,but UPDATE CMST_Standard_SpecTab SET sst_spec_code = (SELECT DFI_WHS_CODES FROM view.dataChange) WHERE sst_spec_code IS NULL will error ,view.dataChange not exists! can you help me?thanks very much!@

[rareddy]

It is indicating that engine does not know what is "view.dataChange". Do you have that table in your view?

[xiaoyanjava]

see picture below ,I create a virtual procedure ,the grammar is ok ,but when I preview data ,it return errors" the view is not exists" but when I use it in select statement ,it returns success.

the select statement is success like this:

SELECT

  zeceff.DATACHANGE_FAILITEM.DFI_VALUE AS dvalue, zeceff.DATACHANGE_FAILITEM.DFI_WHS_CODES AS codes, zeceff.DATACHANGE_FAILITEM.DFI_TYPE AS type, zeceff.DATACHANGE_FAILITEM.DFI_NOTE AS note, zeceff.DATACHANGE_FAILITEM.DFI_STATUS AS status

  FROM

  zeceff.DATACHANGE_FAILITEM, zzff.CMST_Standard_SpecTab

  WHERE

  zeceff.DATACHANGE_FAILITEM.DFI_VALUE IN (SELECT zzff.CMST_Standard_SpecTab.SST_SPEC_NAME FROM zzff.CMST_Standard_SpecTab)

the update procesure  like this ,it returns view not exists error

CREATE VIRTUAL PROCEDURE

BEGIN

  UPDATE zeceff.DATACHANGE_FAILITEM SET dfi_whs_codes = ((zeceff.DATACHANGE_FAILITEM.DFI_WHS_CODES || ',') || '399399393') WHERE zeceff.DATACHANGE_FAILITEM.DFI_VALUE IN (SELECT zzff.CMST_Standard_SpecTab.SST_SPEC_NAME FROM zzff.CMST_Standard_SpecTab WHERE zzff.CMST_Standard_SpecTab.SST_SPEC_CODE IS NULL);

  SELECT * FROM zeceff.DATACHANGE_FAILITEM;

END

[rareddy]

You should use "SELECT" statement, you can not use "UPDATE" statement for the view selection transformation. If you want to "update" the view, then look for a separate "update" tab, where you can provide that statement.