5 Replies Latest reply on Oct 23, 2013 10:45 AM by Ramesh Reddy

    teiid update using data from other schema

    gadeyne.bram Master

      Hi,

       

      I'm using Teiid 8.3.

       

      Is it possible to do something like this with teiid?

       

      The query should update values in one table in schema a based on values in an other table in schema b.

       

      update a.table1

      set a.table1.col = (select b.table2.val from b.table2 where b.table2.id = a.table1.id)

      where a.table1.col is null;

       

      When I execute a simular query, it seems like the query is translated ok but is completely executed agains the database hosting schema a. Therefore an exception is thrown indicating that b.table2 can not be found.

        • 1. Re: teiid update using data from other schema
          Ramesh Reddy Master

          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

           

          Ramesh..

          1 of 1 people found this helpful
          • 2. Re: teiid update using data from other schema
            xiaoyan yuan Newbie

            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
              Ramesh Reddy Master

              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
                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 successQQ截图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

                • 5. Re: teiid update using data from other schema
                  Ramesh Reddy Master

                  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.