On May 3, 12:17 am, "lenygold via DBMonster.com" <u41482@[EMAIL PROTECTED]
> 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
|
