2uzhan.com
Advertisement
Now Place:2uzhan.com » Function returns wrong data on first use

Function returns wrong data on first use

MySQL @ April 22, 2008   Views:0

Hello

last day i create my first function
the problem is that its returns on the first use per session wrong data

getprice is than NULL and the case when returns the fallback value

i think that the problem is the temptable but i don't know how to fix it.

-------
DELIMITER //
CREATE FUNCTION getPrice(custID INT, itemID INT, quant INT)
RETURNS FLOAT
READS SQL DATA
BEGIN
DECLARE getprice FLOAT;
DECLARE outprice FLOAT;
SELECT * FROM (
(SELECT
sp.n_price
FROM
awshop_custsalespr AS csp
LEFT JOIN
awshop_sales_p AS sp
ON ( csp.i_salesgroup_p = sp.i_salesgroup_p )
WHERE csp.i_customer_m = custID
AND sp.i_item_m = itemID
AND sp.n_itemquantity <= quant
AND sp.b_valid =1
AND (
(
NOW( )
BETWEEN sp.d_validfrom
AND sp.d_validuntil )
OR sp.d_validfrom IS NULL
)
AND sp.dt_deleted IS NULL
AND csp.b_valid =1
AND (
(
NOW( )
BETWEEN csp.d_validfrom
AND csp.d_validuntil
)
OR csp.d_validfrom IS NULL
)
AND csp.dt_deleted IS NULL)
UNION
(SELECT
sp.n_price
FROM
awshop_cgrsalespr AS cgsp
LEFT JOIN
awshop_sales_p AS sp
ON ( cgsp.i_salesgroup_p = sp.i_salesgroup_p )
WHERE cgsp.i_custgroup_p = (SELECT cs.i_custgroup_p FROM awshop_customers as cs WHERE cs.i_customer_m = custID)
AND sp.i_item_m = itemID
AND sp.b_valid = 1
AND sp.n_itemquantity <= quant
AND (
(
NOW( )
BETWEEN sp.d_validfrom
AND sp.d_validuntil )
OR sp.d_validfrom IS NULL
)
AND sp.dt_deleted IS NULL
AND cgsp.b_valid =1
AND (
(
NOW( )
BETWEEN cgsp.d_validfrom
AND cgsp.d_validuntil
)
OR cgsp.d_validfrom IS NULL
)
AND cgsp.dt_deleted IS NULL)
ORDER BY 1 ASC
LIMIT 0,1) as temptable
INTO getprice;
SET outprice = (
SELECT
CASE
WHEN getprice IS NULL THEN (
SELECT n_salesprice
FROM awshop_item_m
WHERE i_item_m = itemID
)
ELSE (
SELECT getprice
)
END);
RETURN outprice;
END //
DELIMITER ;
------

--------------Solutions-------------

Tags:
© 2018 2uzhan.com Contact