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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chaitu chaituAuthor Commented:
you are lucky;am to delete this question .i got the answer
0
Mark GeerlingsDatabase 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.