NVL WITH SUM FUNCTION RETURN ERROR ORA INVALID NUMBER

Mark Tabor

Hi I have a query like below and my Distribution columns in database is having number from 1-100 , as well as null some people type 90 some type 090 when performing the action it gives this below error

An error was encountered performing the requested operation

ORA-01722 INVALID NUMBER

01722 00000 INvalid Number

Cause : The specified number was invalid

Action : specify a valid number vendor code 1722

HERE IS MY QUERY

SELECT NVL(SUM(NVL(mt.Distributed,0)),0) DISTRIBUTED_AMOUNT from TBL_DISTRIBUTION mq,Client information mt

where mq.id=mt.id

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Muhammad Imran Ansari4y ago
The error "ORA-01722 invalid number error" you specified usually occurs because of conversion.
If NVL() function is not replacing the null value then check with the datatype of your table.
Further, you can share your table definition to find the error in your query.
Mark Tabor4y ago
Thanks Dinesh you are skipping the first NVL which may change my result set.
Dinesh Kumar4y ago
Please try this query

SELECT TO_NUMBER(SUM(NVL(mt.Distributed,0)),0) DISTRIBUTED_AMOUNT from TBL_DISTRIBUTION mq,Client information mt
where mq.id=mt.id