Db2 Error 42824
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up DB2 using parameteres in like statement up vote 1 down vote favorite 1 The problem is http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/codes/src/tpc/n414.html as following: I have a Procedure in DB2 that receives couple parameters and does a filtering. One of the parameters is a VARCHAR thath is needed for a like statement. In SQLServer I simply join the string like so '%'+prmName+'%', naturally this won't work in DB2 and needs to be replaced with '%'||prmName||'%'. But then this error pops up: DB2 Database Error: ERROR [42824] [IBM][DB2/NT] SQL0132N A LIKE predicate or POSSTR http://stackoverflow.com/questions/6828488/db2-using-parameteres-in-like-statement scalar function is not valid because the first operand is not a string expression or the second operand is not a string. A LOCATE or POSITION scalar function is not valid because the first operand is not a string or the second operand is not a string expression. LINE NUMBER=6. SQLSTATE=42824 After Google-ing the error I found out that DB2 cannot support column names and parameter in LIKE statements, among couple of others. Since I cant change the code of the application but I need to transfer the SQLServer procedure to DB2, any help to find the workaround is appreciated. This is the code in SQLServer CREATE PROCEDURE [DATABYUSERNAME] ( @prmQuestionnaireId int, @prmStartDate DateTime, @prmEndDate DateTime, @prmUserName nvarchar(100) ) AS SELECT * from ExecutedQuestionnaire EQ left outer join SecurityUserTable SUT on EQ.CreatedBy = SUT.UserId where EQ.CreationDate between @prmStartDate and @prmEndDate and SUT.Name LIKE '%'+@prmUserName+'%' and EQ.QuestionnaireId = @prmQuestionnaireId RETURN And this is the try of the select statement in DB2: BEGIN ATOMIC DECLARE bla VARCHAR(100); DECLARE search VARCHAR(100); set bla = 'sup'; SELECT EQ.EXECUTEDQUESTIONNAIREID,EQ.EXECUTEDQUESTIONNAIRESTATUSID,EQ.CREATIONDATE,EQ.CREATEDBY,EQ.ISCOMPLETE,EQ.QUESTIONNAIREID, SUT.NAME, SUT.USR FROM EXECUTEDQUESTIONNAIRE EQ LEFT OUTER JOIN SECURITYUSERTABLE SUT ON EQ.CREATEDBY = SUT.USERID WHERE EQ.CREATIONDATE BETWEEN '2010-12-01' and '2011-12-01' AND UPPER(SUT.NAME) LIKE Upper(CAST('%'||bla||'%' as VARCHAR(100))) AND EQ.QUESTIONNAIREID = 1; END sql-server stored-procedures db2 share|improve this q
Tag: Error: DB2 SQL Error: SQLCODE=-132 SQLSTATE=42824 DB2 SQL Errors Codes and Error Messages and Warnings from Error -130 to -132 February 5, 2015 SQLServerF1 Leave a comment Error: DB2 SQL Error: SQLCODE=-130, SQLSTATE=22019, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER= THE ESCAPE CLAUSE CONSISTS http://www.sqlserverf1.com/tag/error-db2-sql-error-sqlcode-132-sqlstate42824/ OF MORE THAN ONE CHARACTER, OR THE STRING PATTERN CONTAINS AN INVALID OCCURRENCE OF THE ESCAPE CHARACTER Explanation: The ESCAPE character must be a single character, either SBCS or DBCS as appropriate. For ‘column-name LIKE pattern', the ESCAPE http://www.cnblogs.com/zhangchuan210/p/3232321.html character can only appear in the character string if it is followed by itself, %, or _ (underscore). The Escape Clause cannot be specified if the column name at the left of the LIKE or NOT LIKE has db2 error the MIXED subtype. System action: The statement cannot be executed. Programmer response: Correct the string pattern, or choose a different ESCAPE character and change the pattern accordingly, or eliminate the use of the Escape Clause on the LIKE or NOT LIKE predicate where the column name to the left has the MIXED subtype. SQLSTATE: 22019 if other than invalid ESCAPE pattern. 22025 if invalid ESCAPE pattern. Error: DB2 SQL Error: SQLCODE=-131, SQLSTATE=42818, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER= db2 error 42824 STATEMENT WITH LIKE PREDICATE HAS INCOMPATIBLE DATA TYPES Explanation: If the column name at the left of LIKE or NOT LIKE is of type character, the expression at the right and the ESCAPE character must be of type character. If the column name is of type graphic, the expression at the right and the ESCAPE character must be of type graphic. If the column data type is binary string, the expression on the right and the ESCAPE character must be binary strings. System action: The statement cannot be executed. Programmer response: Check the data type of every operand. SQLSTATE: 42818 Error: DB2 SQL Error: SQLCODE=-132, SQLSTATE=42824, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER= AN OPERAND OF value IS NOT VALID Explanation: The operation value can be the LIKE predicate, the ESCAPE clause, the LOCATE scalar function, the LOCATE_IN_STRING scalar function, the POSITION scalar function, or the POSSTR scalar function. The operand or argument of value was not valid for one of the following reasons: v The match-expression operand of a LIKE or NOT LIKE predicate must be a string expression. v The pattern-expression operand of the LIKE or NOT LIKE predicate, or search-string argument of the LOCATE, LOCATE_IN_STRING, POSITION, or POSSTR function must be a string expression that can be specified by any one of the following: – A constant – A special register – A host variable (including
00000 The SQL statement finished successfully. 01xxx The SQL statement finished successfully, but with a warning. +012 01545 The unqualified column name was interpreted as a correlated reference. +098 01568 A dynamic SQL statement ends with a semicolon. +100 02000 No rows found to satisfy the SQL statement. +110 01561 Update to a table defined using DATA CAPTURE was not signaled to originating subsystem. (DPROP) +111 01590 The SUBPAGES clause was specified (and ignored) for a Type 2 index. +117 01525 The number of values being inserted does not equal the number of columns in the table being inserted to. +162 01514 Named tablespace placed in check pending status. +203 01552 The named qualified column was resolved using a non-unique name. +204 01532 Named object is not defined to DB2. +206 01533 Named column does not exist in any table named in the SQL statement. +218 01537 EXPLAIN cannot be executed for the SQL statement as it references a remote object. +219 01532 The named PLAN_TABLE does not exist. +220 01546 Improperly defined PLAN_TABLE; check definition of named column. +236 01005 The value of SQLN in the SQLDA should be at least as large as the number of columns that are being described. +237 01594 At least one of the columns being described is a distinct type, so additional space is required for extended SQLVAR entries. +238 01005 At least one of the columns being described is a LOB, so additional space is required for extended SQLVAR entries. +239 01005 At least one of the columns being described is a distinct type, so additional space is required for extended SQLVAR entries. +304 01515 Value cannot be assigned to host variable because it is out of range for the data type. +331 01520 String cannot be translated so it has been assigned to NULL. +339 01569 Character conversion problem may exist due to connection to a DB2 V2.2 subsystem. +394 01629 Optimizer "hints" used to select the access path. +395 01628 Invalid optimizer "hints" specified; reason code specifies why. Optimizer "hints" were ignored. +402 01521 Unknown location. +403 01522 CREATE ALIAS object does not exist locally. +434 01608 The specified feature will not be supported in future releases of DB2. IBM recommends that you stop using this feature. +445 01004 Value has been truncated by a CAST function. +462 01Hxx Warning issued by user-defined function or stored procedure. +464 01609 Named stored procedure exceeded the limit on the number of query results sets it can return. +466 01610 Specifies the number of query results sets returned by the name