NULL value returned from function displays in table as 0

pbs commented on Jun 22, 2022

I have some sql which passes an id to a mysql function to return the text value. If this function returns null because the value does not exist, KoolReport table is displaying this as 0

I suspect this is because KoolReport cannot determine the type of variable it is.

Is there any way I can tell KoolReport that this field is actually text and nulls are valid.

The function is coded like this and accepts 1 integer parameter

BEGIN
DECLARE theTitle VARCHAR(50);
SELECT Title INTO theTitle FROM tbl_codes WHERE Id = Id;
RETURN theTitle;
END

My query passed into KoolReport is like this,

SELECT CONCAT(STU.LastName,', ',STU.FirstName) AS 'Name', 
 funcGetCode(STU.ClassId) AS 'StudentClass'
FROM tbl_students AS STU

Many thanks

Sebastian Morales commented on Jun 23, 2022

The core Table widget has a property called emptyValue which is 0 by default. You could set it to the string "NULL" like this:

Table::create(array(
    ...
    "emptyValue" => "NULL",
));

Let us know if this solves your case. Tks,

pbs commented on Jun 23, 2022

Thanks very much Sebastian - it works.

KoolReport's Forum

NULL value returned from function displays in table as 0 #2737

Build Your Excellent Data Report