13 Replies Latest reply on Dec 2, 2010 4:47 PM by Steven Hawkins

    A problem regarding "where criteria"

    jalen zhong Newbie

      I have a view model "ITEM" and two source model "TC_ASSET" and "PROJECT_ASSIGNEES". The two souce models (one-to-one) are mapped to the view model.

      Snap1.jpg

       

      The Select procedure is (it is a little complex, but you can just use it as a reference):

      SELECT
        CIS_ARTESIA.TC_ASSET.UOI_ID AS ITM_ID, cast(null AS biginteger) AS ITM_RLE_ID, cast(null AS biginteger) AS ITM_PRD_ID, cast(null AS biginteger) AS ITM_PBL_ID, cast(null AS biginteger) AS ITM_LOC_ID, cast(null AS biginteger) AS ITM_COVERAGE_SPATIAL_LOC_ID, cast(null AS biginteger) AS ITM_RIC_ID, cast(null AS biginteger) AS ITM_ARS_ID, cast(null AS biginteger) AS ITM_CAT_ID, cast(null AS short) AS ITM_ISPRIVATE, cast(null AS biginteger) AS ITM_PRIVATE_UGP_ID, cast(null AS biginteger) AS ITM_PRIVATE_USR_ID, cast(null AS biginteger) AS ITM_CREATOR_USR_ID, cast(null AS biginteger) AS ITM_OWNER_USR_ID, cast(null AS biginteger) AS ITM_LOCKED_BY_USR_ID, cast(null AS biginteger) AS ITM_CREATOR_DEP_ID, cast(null AS biginteger) AS ITM_TXT_ID, null AS ITM_DRAMS_ID, cast(null AS biginteger) AS ITM_NUMBER, TITLE AS ITM_TITLE, null AS ITM_TITLE_ALT, COMMENTS AS ITM_DESCRIPTION, CREATION_DATE AS ITM_UPDATE_TIMESTAMP, CREATION_DATE AS ITM_DIGITIZED_TIMESTAMP, null AS ITM_TECHNICAL_COMMENT, COMMENTS AS ITM_ARCHIVE_COMMENT, cast('2010-10-10 10:10:10' AS timestamp) AS ITM_ARCHIVE_DATE, null AS ITM_RIGHTS_URL, CIS_ARTESIA.UOI_RIGHTS.DESCR AS ITM_RIGHTS_DESCRIPTION, null AS ITM_SOURCE_URL, cast('2010-10-10 10:10:10' AS timestamp) AS ITM_KILLDATE, null AS ITM_XML_METADATA, cast(null AS short) AS ITM_DELETED_FROM_SOURCE, CREATION_DATE AS ITM_CREATE_TIMESTAMP, cast(null AS short) AS ITM_SOFT_DELETED, null AS ITM_LOCATION, null AS ITM_COVERAGE_SPATIAL_LOCATION, cast(null AS biginteger) AS ITM_IM_VERSION, cast(null AS biginteger) AS ITM_IM_WRITE_LOCK, cast(null AS short) AS ITM_ISTEMPLATE, cast(null AS biginteger) AS ITM_OFF_ID, cast(null AS biginteger) AS ITM_EXT_ID, EMAIL_ADDR AS ITM_EMAIL, cast(null AS biginteger) AS ITM_DEP_ID
      FROM
        ((CIS_ARTESIA.TC_ASSET LEFT OUTER JOIN CIS_ARTESIA.PROJECT_ASSIGNEES ON CIS_ARTESIA.TC_ASSET.UOI_ID = CIS_ARTESIA.PROJECT_ASSIGNEES.UOI_ID) LEFT OUTER JOIN CIS_ARTESIA.UOI_RIGHTS ON CIS_ARTESIA.UOI_RIGHTS.UOI_ID = CIS_ARTESIA.TC_ASSET.UOI_ID) LEFT OUTER JOIN CIS_ARTESIA.IPTC_APP_RECORDS ON CIS_ARTESIA.IPTC_APP_RECORDS.UOI_ID = CIS_ARTESIA.TC_ASSET.UOI_ID

       

      The problem is with the DELETE and UPDATE procedure:

      CREATE PROCEDURE
      BEGIN
      VARIABLES.ROWS_UPDATED = DELETE FROM CIS_ARTESIA.TC_ASSET WHERE TRANSLATE CRITERIA;
      DELETE FROM CIS_ARTESIA.PROJECT_ASSIGNEES WHERE TRANSLATE CRITERIA;
      END

       

      Two different SQL execution:

      ------------------------------------------------------------------------------

      1) I deployed the VDB and executed the SQL "delete from item where itm_id = '1'"

      Error message prompted:

      delete from item where itm_id = '1'

      Error Code:The multi-part identifier "TC_ASSET.UOI_ID" could not be bound.

      Executing statement:

        [Prepared Values: ['1'] SQL: DELETE FROM PROJECT_ASSIGNEES WHERE TC_ASSET.UOI_ID = ?] Message:Remote org.teiid.core.TeiidProcessingException: Error Code:The multi-part identifier "TC_ASSET.UOI_ID" could not be bound.

      Executing statement:

        [Prepared Values: ['1'] SQL: DELETE FROM PROJECT_ASSIGNEES WHERE TC_ASSET.UOI_ID = ?] Message:Error Code:The multi-part identifier "TC_ASSET.UOI_ID" could not be bound.

      Executing statement:

        [Prepared Values: ['1'] SQL: DELETE FROM PROJECT_ASSIGNEES WHERE TC_ASSET.UOI_ID = ?] Message:4104

       

      Since I have two source models, the translated SQL should be:

      DELETE FROM TC_ASSET WHERE UOI_ID = '1'

      and

      DELETE FROM PROJECT_ASSIGNEES WHERE PA_UOI_ID = '1' ( if the column of id named "PA_UOI_ID" )

       

      I think the problem may caused by my DELETE procedure calling "WHERE TRANSLATE CRITERIA" twice. Could any body help me how to write this DELETE procedure?

      -----------------------------------------------------------------

      2) I deployed the VDB and executed the SQL "delete from item where itm_description like '%jalen%'". Note that "itm_description" is from physical table "PROJECT_ASSIGNEES" (table "TC_ASSET" does not have this column)

      Error message prompted:

      delete from item where ITM_DESCRIPTION like '%jalen%'

      Error Code:The multi-part identifier "TC_ASSET.COMMENTS" could not be bound.

      Executing statement:

        [Prepared Values: ['%jalen%'] SQL: DELETE FROM PROJECT_ASSIGNEES WHERE TC_ASSET.COMMENTS LIKE ?] Message:Remote org.teiid.core.TeiidProcessingException: Error Code:The multi-part identifier "TC_ASSET.COMMENTS" could not be bound.

      Executing statement:

        [Prepared Values: ['%jalen%'] SQL: DELETE FROM PROJECT_ASSIGNEES WHERE TC_ASSET.COMMENTS LIKE ?] Message:Error Code:The multi-part identifier "TC_ASSET.COMMENTS" could not be bound.

      Executing statement:

        [Prepared Values: ['%jalen%'] SQL: DELETE FROM PROJECT_ASSIGNEES WHERE TC_ASSET.COMMENTS LIKE ?] Message:4104

       

      Actually, I want Teiid help me convert it into physical SQL like:

      DELETE FROM TC_ASSET WHERE COMMENTS LIKE ?

      But I DO NOT need this SQL:

      DELETE FROM PROJECT_ASSIGNEES WHERE COMMENTS LIKE ?

      How could I update the previous procedure to support both 1) and 2) SQLs?

      ----------------------------------------------------------------------------

       

      Thanks a lot!

        • 1. Re: A problem regarding "where criteria"
          Ramesh Reddy Master

          Jalen,

           

          In the procedure language there is conditional statement called "HAS CRITERIA", may be you can use that to write the procedure to execute the way you are looking for. The multi-part identifier could be invalid metadata in your source tables. i.e in NameInSource. See this explanation

           

          can you execute the select properly?

           

          Ramesh..

          • 2. Re: A problem regarding "where criteria"
            jalen zhong Newbie

            Yes, the select can be executed successfully.

             

            From my understanding, "has" is used for grouping the data. It is a little strange to use "has" instead of "where" to solve my problem.

             

            I am still not familiar with Teiid "has criteria" and "where criteria" even I had read the document.

             

            It would be perfect if there are more examples for these two criteria.

            • 3. Re: A problem regarding "where criteria"
              Steven Hawkins Master

              To handle the first issue where you are getting the wrong column name, you need to help translate criteria understand the join between TC_ASSET and PROJECT_ASSIGNEES using the WITH clause:

               

              CREATE PROCEDURE
              BEGIN
              VARIABLES.ROWS_UPDATED = DELETE FROM CIS_ARTESIA.TC_ASSET WHERE TRANSLATE CRITERIA;
              DELETE FROM CIS_ARTESIA.PROJECT_ASSIGNEES WHERE TRANSLATE CRITERIA WITH (TC_ASSET.UOI_ID = PROJECT_ASSIGNEES.UOI_ID)

              END

               

              The second issue, of selectively running the second delete, could be addressed with HAS criteria, for example:

               

              CREATE PROCEDURE
              BEGIN
              VARIABLES.ROWS_UPDATED = DELETE FROM CIS_ARTESIA.TC_ASSET WHERE TRANSLATE CRITERIA;

              IF (HAS CRITERIA ON (UOI_ID))

              BEGIN
                  DELETE FROM CIS_ARTESIA.PROJECT_ASSIGNEES WHERE TRANSLATE CRITERIA WITH (TC_ASSET.UOI_ID = PROJECT_ASSIGNEES.UOI_ID)

              END

              END

               

              Sorry for the confusion, we'll continue to update the docs with more examples.  The hope though is that past 7.2 you won't have to use the translate criteria construct - see https://jira.jboss.org/browse/TEIID-1351 and https://jira.jboss.org/browse/TEIID-1349.

               

              Steve

              • 4. Re: A problem regarding "where criteria"
                jalen zhong Newbie

                It is very clear.

                Thank you, Steven.

                • 5. Re: A problem regarding "where criteria"
                  jalen zhong Newbie

                  Hi Steven,

                   

                  My procedure is:

                  VARIABLES.ROWS_UPDATED = DELETE FROM CIS_ARTESIA.UOIS WHERE TRANSLATE CRITERIA WITH (Mappings.ITEM.ITM_ID = UOI_ID');

                   

                  But error occurs when running my SQL:

                  Remote org.teiid.api.exception.query.QueryPlannerException: Unable to translate criteria on the update command against the virtual group, the element itm_id is mapped to an expression whose elements are not present on the command in the procedure using the translated criteria.

                   

                  My SQL is:

                  delete from item where itm_id = '1'

                   

                  My expected real SQL is:

                  delete from UOIS where UOI_ID = '1'

                   

                  Do you know what's wrong with my procedure?

                  • 6. Re: A problem regarding "where criteria"
                    Steven Hawkins Master

                    Jalen,

                     

                    That message should indicate that UOI_ID is not on the UOIS table.  If it is, the workaround should be to qualify it as CIS_ARTESIA.UOIS.UOI_ID.  However it's unexpected that it would need to be qualified.

                     

                    Steve

                    • 7. Re: A problem regarding "where criteria"
                      jalen zhong Newbie

                      "Where translate criteria" is a little hard to use.

                       

                      Suppose we have the logic:

                      if user calls "...where col1='1' " then delete data from some physical tables.

                      If user calls "...where col1='2' " then delete data from other different tables.

                       

                      That means the real logic is based on the input value.

                      But we cannot accquire the input value in update/delete procedure. We can only use the "where translate criteria". I am not sure if we can implement the above logic.

                       

                      Can I get the value inputted by user in update/delete procedure?

                      • 8. Re: A problem regarding "where criteria"
                        Steven Hawkins Master

                        Yes translate criteria is not very easy to use.  Using the for each row trigger in 7.3 should be much simplier.  To emulate the for each row construct, which would allow you to use row vaules, you could write the procedure as:

                         

                        loop on ([view definition query] and translate criteria) as x

                        begin

                             if (x.col1 = '1')

                             begin

                                  ...

                             end

                             ...

                        end

                         

                        Other than that, I can't come up with a good way of getting at equality predicate literal values.

                         

                        Just to understand your scenario more, is your view defined by a union of the form: select '1' as col1 ... union all select '2' as col1 ... ?

                        • 9. Re: A problem regarding "where criteria"
                          jalen zhong Newbie

                          Actually, we have more than 2 databases (each has its distinct schemas) which should be combined together to be looked like one single database.

                          For example, database 1 has a table t1 with columns c11 and c12; while database 2 has one tables t2 with column c21 and c22. These tables need to be exposed as a new view named PublicView with columns c01, c02, and column c_db_id (which is used to judge which db the data is from).

                           

                          This is a simple case to make it clear what we need to do.

                           

                          SELECT procedure is just like:

                          select  t1.c11 as c01, t1.c12 as c02, 'database1' as c_db_id from db1.t1 as t1

                          union

                          select t2.c21 as c01, t2.c22 as c02, 'database2' as c_db_id from db2.t2 as t2

                           

                          UPDATE procedure is like:

                          // That's why we need get some data from user input

                          if(INPUTS.c_db_id = 'database1')

                               begin

                                    //update all relevant tables in database 1. That's why I ask the "criteria" questions

                                    update db1.t1 set ... where translate criteria on (c01) with (c01=INPUTS.c01);

                                    //update more other tables if data come from many tables

                                    update db1.t12 set ... where translate criteria;

                               end

                          else if(INPUTS.c_db_id='database2')

                               begin

                                    //update all related tables in database 2

                               end

                           

                          DELETE procedure looks like:

                          if(INPUTS.c_db_id = 'database1')

                               begin

                                    delete from db1.t1 where translate criteria;

                                    delete from db1.t12 where translate criteria;

                               end

                          else if(INPUTS.c_db_id='database2')

                               begin

                                   // delete related tables in database 2

                               end

                           

                          Regarding insert procedure, it looks the same (here we can get the c_db_id from user input directly)

                          • 10. Re: A problem regarding "where criteria"
                            Steven Hawkins Master

                            Although it seems somewhat natural in the context of a procedure, it's quite awkward to treat predicates as inputs/assigments.  So neither of those procedures will work as INPUTS only refers to the insert values or update set clauses.

                             

                            I would still recommend just reusing the view definition (although an inline view will help simplify things), suppose the view v1 is defined as "select * from (

                            select  t1.c11 as c01, t1.c12 as c02, 'database1' as c_db_id from db1.t1 as t1

                            union

                            select t2.c21 as c01, t2.c22 as c02, 'database2' as c_db_id from db2.t2 as t2) as x"

                             

                            then the procedure

                             

                            loop on (select * from v1 as x where translate criteria) as updated

                            begin

                                 if (updated.c_db_id = 'database1')

                                 begin

                                      ...

                                 end

                                 ...

                            end

                             

                            allows you to differentiate what update should be performed.  The only drawback to this approach is if you expect update/delete operations to affect a lot of rows - which would have relatively poor performance since they are performed 1 at a time.

                             

                            With 7.3 as long as your view is defined with a UNION ALL and there is no type conversion, I'll make sure that the automatic update/delete scenarios are handled for you given that each of the union branches is inherently updatable.

                             

                            Steve

                            • 11. Re: A problem regarding "where criteria"
                              jalen zhong Newbie

                              Hi Steven,

                               

                              I met a problem.

                               

                              My DELETE procedure is:

                              CREATE PROCEDURE
                              BEGIN
                              DECLARE string VARIABLES.in_cis_id_artesia = 'CIS_1';
                              LOOP ON (SELECT Mappings_View.ITEM.CIS_ID FROM Mappings_View.ITEM WHERE TRANSLATE CRITERIA) AS UPDATED
                              BEGIN
                                IF(UPDATED.CIS_ID = VARIABLES.in_cis_id_artesia)
                                BEGIN
                                 VARIABLES.ROWS_UPDATED = DELETE FROM CIS_ARTESIA.TC_ASSET WHERE TRANSLATE CRITERIA;
                                END
                                ELSE
                                BEGIN
                                 VARIABLES.ROWS_UPDATED = DELETE FROM DR.ITEM WHERE TRANSLATE CRITERIA;
                                END
                              END
                              END

                               

                              If I run following SQL:

                              1) delete from item where itm_id = '10' and cis_id = 'CIS_1'
                              2) delete from item where itm_id = '10' and cis_id = '2'

                              3) delete from item where itm_id = '10'

                              1) and 2) both run successfully.

                              But 3) prompts error:

                              Error Code:0 Message:Remote org.teiid.core.TeiidProcessingException: Error Code:0 Message:Error Code:0 Message:Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 109e3365:d6f:4cf73b9a:d4 status: ActionStatus.ABORT_ONLY >); - nested throwable: (org.jboss.resource.JBossResourceException: Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 109e3365:d6f:4cf73b9a:d4 status: ActionStatus.ABORT_ONLY >))

                               

                              I don't know why 1) and 2) are ok, but 3) failed.

                               

                              • 12. Re: A problem regarding "where criteria"
                                Ramesh Reddy Master

                                Jalen,

                                 

                                With (3), you are having an issue with transaction. You probably defined your data sources  as <local-tx-datasource>, where only one source can participate in the transaction. If you define them as <xa-datasource> you will not have an issue. If your source is not capable of participating in a transaction then use <no-tx-datasource>. Please read about transaction support in Teiid here.

                                 

                                http://docs.jboss.org/teiid/7.2.0.Final/reference/en-US/html_single/#transaction_support (especially section 6.4)

                                 

                                Thanks.

                                 

                                Ramesh..

                                • 13. Re: A problem regarding "where criteria"
                                  Steven Hawkins Master

                                  Jalen,

                                   

                                  You probabl don't want to use a loop in that way, which would end up performing the delete against the entire table for each row that matches.

                                   

                                  If you don't want to perform the delete row by row, then you could use:

                                   

                                  CREATE PROCEDURE
                                  BEGIN
                                  DECLARE string VARIABLES.in_cis_id_artesia = 'CIS_1';
                                  INSERT INTO #temp SELECT DISTINCT Mappings_View.ITEM.CIS_ID FROM Mappings_View.ITEM WHERE TRANSLATE CRITERIA
                                     IF(EXISTS SELECT 1 FROM #temp WHERE CIS_ID = VARIABLES.in_cis_id_artesia)
                                    BEGIN
                                     VARIABLES.ROWS_UPDATED = DELETE FROM CIS_ARTESIA.TC_ASSET WHERE TRANSLATE CRITERIA;
                                    END

                                  IF(EXISTS SELECT 1 FROM #temp WHERE CIS_ID <> VARIABLES.in_cis_id_artesia)
                                    BEGIN
                                     VARIABLES.ROWS_UPDATED = DELETE FROM DR.ITEM WHERE TRANSLATE CRITERIA;
                                    END
                                  END

                                   

                                  Upon further reflection for the union scenario the better solution would be to use separate views for each of the union branches.  Then the delete procedure would just be:

                                   

                                  ROWS_UPDATED = delete from v1 where translate criteria;

                                  ROWS_UPDATED = ROWS_UPDATED + delete from v2 where translate criteria;

                                  ...

                                   

                                  If the criteria specifies an id that is not provided in the SELECT clause of the view that delete will not get issued to the source.

                                   

                                  Steve