5 Replies Latest reply on Oct 14, 2013 9:56 AM by Steven Hawkins

    how to update data from other table ?

    xiaoyan yuan Newbie

      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?

        • 1. Re: how to update data from other table ?
          Steven Hawkins Master

          > 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

          • 2. Re: how to update data from other table ?
            xiaoyan yuan Newbie

            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.

            QQ截图20131011162550.jpg

            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 !

            • 3. Re: how to update data from other table ?
              Steven Hawkins Master

              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.

              • 4. Re: how to update data from other table ?
                xiaoyan yuan Newbie

                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?

                1012095329.jpg

                • 5. Re: how to update data from other table ?
                  Steven Hawkins Master

                  > 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