SQLCODE=-104 Received Executing Search Objects that contain LAST UPDATE ON in the SQL

Products

• Repository

Issue/Introduction

Description:

If the first line in the query definition for a Search Object defined in VD SHOPCNTL; VT TAB CNTL begins with LAST UPDATED ON, the following error is received during execution of this search object in WSO

Error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601

example

==MSG> DESCRIPT TEXT FOR: COPYBOOKS FOR FINDER, DBXT, 0          
==MSG> LAST UPDATED BY   USER01 AT   2013-05-16-13.27.54.673951       
000001    LAST UPDATED ON 2009/04/29 AT 15:37 BY USER01               
000002    SELECT E.ENT_ID, E.COPYBOOK_NAME AS COPYBOOK, E.LANGUAGE,    
000003    E.PREFIX, E.SUFFIX, E.INCLUDE_01, E.QUAL_DATASET_1 AS DATASET
000004    FROM  repowner.DBX_DES1_COPYBOOK E,                           
000005                  repowner.DBX_XREF X1                         
000006           WHERE E.ENT_ID = X1.ENT_ID AND                        
000007           X1.ENT_TYPE = 557 AND (?)                             
000008 ORDER BY 2, 3              

Solution:

This error is the result of a space in the query definition for a queryid in the repository table IDQUERYRELATION. Execute the following UPDATE statement to correct the SQL that results in this error

-
UPDATE repowner.IDQUERYRELATION                                       
SET QUERIES =                                                         
'SELECT  ENT_ID, TEXT, SEQ_NUM FROM ##creator##.DBX_TEXT_1 WHERE NOT   
(SUBSTR(TEXT, 1 , 16)  = "LAST UPDATED ON ") AND ENT_ID =             
##searchobjectid## ORDER BY SEQ_NUM ASC FOR READ ONLY WITH UR'         
WHERE QUERYIDS IN ('2','28','29');                                     
COMMIT                                                                 
;          

Environment

Release:
Component: XTR