> but returns VIEW does not exist error,can you help me to resolve it?
What view specifically is it complaining about? In general what version of Teiid Designer / Teiid are you on?
> also you tell me that you can create stored procedure ,but I only can create virtual procedure?that's why?
A procedure created on a view model is termed a "virtual procedure". It is the Teiid equivalent to a stored procedure in that it is a series of statements executed in Teiid.
A procedure created on a source model just provides the metadata to Teiid so that we know a source stored procedure exists and can be called with the given parameters and procedures the expected output.
Also is this effectively the same post as is on Re: teiid update using data from other schema
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:
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;
can you help me resolve it ? thank you very much !
The exception is coming from Oracle, so the problem is a lot further down. We'll probably need the relevant server side logging, such as the full sql statement issued to Oracle.
> how to call and execute it？
A for each row instead of trigger will be called for the given update (insert/update/delete). So if you issue an update dataChange02 statement then your procedure body will be called for each row. You can use the new/old/changing variables to control how the update is processed - Update Procedures - Teiid 8.6 (draft) - Project Documentation Editor