Do calculation in Char filed

[situ]

Hello all,

is there any way, can we work around with mathematical calculation in
char filed, like if i am storing '3+5' in character field, can make a
calculation on the above field and get the values 8 .

thanks and regards
situ

[Knut Stolze]

situ wrote:

Hello all,

is there any way, can we work around with mathematical calculation in
char filed, like if i am storing '3+5' in character field, can make a
calculation on the above field and get the values 8 .

Not natively in DB2. You need something that parses the string, converts
the numbers to an integer or floating point data type, and then calculates
your result. Writing such a UDF should be rather trivial, given that there
are lots of C/Java examples out there that implement a "calculator". Just
wrap those into a UDF.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[situ]

On May 22, 1:34 pm, Knut Stolze <[e-mail]:

situ wrote:

Hello all,

is there any way, can we work around with mathematical calculation in
char filed, like if i am storing '3+5' in character field, can make a
calculation on the above field and get the values 8 .

Not natively in DB2. You need something that parses the string, converts
the numbers to an integer or floating point data type, and then calculates
your result. Writing such a UDF should be rather trivial, given that there
are lots of C/Java examples out there that implement a "calculator". Just
wrap those into a UDF.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Thanks ...

[Serge Rielau]

Maybe hooking up a LISP interpreter would be the easiest...

Cheers
Serge

PS: This goes way beyond "duck-typing" as I know it....

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Knut Stolze]

Serge Rielau wrote:

Maybe hooking up a LISP interpreter would be the easiest...

Cheers
Serge

PS: This goes way beyond "duck-typing" as I know it....

Actually, there is a quite simple way to do that with an external UDF. The
following (untested) C code is still missing proper error/NULL handling and
it assumes that "bc" is installed at the DB2 server.

void SQL_API_FN calculator(
SQLUDF_VARCHAR *formula,
SQLUDF_DOUBLE *result,
SQLUDF_NULLIND *formula_ind,
SQLUDF_NULLIND *result_ind,
SQLUDF_TRAIL_ARGS)
{
FILE *pipe = NULL;
char str[1000] = { '\0' };

sprintf(command, "echo \"%s\" | bc", formula);
pipe = popen("bc", "r");
fgets(str, sizeof str, pipe);
pclose(pipe);
*result = strtod(str);
*result_ind = 0;
}

Use this as entry point for an external UDF with a VARCHAR(980) as input
parameter and returning a DOUBLE. You can choose whichever maximum length
suits you - just adjust the size of the char array in the C code.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[swami]

Is there any function for converting String to number & vise versa ?

[Serge Rielau]

swami wrote:

Is there any function for converting String to number & vise versa ?

Of course: CHAR() and INT(), DOUBLE(), DECIMAL(), ...
Also you can use the "CAST-specification".

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Tonkuma]

Make UDF in that let DB2 do calculation by using dynamic SQL.
The weak point of this strategy is need to specify MODIFIES SQL DATA
because of using dynamic SQL. Therefor, the UDF must be TABLE UDF.
There are some restriction to use TABLE UDF.
Followings are one example:

CREATE FUNCTION Calculate (inStr VARCHAR(100))
RETURNS TABLE (Result FLOAT)
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE Result FLOAT;
CALL Calculate(inStr, Result);
RETURN VALUES Result;
END
!

CREATE PROCEDURE Calculate (IN inStr VARCHAR(100)
,OUT Result FLOAT)
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN NOT ATOMIC
DECLARE CalcSelect VARCHAR(130) DEFAULT '';
DECLARE PrepSelect STATEMENT;
DECLARE C1 CURSOR FOR PrepSelect;
SET CalcSelect = 'SELECT '||COALESCE(inStr,'CAST(NULL AS FLOAT)')
||' FROM SYSIBM.SYSDUMMY1';
PREPARE PrepSelect FROM CalcSelect;
OPEN C1;
FETCH C1 INTO Result;
CLOSE C1;
END
!

Sample execution:
-------------------- Commands Entered --------------------
SELECT deptno
, mgrno
, INT(Result) INT_Result
FROM Department D
, TABLE( Calculate(mgrno||'*10+5') ) C!
----------------------------------------------------------

DEPTNO MGRNO INT_RESULT
------ ------ -----------
A00 000010 105
B01 000020 205
C01 000030 305
D01 - -
D11 000060 605
D21 000070 705
E01 000050 505
E11 000090 905
E21 000100 1005

9 record(s) selected.