Hi!

DB2 z/OS V.7 has scalar funtions MIN and MAX*). Now DB2 UDB seems not to have those. So we have problem to port application.

Any ideas? These functions can be implemented by UDF, right? Are there any freeware or commercial packages?

Cheers, Bill



*) Normal column function use this syntax: MAX( ALL/ /DISTINCT expression).
Scalar function MAX has this syntax: MAX(expression, expression...).

Can you use the CASE statement?

ie. SELECT CASE WHEN A > B THEN A ELSE B END
FROM MYTABLE;

Kind of ugly.

OK!

I think, CASE would work, allright.

However, since we have more than two columns to choose, result can really be pretty much ugly, indeed. Thank you, anyway - we'll use it, if no better solution will be found.

Cheers, Bill

You could always bury it into a function:

CREATE FUNCTION SCHEMA1.NEWMAX
(VAL1 INTEGER, VAL2 INTEGER)
RETURNS INTEGER
LANGUAGE SQL
BEGIN ATOMIC
RETURN CASE WHEN VAL1 > VAL2 THEN VAL1
ELSE VAL2
END;
END @

... or hold your values in a CTE and use the column function.

Hold the phone...are you saying V8 doesn't have MIN and MAX?

It has MIN and MAX the column functions:
SELECT MAX(A) FROM MYTABLE;
(returns the biggest A)

But not the scalar function:
SELECT MAX(A,B) FROM MYTABLE;
(returns the larger of A or B for *each* columns)

Well it's glad that I didn't know that, now that it's going away...

Because it not ANSI?

Amazing...

I think it's sticking around in z/OS, but it's never existed that I can recall on Linux/Unix/Windows.