Informix

 View Only
Expand all | Collapse all

How to create function using foreach cursor from multiple table to return multiple row

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

    Posted Thu May 26, 2022 10:26 AM
    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


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

    IBM Champion
    Posted Thu May 26, 2022 10:49 AM
    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
    ------------------------------



  • 3.  RE: How to create function using foreach cursor from multiple table to return multiple row

    IBM Champion
    Posted Thu May 26, 2022 02:26 PM
    Edited by System Fri January 20, 2023 04:15 PM

    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
    ------------------------------