Is there a 'greatest' function in db2?

Question

I found in MYSQL and apparently other database engines that there is a "greatest" function that can be used like: greatest(1, 2, 3, 4), and it would return 4. I need this, but I am using IBM's DB2. Does anybody know of such an equivalent function, even if it only accepts 2 parameters?

I found somewhere that MAX should do it, but it doesn't work... it only works on selecting the MAX of a column.

If there is no such function, does anybody have an idea what a stored procedure to do this might look like? (I have no stored procedure experience, so I have no clue what DB2 would be capable of).

Answer 1

Two options:

1. What about sorting the column in descending and grabbing the top 1 row?

2. According to my "SQL Pocket Guide", MAX(x) returns the greatest value in a set.

UPDATE: Apparently #1 won't work if you are looking at columns.

Answer 2

Why does MAX not work for you?

select max(1,2,8,3,1,7) from sysibm.sysdummy1

gives me

    1
    ---------------
                 8

      1 record(s) selected.

Answer 3

As Dave points out, MAX should work as it's overloaded as both a scalar and a column function (the scalar takes 2 or more arguments). This is the case in DB2 for LUW, DB2 for z/OS and DB2 for i5/OS. What exact version and platform of DB2 are you using, and what is the exact statement you are using? One of the requirements of the scalar version of MAX is that all the arguments are "compatible" - I suspect there may be a subtle type difference in one or more of the arguments you're passing to the function.

Answer 4

On Linux V9.1, the "select max (1,2,3) ..." gives -

SQL0440N No authorized routine named "MAX" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

It is a scalar function requiring either a single value or a single column name. On z/os, it behaves differently.

However, It does work as expected on Linux 9.5.

Answer 5

It sounds crazy, but no such function exists in DB2, at least not in version 9.1. If you want to select the greater of two columns, it would be best to use a case expression.

You can also define your own max function. For example:

create function importgenius.max2(x double, y double)
returns double
language sql
contains sql
deterministic
no external action
begin atomic
    if y is null or x >= y then return x;
    else return y;
    end if;
end

Defining the inputs and outputs as doubles lets you take advantage of type promotion, so this function will also work for integers. The "deterministic" and "no external action" statements help the database engine optimize use of the function.

If you want another max function to work for character inputs, you'll have to give it another name.