A problem regarding "where criteria"
jalen Nov 23, 2010 5:49 AMI 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.
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!