CODEWITHSUNDEEP
sqldb2ibm-midrange
Lele
Lele

Reputation: 33

AS400 DB2 query math expression in Select

I have not done DB2 queries for a while so I am having issues with a math expression in my Select statement. It does not throw an error but I get the wrong result. Can someone tell me how DB2 evaluates the expression? Part of my Select is below.

The values are:

It should read like (100 * 1) * (1 - (.000274 * 1268)) = 65.2568

SELECT Value1,
       value2,
       (CASE
            WHEN (T1.POINTS * T2.INVOLVEPOINTS) * (1 - .000274 * DAYS(CURRENT DATE) - DAYS(T1.FROMDT)) >= 0 THEN (T1.POINTS * T2.INVOLVEPOINTS) * (1 - .000274 * DAYS(CURRENT DATE) - DAYS(T1.FROMDT))
            ELSE 0
        END) AS POINTSTOTAL
FROM TABLE1;

Upvotes: 0

Views: 2657

Answers (2)

James Allman
James Allman

Reputation: 41168

The parenthesis are not enforcing the correct precedence of operations and the join declaration is missing. In addition you can use the MAX scalar function instead of the repetitive CASE statement.

Here is a proof using common table expressions to simulate the source data:

with 
t1 (value1, points, fromdt) 
    as (select 1, 100, '2010-01-28' from sysibm.sysdummy1),
t2 (value2, involvepoints) 
    as (select 2, 1 from sysibm.sysdummy1)
select value1, value2, 
    max(0, t1.points * t2.involvepoints * 
    (1 - .000274 * (DAYS('2013-07-19') - DAYS(t1.fromdt)))) as pointstotal
    from t1, t2;

The result is:

VALUE1 VALUE2 POINTSTOTAL
------ ------ -----------
1      2      65.256800

Upvotes: 1

mustaccio
mustaccio

Reputation: 18945

Did you mean this?

...
(T1.POINTS * T2.INVOLVEPOINTS) * (1 - .000274 * ( DAYS(CURRENT DATE) - DAYS(T1.FROMDT) ) ) 
...

Note the extra pair of parentheses around the subtraction of dates. Normally multiplication takes precedence over addition, so in your original query you multiply today's date by 0.000274, subtract that from 1, then subtract the value of FROMDT from the result.

Curiously, you have those parentheses in your explanation, but not in the actual formula.

Upvotes: 0

Related Questions