1 of 1 people found this helpful
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
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!@
It is indicating that engine does not know what is "view.dataChange". Do you have that table in your view?
the select statement is success like this:
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
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
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;
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.