how to update data from other table ?

[xiaoyanjava]

I want to update table data by select from another table ,such as :

CREATE VIRTUAL PROCEDURE

BEGIN

  UPDATE CMST_Standard_SpecTab SET sst_spec_code = (SELECT DFI_WHS_CODES FROM view.dataChange where sst_spec_code = DFI_WHS_CODES ) WHERE sst_spec_code IS NULL;

  SELECT d.*, c.* FROM view.dataChange AS d, CMST_Standard_SpecTab AS c WHERE (sst_spec_name = dfi_value) AND (sst_spec_code IS NULL);

END

but returns VIEW does not exist error,can you help me to resolve it? thank you !

also you tell me that you can create stored procedure ,but I only can create virtual procedure?that's why?

[shawkins]

> 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

[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

can you help me resolve it ? thank you very much !

[shawkins]

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.

[xiaoyanjava]

sorry ,I still can't understand.but when I update data using other schema table,such as :

FOR EACH ROW

BEGIN

  UPDATE tableA SET columnA = 'C9939' WHERE tableA.columnB =  'c01';

END

how  to call and execute it？

[shawkins]

> 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

Steve