I am trying to create a simple function that returns the least value passed to the function... but when running it doesn't work it just returns the same values I pass into the function 15,2,3 which should return 2... can someone help me and tell me why it doesn't work?

CREATE OR REPLACE
function
 GET_LEAST_VALUE(in_numbers IN VARCHAR2)
 RETURN VARCHAR2
 IS
 vReturn varchar2(50);
 
   BEGIN
       vReturn := least(in_numbers);
     return vReturn;
   END;

CodePudding user response：

As commented, it can't work as you're actually passing a string to the function.

Here's one option - it splits that string into rows; regexp_substr returns a string (again) so - trim it first (to remove possible spaces) and apply to_number (otherwise you'd compare strings, and that's different from comparing numbers). Finally, apply the min aggregate function because least won't work if you passed more than a single number (you'd get too_many_rows error).

SQL> CREATE OR REPLACE FUNCTION get_least_value (in_numbers IN VARCHAR2)
  2     RETURN VARCHAR2
  3  IS
  4     vreturn  VARCHAR2 (50);
  5  BEGIN
  6     WITH
  7        temp
  8        AS
  9           (    SELECT TO_NUMBER (TRIM (REGEXP_SUBSTR (in_numbers,
 10                                                       '[^,] ',
 11                                                       1,
 12                                                       LEVEL))) val
 13                  FROM DUAL
 14            CONNECT BY LEVEL <= REGEXP_COUNT (in_numbers, ',')   1)
 15     SELECT MIN (val)
 16       INTO vreturn
 17       FROM temp;
 18
 19     RETURN vreturn;
 20  END;
 21  /

Function created.

SQL> SELECT get_least_value ('15,2,3') result FROM DUAL;

RESULT
--------------------------------------------------------------------------------
2

SQL>

CodePudding user response：

CREATE OR REPLACE FUNCTION get_least_value (in_numbers IN VARCHAR2) RETURN NUMBER IS
    w_result NUMBER ;
BEGIN
    SELECT MIN(value) INTO w_result FROM 
    json_table (
        '[' || in_numbers || ']',
        '$[*]'
        columns (
            value NUMBER PATH '$'
        )
    )
    ;
    RETURN w_result ;
END ;
/


SELECT get_least_value('4,1,9,0,-5') from dual ;


-5

CodePudding user response：

Why it doesn't work?

It does work as you pass in a single string and LEAST finds the least value when comparing that single string to nothing else so it returns that single string; which is exactly what LEAST is intended to do.

However, it is not what you expect it to do.

Why does it not do what I expect?

You are expecting '15,2,3' to be evaluated as a list of numbers (i.e. LEAST(15,2,3)) but it is not a list of numbers it is a single string literal that happens to look, to a human, like a list of numbers but, to the SQL engine, it is actually only one string value and will be evaluated as LEAST('15,2,3').

How to fix it?

You need to either pass in multiple values via a collection (for example, the built-in SYS.ODCINUMBERLIST varray collection type):

CREATE FUNCTION GET_LEAST_VALUE(
  in_numbers IN SYS.ODCINUMBERLIST
) RETURN NUMBER
IS
  v_least NUMBER;
BEGIN
  SELECT MIN(column_value)
  INTO   v_least
  FROM   TABLE(in_numbers);

  RETURN v_least;
END;
/

and then call it as:

SELECT get_least_value(SYS.ODCINUMBERLIST(15,2,3))
FROM   DUAL;

or

BEGIN
  DBMS_OUTPUT.PUT_LINE(get_least_value(SYS.ODCINUMBERLIST(15,2,3)));
END;
/

fiddle

Or alternatively, you need to split the string into separate values and then find the minimum of those values.