Need help with Knut's function for summing values in rows
here is my example:
WITH T1 (C1) AS
(VALUES ('1'),
('1.1'),
('1.2.1.2'),
('1.10.1'),
('1.10.1.2.2'),
('1.22.99.1'),
('1.2'),
('1.3.3.7.4'),
('1.3.2.7'),
('1.4.1')),
T2(ALL_SUM) AS
(SELECT REPLACE(C1,'.','+') FROM T1)
select all_sum, sum(int(substr(all_sum, index-1,1))) FROM T2,
TABLE (elements(RTRIM(T2.all_sum)|| '+')) x
where ordinal > 0
group by all_sum
ORDER BY 1;
output:
ALL_SUM 2
---------- -----------
1 1
1+1 2
1+10+1 2
1+10+1+2+2 6
1+2 3
1+2+1+2 6
1+22+99+1 13
1+3+2+7 13
1+3+3+7+4 18
1+4+1 6
10 record(s) selected.
why it is summing wrong?
Lennart - 03 May 2008 16:22 GMT
> Need help with Knut's function for summing values in rows
> here is my example:
[quoted text clipped - 34 lines]
> 10 record(s) selected.
> why it is summing wrong?
For one thing, you can't do:
int(substr(all_sum, index-1,1))
because there are numbers with more than 1 digit.
/Lennart