MySQL Forums
Forum List  »  Stored Procedures

MySQL: Stored function not accepting null from view field
Posted by: Roger Palacios
Date: December 12, 2013 09:29AM

I have the following query that call stored function:

select count(*)
from vw_estatus_empleados vw
where nivel_color_id('Cumplimiento', vw.estatus_actual) = -1;

The `nivel_color_id` function returns `-1` when second parameter is null.

Actually, **the `vw.estatus_actual` field have `null` in all rows**.

When I run the query above the results is:

+----------+
| count(*) |
+----------+
| 0 |
+----------+

By other hand, when I run the same query with `null` instead of `vw.estatus_actual`:

select count(*)
from vw_estatus_empleados vw
where nivel_color_id('Cumplimiento', null) = -1;

The result is:

+----------+
| count(*) |
+----------+
| 19 |
+----------+


Here is the function header:

CREATE DEFINER=...
FUNCTION `nivel_color_id`(`escala` VARCHAR(16), `valor` FLOAT)
RETURNS int(11)

Why this could happed?


A view row sample:

SELECT * FROM vw_estatus_empleados
WHERE estatus_actual IS NULL;

+-------------+--------------+----------------+
| id_empleado | id_proveedor | estatus_actual |
+-------------+--------------+----------------+
| 11 | 16 | NULL |
| 8 | 11 | NULL |
| 4 | 2 | NULL |
| 19 | 23 | NULL |
| 13 | 18 | NULL |
+-------------+--------------+----------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL: Stored function not accepting null from view field
2319
December 12, 2013 09:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.