Peter G. Peter G. - 1 year ago 129
SQL Question

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

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 reality I want to use somthing 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.

Answer Source

You could use a CASE statement

SELECT Total = CASE WHEN number_of_items > 0 
               THEN total/number_of_items
               ELSE total END
FROM   xxx
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download