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;
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>
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
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.
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').
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;
/
Or alternatively, you need to split the string into separate values and then find the minimum of those values.
It could be done in an old way with one user defined function spliting a string by delimiter and returning an array of elements. In that case the simple plsql fuction could be like here:
create or replace Function Get_Least(P_LIST VARCHAR2, P_DELIMITER VARCHAR2 := ',') Return NUMBER AS
BEGIN
Declare
elements STRING_ARRAY := STRING_ARRAY();
least_element Number(12) := 999999999999;
Begin
elements := split(P_LIST , P_DELIMITER);
For i In 1..elements.count Loop
If To_Number(elements(i)) < least_element Then
least_element := To_Number(elements(i));
End If;
End Loop;
RETURN least_element;
End;
END get_Least;
Here are the user defined type STRING_ARRAY and SPLIT() function:
create or replace TYPE STRING_ARRAY AS
VARRAY(4000) OF VARCHAR2(1000);
/
create or replace FUNCTION split ( csvString IN VarChar2, delimiter IN VarChar2 := ',')
RETURN STRING_ARRAY AS
BEGIN
Declare
elements STRING_ARRAY := STRING_ARRAY();
CURSOR c IS
SELECT COL1
FROM ( SELECT INDX, MY_STR1, COL1_ELEMENTS, COL1
FROM ( SELECT 0 "INDX", COL1 "MY_STR1", COL1_ELEMENTS, COL1
FROM(
SELECT
REPLACE(COL1, delimiter || ' ', delimiter) "COL1",
Trim(Length(Replace(COL1, delimiter || ' ', delimiter))) - Trim(Length(Translate(REPLACE(COL1, delimiter || ' ', delimiter), 'A' || delimiter, 'A'))) + 1 "COL1_ELEMENTS"
FROM (SELECT csvString "COL1" FROM DUAL)
)
)
MODEL
DIMENSION BY(0 as INDX)
MEASURES(COL1, COL1_ELEMENTS, CAST('a' as VarChar2(4000)) as MY_STR1)
RULES ITERATE (1000) --UNTIL (ITERATION_NUMBER <= COL1_ELEMENTS[ITERATION_NUMBER + 1]) -- If you don't know the number of elements this should be bigger then you aproximation. Othewrwise it will split given number of elements
(
COL1_ELEMENTS[ITERATION_NUMBER + 1] = COL1_ELEMENTS[0],
MY_STR1[0] = COL1[CV()],
MY_STR1[ITERATION_NUMBER + 1] = SubStr(MY_STR1[ITERATION_NUMBER], InStr(MY_STR1[ITERATION_NUMBER], delimiter, 1) + Length(delimiter)),
COL1[ITERATION_NUMBER + 1] = SubStr(MY_STR1[ITERATION_NUMBER], 1, CASE WHEN InStr(MY_STR1[ITERATION_NUMBER], delimiter) <> 0 THEN InStr(MY_STR1[ITERATION_NUMBER], delimiter)-1 ELSE Length(MY_STR1[ITERATION_NUMBER]) END)
)
)
WHERE INDX > 0 And INDX <= COL1_ELEMENTS; -- And COL1 Is Not Null;
cStr VarChar2(1000);
i Number := 1;
Begin
If c%ISOPEN Then CLOSE c; End If;
OPEN c;
LOOP
FETCH c Into cStr;
EXIT WHEN c%NOTFOUND;
elements.extend;
elements(i) := cStr;
i := i + 1;
END LOOP;
CLOSE c;
RETURN elements;
End;
END split;
/
Test call with string containing comma separated list of numbers
SET SERVEROUTPUT ON
Declare
least_one NUMBER(3);
Numbers_List VARCHAR2(100) := '123, 46, 756, 3, 918, 4';
Begin
least_one := GET_LEAST(Numbers_List);
DBMS_OUTPUT.PUT_LINE(least_one);
End;
--
-- Result:
--
-- anonymous block completed
-- 3
SPLIT() function could split any string by any delimiter (including space character for splitting words in a sentence). It works with db versions without regexp too.
Example: - the same numbers separated by 'xo' - same result
SET SERVEROUTPUT ON
Declare
least_one NUMBER(3);
Numbers_List VARCHAR2(100) := '123xo46xo756xo3xo918xo4';
Begin
least_one := GET_LEAST(Numbers_List, 'xo');
DBMS_OUTPUT.PUT_LINE(least_one);
End;
--
-- Result:
--
-- anonymous block completed
-- 3
Regards...
