Hello I am trying to create a mysql stored function that used "UNION ALL" but the result is this:
1415 - Not allowed to return to result set from a function
How can I solve it?
DELIMITER $$
CREATE DEFINER =
root@localhostFUNCTIONF_GET_CLIENT_CREDIT(I_GUEST_CLIENT_NUM INT(11),
I_GROUP_CD INT(11),
I_LOCAL_CD INT(11)
) RETURNS DOUBLE BEGIN
SELECT SUM(TOTAL) FROM (
SELECT SUM(CASH_PRECIO+CHECK_PRECIO+COUPON_PRICE-TOTAL_PRECIO) AS TOTAL FROM T_VENTA WHERE STAT_CD = '3200' AND GUEST_NUM = I_GUEST_CLIENT_NUM AND GROUP_CD = I_GROUP_CD AND LOCAL_CD = I_LOCAL_CD GROUP BY GROUP_CD, LOCAL_CD, GUEST_NUM
UNION ALL
SELECT SUM(CASH_PRECIO+CHECK_PRECIO+COUPON_PRICE-TOTAL_PRECIO) AS TOTAL FROM T_VENTA_OLD WHERE STAT_CD = '3200' AND GUEST_NUM = I_GUEST_CLIENT_NUM AND GROUP_CD = I_GROUP_CD AND LOCAL_CD = I_LOCAL_CD GROUP BY GROUP_CD, LOCAL_CD, GUEST_NUM
UNION ALL
SELECT SUM(PRICE) AS TOTAL FROM T_MNG_BANK WHERE STAT_CD = '1000' AND CHECK_FLAG = 'Y' AND CLIENT_NUM = I_GUEST_CLIENT_NUM AND GROUP_CD = I_GROUP_CD AND LOCAL_CD = I_LOCAL_CD GROUP BY GROUP_CD, LOCAL_CD, CLIENT_NUM
UNION ALL
SELECT SUM(TOTAL_PRECIO) AS TOTAL FROM T_MNG_CREDIT WHERE STAT_CD = '1000' AND CLIENT_NUM = I_GUEST_CLIENT_NUM AND GROUP_CD = I_GROUP_CD AND LOCAL_CD = I_LOCAL_CD GROUP BY GROUP_CD, LOCAL_CD, CLIENT_NUM
UNION ALL
SELECT SUM(NOTE_PRICE) AS TOTAL FROM T_MNG_NOTE WHERE STAT_CD = '1000' AND CLIENT_NUM = I_GUEST_CLIENT_NUM AND GROUP_CD = I_GROUP_CD AND LOCAL_CD = I_LOCAL_CD GROUP BY GROUP_CD, LOCAL_CD, CLIENT_NUM ) AS TOTAL_CREDIT LIMIT 1 ; RETURN TOTAL_CREDIT; END $$ DELIMITER ;