max

miii · #1 (**permalink**) 02-02-04, 10:53

Hi
A table with fields A, B, C and D. I have to perform the following calculation:
D=A- max(B,C)
I have to substract B or C from A.
How to set the calculated formula?
Or how to calculate it with a trigger?

Thank you in advance!

sathyaram_s · #2 (**permalink**) 02-02-04, 11:06

AFAIK, there is no max function to comapre different columns in the same row ..

But, I assume you should be able to write a SQL UDF very easily

Cheers
Sathyaram

GertK · #3 (**permalink**) 02-02-04, 13:28

Quote:

Originally posted by miii
Hi
A table with fields A, B, C and D. I have to perform the following calculation:
D=A- max(B,C)
I have to substract B or C from A.
How to set the calculated formula?
Or how to calculate it with a trigger?

Thank you in advance!

You can use the case statement, assuming you're using DB2 Windows/Unix (don't know about DB2/OS390)

Also assuming that A is the key column this should work:

Code:

update t1 x set d = 

     (select 
                case
                   when b>c then a-b
                else
                   a-c
                end

         from t1 y
       where x.a = y.a )
          ;

Kind regards, Gert

miii · #4 (**permalink**) 02-03-04, 02:43

Thank You!

Internet Services

Web Development

Digital Marketing

Consumer Tech