CODEWITHSUNDEEP
sqlexpressionevalinformix
Dominik00000
Dominik00000

Reputation: 331

Eval math expression string with informix

I need to calculate a math expression from a string.

The String looks like "1.00*1.00*4.00"

How can I do that?

Something like 

Select field1, field2, EVAL("EXPRESSION") as float from table

would be great!

Upvotes: 0

Views: 234

Answers (1)

Ricardo Henriques
Ricardo Henriques

Reputation: 1086

Let us create a test case:

CREATE TABLE tab1(
    col1 DECIMAL(10,2),
    col2 DECIMAL(10,2),
    col3 DECIMAL(10,2),
    col4 VARCHAR(250)
);

INSERT INTO tab1 VALUES (1.0, 1.0, 4.0, "1.00*1.00*4.00")

If you are building the expression from columns or even passing the string it is straightforward:

SELECT  col1 * col2 * col3  AS from_field,
        1.00*1.00*4.00      AS from_string
FROM    tab1;

If the value is stored on a column you have to do it programmatically, here is an example of an SPL:

CREATE FUNCTION get_eval()
    RETURNING DECIMAL(10,2) AS eval;

    DEFINE stmt     VARCHAR(250);
    DEFINE eval_str VARCHAR(250);
    DEFINE eval_dec DECIMAL(10,2);

    FOREACH cur FOR 
        SELECT  col4
        INTO    eval_str
        FROM    tab1 

        LET stmt = "SELECT " || eval_str || " as eval FROM sysmaster:sysdual INTO TEMP tmp_eval WITH NO LOG;";
        EXECUTE IMMEDIATE stmt;

    END FOREACH

    FOREACH eval FOR
        SELECT  eval
        INTO    eval_dec
        FROM    tmp_eval
            RETURN eval_dec WITH RESUME;
    END FOREACH

    DROP TABLE tmp_eval;

END FUNCTION;

EXECUTE FUNCTION get_eval();

Upvotes: 4

Related Questions