CODEWITHSUNDEEP
sqloracle-databaseselectoracle11g
Kunwar Harshavardhan Singh
Kunwar Harshavardhan Singh

Reputation: 13

Getting error in oracle 11g

Error:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

SQL:

SELECT CASE WHEN 
(
SELECT COUNT(COMP_CODE) FROM COMPANY_MASTER
)=0 THEN 1
ELSE 
(
SELECT LPAD((SELECT MAX(COMP_CODE)+1 FROM COMPANY_MASTER),3, 0) NOS FROM DUAL
)
END AS DOCNO FROM DUAL;

Upvotes: 1

Views: 68

Answers (1)

Mureinik
Mureinik

Reputation: 312219

Both branches of the if-else should return the same type. If the else branch returns a string (which is the return value of lpad), so should the if branch. E.g.:

SELECT CASE WHEN 
(
SELECT COUNT(COMP_CODE) FROM COMPANY_MASTER
)=0 THEN '1' -- '1' is a string literal
ELSE 
(
SELECT LPAD((SELECT MAX(COMP_CODE)+1 FROM COMPANY_MASTER),3, 0) NOS FROM DUAL
)
END AS DOCNO FROM DUAL;

Upvotes: 3

Related Questions