SEO

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?

--
Message posted via http://www.dbmonster.com

On May 3, 12:17 am, "lenygold via DBMonster.com" <u41482@uwe> wrote:
> 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?

For one thing, you can't do:

int(substr(all_sum, index-1,1))

because there are numbers with more than 1 digit.

/Lennart