-
1. Re: teiid update using data from other schema
rareddy Sep 24, 2013 8:34 AM (in response to gadeyne.bram)1 of 1 people found this helpfulYou 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..
-
2. Re: teiid update using data from other schema
xiaoyanjava Oct 8, 2013 3:35 AM (in response to rareddy)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!@
-
3. Re: teiid update using data from other schema
rareddy Oct 8, 2013 11:50 AM (in response to xiaoyanjava)It is indicating that engine does not know what is "view.dataChange". Do you have that table in your view?
-
4. Re: teiid update using data from other schema
xiaoyanjava Oct 11, 2013 4:30 AM (in response to rareddy)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
-
5. Re: teiid update using data from other schema
rareddy Oct 23, 2013 10:45 AM (in response to xiaoyanjava)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.