How to calculate the maximum of two numbers in Oracle SQL select?

user_280314 0
Viewed 93822

This should be simple and shows my SQL ignorance:

SQL> select max(1,2) from dual;
select max(1,2) from dual
       *
ERROR at line 1:
ORA-00909: invalid number of arguments

I know max is normally used for aggregates. What can I use here?

In the end, I want to use something like

select total/max(1,number_of_items) from xxx;

where number_of_items is an integer and can be 0. I want to see total also in this case.

max oracle sql
8 Answers

First, create the function

 CREATE OR REPLACE FUNCTION max_finder(n1 in number, n2 in number)
return number
AS
    n3_max number;
BEGIN
    IF n1<=n2 THEN
        n3_max:=n2;
    ELSE 
        n3_max:=n1;
    END IF;
    return n3_max;
END;
/

Then execute it

DECLARE
    n3_max number;
BEGIN
    n3_max:=max_finder(5,13);
    dbms_output.put_line(n3_max);
END;
/
user_115062880
Related