Need Help with query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lenygold - 05 Apr 2008 02:28 GMT

One column table:

ALL_SUM
--------------------------------------------------------------------
73237155+73240240+73243230+73249335

73237155+73240240+73246345

73237155+73240240+73246345+73249335
....................................................................

I need to sum every 2 digits prior '+' sign plus last 2 digits:

For row 1: 55 + 40 + 30 + 35
For row 2: 55 + 40 + 45
For row 3: 55 + 40 + 45 + 35
Any idea how to do this?
This is realy urgent.
Thank's in advance.

Reply to this Message

Lennart - 05 Apr 2008 04:54 GMT

> One column table:
>
[quoted text clipped - 15 lines]
> This is realy urgent.
> Thank's in advance.

See Knut's post for a function that will be of help here:

http://tinyurl.com/3hp487.

CREATE FUNCTION elements ( string varchar(100) )
RETURNS TABLE ( ordinal INTEGER, index INTEGER )
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
WITH t(ordinal, index) AS
( VALUES ( 0, 0 )
UNION ALL
SELECT ordinal+1, COALESCE(NULLIF(
LOCATE('+', string, index+1), 0), LENGTH(string)
+1)
FROM t
-- to prevent a warning condition for infinite recursion
WHERE ordinal < 500 AND
LOCATE('+', string, index+1) <> 0 )
SELECT ordinal, index
FROM t

You will have to modify it slightly to fit your problem, Given that
the solution is easy:

db2 "create table T (all_sum varchar(50) not null primary key)"
db2 "insert into T values ('73237155+73240240+73243230+73249335')"
db2 "insert into T values ('73237155+73240240+73246345')"

db2 "select all_sum, sum(int(substr(all_sum, index-2 ,2))) from T,
TABLE (elements( T.all_sum )) x where index > 0 group by all_sum"

ALL_SUM 2
-------------------------------------------------- -----------
73237155+73240240+73243230+73249335 125
73237155+73240240+73246345 95

2 record(s) selected.

Note that the sum is not correct since the last part of all_sum is not
taken into concideration, but you get the idea.

A word of caution, if there are duplicate all_sum (I added the primary
key to ensure that there is not) you will get strange results

HTH
/Lennart

Reply to this Message

Lennart - 05 Apr 2008 07:35 GMT

> > One column table:
>
[quoted text clipped - 65 lines]
> HTH
> /Lennart

On second thought, you can use the function as is by adding a '+' at
the end of all_sum as in:

db2 "select all_sum, sum(int(substr(all_sum, index-2 ,2))) from T,
TABLE (elements( rtrim(T.all_sum) || '+')) x where ordinal > 0 group
by all_sum"

ALL_SUM 2
-------------------------------------------------- -----------
73237155+73240240+73243230+73249335 160
73237155+73240240+73246345 140
73237155+73240240+73246345+73249335 175

/L

Reply to this Message

lenygold - 05 Apr 2008 21:37 GMT

Thank You Lennart. It is working perfect.

>> > One column table:
>>
[quoted text clipped - 16 lines]
>
>/L

Reply to this Message

lenygold - 02 May 2008 23:05 GMT

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?

>> One column table:
>>
[quoted text clipped - 51 lines]
>HTH
>/Lennart

Reply to this Message