CODEWITHSUNDEEP
sqloracle-database
Anas Rafei
Anas Rafei

Reputation: 21

PLSQL error : invalid number

I am trying to execut this query which is a part of my procedure, and the one creating the probleme :

select SUM(
                 CASE --DT007
              WHEN PRO.Sens_Mnt_Brut = 'D'
              THEN '-' || TO_CHAR(PRO.Mnt_Brut,'99999999999,99')
              WHEN PRO.Sens_Mnt_Brut = 'C'
              THEN '' || TO_CHAR(PRO.Mnt_Brut,'99999999999,99')
                END) OVER(PARTITION BY PRO.Num_Est_Remise) - SUM(TO_CHAR(PRO.Mnt_Comm_Tot_Ope,'99999999999,99')) OVER(PARTITION BY PRO.Num_Est_Remise))--DT015

From dlc_pr_operation pro;

After execution i am getting this error :

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    
*Action:

is it because of the conversion from char to number and the commas that i am adding to the query or is it something else .

Thank you in advance for your help.

Upvotes: 0

Views: 985

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You are doing a lot of explicit and implicit conversion between numbers and strings, and inconsistencies and assumptions are tripping you up. But they are all unnecessary anyway - you don't need to convert to and from strings at all. Your query could be rewritten as:

select SUM(
    CASE PRO.Sens_Mnt_Brut WHEN 'D' THEN -1 ELSE 1 END * PRO.Mnt_Brut
  ) OVER (PARTITION BY PRO.Num_Est_Remise)
    - SUM(PRO.Mnt_Comm_Tot_Ope) OVER(PARTITION BY PRO.Num_Est_Remise)
From dlc_pr_operation pro;

which just decides, based on the Sens_Mnt_Brut flag, whether Mnt_Brut should be used as-is or should be negated, and multiplies it by 1 or -1 as appropriate.

You have at least two problems with your approach. You're converting numbers to strings with an explicit format model, and then implicitly converting them back to numbers to sum them; which means (a) you're relying on your NLS settings for the implicit conversion, and (b) the strings you're forming can't be converted back anyway using your explicit model.

Let's start with some dummy data that would go into your first branch:

with dlc_pr_operation (Sens_Mnt_Brut, Mnt_Brut, Mnt_Comm_Tot_Ope, Num_Est_Remise) as (
  select 'D', 1234.56, 1, 2 from dual
)
select to_number('-' || TO_CHAR(PRO.Mnt_Brut,'99999999999,99')) from dlc_pr_operation pro;

ORA-01722: invalid number

If you look at the string it's forming you'll see:

with dlc_pr_operation (Sens_Mnt_Brut, Mnt_Brut, Mnt_Comm_Tot_Ope, Num_Est_Remise) as (
  select 'D', 1234.56, 1, 2 from dual
)
select '-' || TO_CHAR(PRO.Mnt_Brut,'99999999999,99') from dlc_pr_operation pro;

'-'||TO_CHAR(PRO
----------------
-          12,35

The conversion of that string fails because of all the white space. You could avoid that with a slightly modified format model:

with dlc_pr_operation (Sens_Mnt_Brut, Mnt_Brut, Mnt_Comm_Tot_Ope, Num_Est_Remise) as (
  select 'D', 1234.56, 1, 2 from dual
)
select to_number('-' || TO_CHAR(PRO.Mnt_Brut,'FM99999999999,99')) from dlc_pr_operation pro;

TO_NUMBER('-'||TO_CHAR(PRO.MNT_BRUT,'FM99999999999,99'))
--------------------------------------------------------
-12,35

but only if your NLS setting, and particularly NLS_NUMERIC_CHARACTERS, is right. If I ran that same query after doing alter session set nls_numeric_characters = '.,' then this would also get ORA-01722, because you've used a fixed comma. You can either explicitly alter your session, or switch from using a fixed , to the generic D decimal separator:

alter session set nls_numeric_characters = '.,';

with dlc_pr_operation (Sens_Mnt_Brut, Mnt_Brut, Mnt_Comm_Tot_Ope, Num_Est_Remise) as (
  select 'D', 1234.56, 1, 2 from dual
)
select to_number('-' || TO_CHAR(PRO.Mnt_Brut,'FM99999999999,99')) from dlc_pr_operation pro;

ORA-01722: invalid number

with dlc_pr_operation (Sens_Mnt_Brut, Mnt_Brut, Mnt_Comm_Tot_Ope, Num_Est_Remise) as (
  select 'D', 1234.56, 1, 2 from dual
)
select to_number('-' || TO_CHAR(PRO.Mnt_Brut,'FM99999999999D99')) from dlc_pr_operation pro;

TO_NUMBER('-'||TO_CHAR(PRO.MNT_BRUT,'FM99999999999D99'))
--------------------------------------------------------
                                                -1234.56

This is still doing implicit conversion back to numbers of course, throughout your query.

Using the modified query from the start of this answer (if you can remember back that far) instead and some more dummy data gives the output:

with dlc_pr_operation (Sens_Mnt_Brut, Mnt_Brut, Mnt_Comm_Tot_Ope, Num_Est_Remise) as (
  select 'D', 1234.56, 1, 2 from dual
  union all select 'X', 123.45, 2, 4 from dual
  union all select 'Y', 456.78, 3, 4 from dual
)
select SUM(
    CASE PRO.Sens_Mnt_Brut WHEN 'D' THEN -1 ELSE 1 END * PRO.Mnt_Brut
  ) OVER (PARTITION BY PRO.Num_Est_Remise)
    - SUM(PRO.Mnt_Comm_Tot_Ope) OVER(PARTITION BY PRO.Num_Est_Remise) as result
From dlc_pr_operation pro; 

    RESULT
----------
-1235,56  
575,23    
575,23

As there is no conversion your NLS settings don't matter now; except that the final result is formatted by the client using your session's decimal separator automatically, so it's still showing a comma separator in this example with the numeric characters set to ',.'. If I altered my session to '.,' instead I'd see:

    RESULT
----------
  -1235.56
    575.23
    575.23

Whether that's the result you actually want is less clear. It might be useful to include other columns, but you also need to check you really want the analytic functions, not just aggregates.

You said in a comment:

it's a number(16.0) but i want to put a comma before the 2 last numbers

That doesn't really make sense, unless you are storing financial amounts as integers - essentially as pence/cents rather than pounds/euros/dollars - but want to show them in a more friendly way; in which case you should be dividing them by 100, not mangling them as strings:

with dlc_pr_operation (Sens_Mnt_Brut, Mnt_Brut, Mnt_Comm_Tot_Ope, Num_Est_Remise) as (
  select 'D', 123456, 100, 2 from dual
  union all select 'X', 12345, 200, 4 from dual
  union all select 'Y', 45678, 300, 4 from dual
)
select (SUM(
    CASE PRO.Sens_Mnt_Brut WHEN 'D' THEN -1 ELSE 1 END * PRO.Mnt_Brut
  ) OVER (PARTITION BY PRO.Num_Est_Remise) / 100)
    - (SUM(PRO.Mnt_Comm_Tot_Ope) OVER(PARTITION BY PRO.Num_Est_Remise) / 100) as result
From dlc_pr_operation pro; 

    RESULT
----------
  -1235.56
    575.23
    575.23

... assuming (again) that both values being summed are stored like that; you could also subtract the two sums and divide the total by 100 if you prefer.

Upvotes: 3

Related Questions