Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    Question Unanswered: Scalar MIN and MAX for UDB?

    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...).

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by hurmavi
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    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

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    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 @
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    ... or hold your values in a CTE and use the column function.

    Code:
    with temptable (col1) as
    (
     values  (1),(2),(0),(9)
    )
    select min(col1) from temptable

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hold the phone...are you saying V8 doesn't have MIN and MAX?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Brett Kaiser
    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)
    --
    Jonathan Petruk
    DB2 Database Consultant

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well it's glad that I didn't know that, now that it's going away...

    Because it not ANSI?

    Code:
    SELECT MAX(NAME,TSNAME) FROM SYSIBM.SYSTABLES;
    Amazing...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Brett Kaiser
    Well it's glad that I didn't know that, now that it's going away...

    Because it not ANSI?

    Code:
    SELECT MAX(NAME,TSNAME) FROM SYSIBM.SYSTABLES;
    Amazing...
    I think it's sticking around in z/OS, but it's never existed that I can recall on Linux/Unix/Windows.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •