On Apr 5, 3:28 am, "lenygold via DBMonster.com" <u41482@uwewrote:
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.
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
