How to create function using foreach cursor from multiple table to return multiple row
Hi, anyone know how can i fix this?
CREATE FUNCTION neo_trend1(ParaId INT, startdate DATETIME YEAR TO FRACTION(5), enddate DATETIME YEAR TO FRACTION(5), Code NVARCHAR(10))
RETURNING INT AS ParaId, DATETIME YEAR TO FRACTION(5) AS date, DECIMAL AS value, NVARCHAR(10) AS Code;DEFINE para_id INT;
DEFINE code NVARCHAR(10);
DEFINE value DECIMAL;
DEFINE date DATETIME YEAR TO FRACTION(5);
set debug file to 'c:\\trace.txt';
TRACE ON;
TRACE 'FOREACH starts';
FOREACH neo_cursor WITH HOLD for
SELECT a.para_id, c.code, a.value1, a.date1 INTO para_id, code, value, date
FROM vt_meter_ts_tbl a join meter_para b
on a.para_id=b.id
join meter_category_reading c
on b.meter_category_reading_id=c.id
WHERE a.date1 > startdate
AND a.date1 < enddate
AND c.code=Code;AND a.para_id=ParaId
RETURN ParaId, date, value, Code WITH RESUME;
END FOREACH
END FUNCTION;
--EXECUTE USING THIS PARAMETER ARGUMENT--
EXECUTE FUNCTION neo_trend1 (101,'2022-01-01 00:00:00.00000','2022-01-03 00:00:00.00000','Kwh+');------------------------------
nasiha zailan
------------------------------
#Informix
Would you care to share the error you are getting? Why do you have a semicolon after: AND c.code=Code ; I would expect this to give a syntax error. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------
OK, I see two issues. You have named the local variables for the INTO clause in problematic ways:
"code" is the same name as the input variable "Code" (case is ignored)
"date" is a keyword for the type DATE
"para_id" is the same name as the column you are loading into it
Best practice is to name variables defined inside a stored procedure with a prefix of "l_", ie as local and to name input argument with a prefix of "p_", is as a parameter. This avoids all three of these issues. I have made the changes below as I would write it:
CREATE FUNCTION neo_trend1(p_ParaId INT, p_startdate DATETIME YEAR TO FRACTION(5), p_enddate DATETIME YEAR TO FRACTION(5), p_Code NVARCHAR(10))
RETURNING INT AS ParaId, DATETIME YEAR TO FRACTION(5) AS date, DECIMAL AS value, NVARCHAR(10) AS Code;DEFINE l_para_id INT;
DEFINE l_code NVARCHAR(10);
DEFINE l_value DECIMAL;
DEFINE l_date DATETIME YEAR TO FRACTION(5);
set debug file to 'c:\\trace.txt';
TRACE ON;
TRACE 'FOREACH starts';
FOREACH neo_cursor WITH HOLD for
SELECT a.para_id, c.code, a.value1, a.date1
INTO l_para_id, l_code, l_value, l_date
FROM vt_meter_ts_tbl a
join meter_para b
on a.para_id=b.id
join meter_category_reading c
on b.meter_category_reading_id=
c.
id
WHERE a.date1 > p_startdate
AND a.date1 < p_enddate
{ ASK: Here on the next line, for example, which variable should the engine use "Code" or "code", the p_ prefix eliminates the confusion }
AND c.code = p_Code
AND a.para_id = p_ParaId
RETURN l_ParaId, l_date, l_value, l_Code WITH RESUME;
END FOREACH
END FUNCTION;
I just noticed the extraneous semi-colon on the c.code = p_Code line that Mike Walker pointed out. Edited to correct.
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------