Oracle SQL export to csv show error: ORA-01722: invalid number

Question

table_a                     table_b
--------------             ----------
product,amount,pcode      rounding,pcode
--------------             ----------
apple,100.00,001              0.02,001
orange,150.02,001            -0.02,001


output
----------   
apple,100.02
orange,150.00

select a.product||','||sum(a.amount)+b.rounding from table_a a,table_b b
where a.pcode=b.pcode
group by a.product,b.rounding

Hi , I trying to spooling this query to csv format, but showing error:

ORA-01722 invalid number.

when I remove +b.rounding then no issued at all, may I know how to use sum(a.amount)+b.rounding in my query ?

your kinds assist is much appreciated.

score 1 · Accepted Answer · 2021-03-30 04:49:15Z

1

The error has nothing to do with "export to csv" or with aggregation. You can reproduce it very simply, like this:

select 'a' || 1 + 3 as result from dual;

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

The reason is very simple: concatenation (the || operator) has the same precedence as addition (+). Since in your formula concatenation comes first, it is performed first. In my example, the number 1 is converted (implicitly) to the string '1' and concatenated to 'a', resulting in 'a1'. Then this must be added to 3. To do that, Oracle tries to convert 'a1' (implicitly) to number, and obviously that fails, with the same error you observed.

If you expected the result to be the string 'a4' (in my example), the solution is trivial: use parentheses.

select 'a' || (1 + 3) as result from dual;

RESULT
------
a4

The same applies to your situation.

Note that the following works - showing that || and + have the same precedence; concatenation is not stronger than addition, they are simply performed in order, left to right.

select 1 + 3 || 'a' as result from dual;

RESULT
------
4a

Here we don't need parentheses, because 1 + 3 = 4, and then that is converted to the string '4' and then 'a' is concatenated to it.

edited Mar 30, 2021 at 4:49

answered Mar 30, 2021 at 4:43

user5683823

Sign up to request clarification or add additional context in comments.

1 Comment

Littlefoot Over a year ago

Not that it matters, but - I just love reading your explanations.

Collectives™ on Stack Overflow

Oracle SQL export to csv show error: ORA-01722: invalid number

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related