Informix

 View Only
Expand all | Collapse all

create function uisng foreach cursor from multiple table to return multiple row

  • 1.  create function uisng foreach cursor from multiple table to return multiple row

    Posted Thu May 26, 2022 10:26 AM
    Hi, i try to create SPL routine using foreach cursor from multiple table but it did not follow the parameter given during the execution. Anyone know how to 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


  • 2.  RE: create function uisng foreach cursor from multiple table to return multiple row

    Posted Thu May 26, 2022 02:25 PM
    Hi Nasiha.

    You have an extra semi-colon in the wrong place at the end of this line:

    AND c.code=Code;


    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------

    Original Message