ORA-00932: inconsistent datatypes:expected NUMBER got CHAR

when i execute this update statement I am getting this exception.docNUMBER  IS VARCHAR2(50).

    UPDATE docmaster VM
       SET VM.docNUMBER = CASE WHEN ((SELECT   instr('01108/R', 'R')
                         from DUAL) = 0) THEN
                         (310320 + 1)
                         ELSE
                         11 || '/R'
                          END
     WHERE VM.DOC_NO = '2019154';
LVL 20
chaitu chaituAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should work better:
 UPDATE docmaster VM
       SET VM.docNUMBER = CASE WHEN ((SELECT   instr('01108/R', 'R')
                         from DUAL) = 0) THEN
                         TO_CHAR(310320 + 1)
                         ELSE
                         11 || '/R'
                          END
     WHERE VM.DOC_NO = '2019154';

Open in new window

0
 
chaitu chaituAuthor Commented:
you are lucky;am to delete this question .i got the answer
0
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
"You are lucky'.  Why?   The main problem with your "case" statement is the fact that the first value returned is a number  (310320 + 1)  but the other value is a varchar2 (11 || '/R').  Since you aren't explicitly converting the first one to a character, Oracle assumes that both should match the datatype of teh first value returned, which is a number, but the implicit datatype conversion causes the .ORA-00932 error.

It is never sa good idea in Oracle to depend on implict datatype conversions.  Youi are always safer doing explicit datatype conversions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.