Error: ORA-01722: Invalid Number when use Sum()

[kamkaro]

Hi All,
I am having difficulties for sum() function, getting ORA-01722: Invalid Number error.

Problem:
My following simple query working fine in both Oracle(Toad) and Crystal Report (SQL Expression)
select
sa.acct_id,
adjc.char_val KWH
from cisadm.ci_ft ft, cisadm.ci_sa sa, cisadm.ci_adj_char adjc
where ft.parent_id = 'FIT'
and sa.sa_id = ft.sa_id
and adjc.adj_id = ft.sibling_id
and adjc.char_type_cd= 'KWH'
and trunc(ft.freeze_dttm) < to_date('2013-06-30', 'YYYY-MM-DD')

Now when I am trying to do SUM KWH for each Acct_ID, getting invalid Number error in Crystal report (SQL Expression) but fine in Oracle (Toad).

My New Query:
select
fit.acct_id,
sum(fit.kwh) kwh,
from
(
select
sa.acct_id,
adjc.char_val KWH
from cisadm.ci_ft ft, cisadm.ci_sa sa, cisadm.ci_adj_char adjc
where ft.parent_id = 'FIT'
and sa.sa_id = ft.sa_id
and adjc.adj_id = ft.sibling_id
and adjc.char_type_cd= 'KWH'
and trunc(ft.freeze_dttm) < to_date('2013-06-30', 'YYYY-MM-DD')
) fit
group by fit.acct_id,fit.kwh;

Issue:
Column adjc.char_val has Varchar2 datatype and i have to convert into Number. when I used to_number(adjc.char_val)kwh function in my first query, got ORA-01722: Invalid Number error and without to_number function my first query is okay. when I use SUM function in my Second query, I am getting same ORA-01722: Invalid Number error again. I tried to use store Procedure, but getting same error.

Could you please let me know any workaround how do I get Sum value for each Acct_ID.

Appreciate that.

Thanks.

 

[SkipVought]

Hi,

Just group by acct_id only

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kamkaro]

Hi Skip,
Group by acct_id only, still getting same error when I am using Sum() function for KWH. Any other thoughts?

Thanks.

 

[SkipVought]

Please post the list of unique values that this query returns?

select distinct
  adjc.char_val KWH 

from
  cisadm.ci_ft ft
, cisadm.ci_sa sa
, cisadm.ci_adj_char adjc

where ft.parent_id = 'FIT' 
  and sa.sa_id     = ft.sa_id 
  and adjc.adj_id  = ft.sibling_id 
  and adjc.char_type_cd = 'KWH' 
  and trunc(ft.freeze_dttm) < to_date('2013-06-30', 'YYYY-MM-DD')

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kamkaro]

Hi Skip,
After run the above query, I got the total 93 records, for your reference, I am pasting few records below. If you want so I will past all 93 records. User wants to sum up KWH (amount) for each Account ID if there are more than one (same) Account ID. Account ID is not PK though.

Thanks.


KWH
-1024.691
-17490.26
-11138.43
-59135.343
-45161.96
-45338.9
-112178.4
-104710.3
-5272.497
-2774.232
-4524.66
-2449.899
-33222.84

 

[SkipVought]

So EVERY ONE of the 93 values are negative numbers?

I's try...

select 
  sa.acct_id[b]
, Sum(to_number(trim(adjc.char_val))) KWH [/b]

from
  cisadm.ci_ft ft
, cisadm.ci_sa sa
, cisadm.ci_adj_char adjc

where ft.parent_id = 'FIT' 
  and sa.sa_id = ft.sa_id 
  and adjc.adj_id = ft.sibling_id 
  and adjc.char_type_cd= 'KWH' 
  and trunc(ft.freeze_dttm) < to_date('2013-06-30', 'YYYY-MM-DD')

group by
  sa.acct_id

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kamkaro]

Skip,
Still getting same error "ORA-01722: Invalid Number". It seems to me sum() can't convert varchar2 to Number data type in crystal report .

Thanks.

 

[SkipVought]

please post your current sql code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kamkaro]

Hi Skip,
when I am using my following Main query, getting Invalid Number error;

select
fit.acct_id,
sum(fit.kwh) kwh
from
(
select
sa.acct_id,
to_number(adjc.adhoc_char_val) kwh
from cisadm.ci_ft ft, cisadm.ci_sa sa, cisadm.ci_adj_char adjc
where ft.parent_id = 'FIT'
and sa.sa_id = ft.sa_id
and adjc.adj_id = ft.sibling_id
and adjc.char_type_cd= 'KWH'
and trunc(ft.freeze_dttm) > to_date('2013-06-01', 'YYYY-MM-DD')
) fit
group by fit.acct_id


2) After removed Outer select (Sum and group by) from the above query and just run following inner select, getting same Invalid Number error.

select
sa.acct_id,
to_number(adjc.adhoc_char_val) kwh
from cisadm.ci_ft ft, cisadm.ci_sa sa, cisadm.ci_adj_char adjc
where ft.parent_id = 'FIT'
and sa.sa_id = ft.sa_id
and adjc.adj_id = ft.sibling_id
and adjc.char_type_cd= 'KWH'
and trunc(ft.freeze_dttm) > to_date('2013-06-01', 'YYYY-MM-DD')

3) After remove to_Number() from adjc.adhoc_char_val kwh column, displaying result now.

select
sa.acct_id,
adjc.adhoc_char_val kwh
from cisadm.ci_ft ft, cisadm.ci_sa sa, cisadm.ci_adj_char adjc
where ft.parent_id = 'FIT'
and sa.sa_id = ft.sa_id
and adjc.adj_id = ft.sibling_id
and adjc.char_type_cd= 'KWH'
and trunc(ft.freeze_dttm) > to_date('2013-06-01', 'YYYY-MM-DD')


Note:
Just let you know that I do not have any issue on Toad, only Crystal report's Command Expert does not work.

Thanks.