Always returning Null: Using Select Statement in creating a function in oracle SQL developer

Question

FOR ANYONE HAVING THE SAME PROBLEM, I WAS ABLE TO SOLVE THIS BY REMOVING '_' IN MY VARCHAR2 PARAMETER NAME

I need help in this, I've been stuck in here for too long. I have tried multiple things already. Would appreciate any troubleshoot.

I have an existing table where the rows are the types of KPIs, and the rows are the targets on multiple target levels.

KPI	target_50	target_100	target_150
KPI A	5	10	20
KPI B	10	30	50

Now, I am creating a function that requires two parameters: the raw score and the KPI type. For example: KPI(6,'KPI A') would return 50% KPI(10,'KPI 1') would return 100% KPI(9,'KPI B') would return 0% KPI(100,'KPI B') would return 150%

This is my script:

create or replace FUNCTION KPI(RAW_SCORE in NUMBER, KPI_TYPE in VARCHAR2)

RETURN NUMBER AS

ACTUAL_SCORE NUMBER;

BEGIN

    select case when RAW_SCORE >= target_150 then 1.50
                when RAW_SCORE >= target_100 then 1.00
                when RAW_SCORE >= target_50 then 0.50
            else 0
            end into ACTUAL_SCORE
    from TARGETS_TABLE
    where TARGETS_TABLE.KPI = KPI_TYPE;
RETURN ACTUAL_SCORE;

However, this always returns NULL. I have tried running the same SELECT statement and manual input the raw_score and kpi_type. It returns the expected value. But the function always returns null whatever I input.

Here is the script for the targets table:

  CREATE TABLE "TARGETS_TABLE" 
   (    "KPI" VARCHAR2(300 BYTE),  
    "TARGET_50" NUMBER(38,14), 
    "TARGET_100" NUMBER(38,14), 
    "TARGET_150" NUMBER(38,17)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA" ;

I would appreciate any insights and troubleshooting that I could try. Thank you so much.

I tried creating a user-defined function with a SELECT statement inside where the two input parameters will be used. I'm expecting a value to be returned, but it is always null.

Answer 1

The KPI_TYPE value that you are passing to the function does not exist in the TARGETS_TABLE table and the function is raising a NO DATA FOUND exception when it tries to use SELECT ... INTO ... and the select matches zero rows.

The SQL query is receiving the NO DATA FOUND exception and understands that to mean that the returned value should be NULL and silently consumes the exception and substitutes NULL in its place.

For example, if you have the data:

CREATE TABLE targets_table (KPI, target_50, target_100, target_125, target_150) AS
SELECT 'KPI A',  5, 10, 15, 20 FROM DUAL UNION ALL
SELECT 'KPI B', 10, 30, 40, 50 FROM DUAL;

Then:

SELECT KPI(100, 'KPI C') FROM DUAL;

Outputs:

KPI(100,'KPIC')
null

But performing the same function call in PL/SQL (rather than in SQL):

DECLARE
  v_kpi NUMBER;
BEGIN
  v_kpi := KPI(100, 'KPI C');
END;
/

Shows the underlying exception:

ORA-01403: no data found
ORA-06512: at "FIDDLE_PTZQEOEAQSCSHGUJATEJ.KPI", line 9
ORA-06512: at line 4

---

The solution is to pass a KPI_TYPE value to the function that actually exists in the table.

---

However, if you have create the table using the CHAR data-type for the KPI columns:

CREATE TABLE targets_table (
  KPI        CHAR(10),
  target_50  NUMBER,
  target_100 NUMBER,
  target_125 NUMBER,
  target_150 NUMBER
);
INSERT INTO targets_table (KPI, target_50, target_100, target_125, target_150)
SELECT 'KPI A',  5, 10, 15, 20 FROM DUAL UNION ALL
SELECT 'KPI B', 10, 30, 40, 50 FROM DUAL;

Then:

SELECT KPI(100, 'KPI B') FROM DUAL;

Will output:

KPI(100,'KPIB')
null

But:

SELECT KPI(100, 'KPI B     ') FROM DUAL;

Outputs:

KPI(100,'KPIB')
1.5

Because you have padded the KPI to the correct column length.

In this case, the best solution would be to convert the KPI column from CHAR(10) to VARCHAR2(10) as you are using variable-length, rather than fixed-length, strings. If you cannot do that then set the KPI_TYPE argument to have the same type as the column (which will implicitly pad shorter strings to the correct length) - however, using a VARCHAR2 column should be preferred (although you can use both):

CREATE OR REPLACE FUNCTION KPI(
  RAW_SCORE IN TARGETS_TABLE.TARGET_50%TYPE,
  KPI_TYPE  IN TARGETS_TABLE.KPI%TYPE
)
RETURN NUMBER
AS
  ACTUAL_SCORE NUMBER;
BEGIN
  select case when RAW_SCORE >= target_150 then 1.50
              when RAW_SCORE >= target_125 then 1.25
              when RAW_SCORE >= target_100 then 1.00
              when RAW_SCORE >= target_50 then 0.50
         else 0
         end
  into   ACTUAL_SCORE
  from   TARGETS_TABLE
  where  TARGETS_TABLE.KPI = KPI_TYPE;

  RETURN ACTUAL_SCORE;
END;
/

---

Alternatively, if you want exactly the same functionality in a query (without the function - even to replicating the behaviour for NO DATA FOUND and TOO MANY ROWS exceptions if KPI_TYPE matches zero or multiple rows, respectively) then you can use a correlated sub-query:

-- sample test data
WITH data (raw_score, kpi_type) AS (
  SELECT 10, 'KPI A' FROM DUAL UNION ALL
  SELECT 10, 'KPI B' FROM DUAL UNION ALL
  SELECT 10, 'KPI C' FROM DUAL
)
-- query starts here
SELECT raw_score,
       kpi_type,
       (
         select case
                when d.RAW_SCORE >= t.target_150 then 1.50
                when d.RAW_SCORE >= t.target_125 then 1.25
                when d.RAW_SCORE >= t.target_100 then 1.00
                when d.RAW_SCORE >= t.target_50  then 0.50
                else 0
                end
         from   TARGETS_TABLE t
         where  t.KPI = d.KPI_TYPE
       ) AS kpi
FROM   data d

fiddle