ORA-00932 inconsistent datatypes: expected string got string

EXECSQL DBIO 수행중 아래와 같은 에러가 발생하였다.

ORA-00932: inconsistent datatypes

vldt_coltr_amt1 컬럼은 number 타입.

@ 문제의 쿼리

vldt_coltr_amt1 = CASE WHEN :vldt_coltr_amt < 0 THEN 0 ELSE :vldt_coltr_amt END

@ 해결된 쿼리( TO_NUMBER 를 사용해서 해결 )

vldt_coltr_amt1 = CASE WHEN TO_NUMBER(:vldt_coltr_amt) < 0 THEN 0 ELSE TO_NUMBER(:vldt_coltr_amt) END

CASE WHEN 뿐만아니라 DECODE 사용시에도 주의!

참고&

ORA-00932 inconsistent datatypes: expected string got string

Cause: One of the following:

• An attempt was made to perform an operation on incompatible datatypes. For example, adding a character field to a date field (dates may only be added to numeric fields) or concatenating a character field with a long field.
• An attempt was made to perform an operation on a database object (such as a table or view) that is not intended for normal use. For example, system tables cannot be modified by a user. Note that on rare occasions this error occurs because a misspelled object name matched a restricted object's name.
• An attempt was made to use an undocumented view.

Action: If the cause is

• different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields.
• an object not intended for normal use, then do not access the restricted object. 

http://download.oracle.com/docs/cd/B10501_01/server.920/a96525/e900.htm#206194