Skip to Content
avatar image
Former Member

Using table varbiable in procedure, attach results

I have procedure with a for loop and would like to put the result into a table variable.

In the followowing code the variable is overwritten on each loop.

How I can avoid the overwrite and jsutatttach it to the before results

PROCEDURE "BETA_SYSTEM"."beta.procedures::patternIdentification" (
OUT myResult "RESULTTAB"
) 
  LANGUAGE SQLSCRIPT
  SQL SECURITY INVOKER 
  DEFAULT SCHEMA "BETA_SYSTEM"
  READS SQL DATA AS  
BEGIN


  DECLARE counter INT;

  FOR counter IN 1 .. 5 DO
  myResult = SELECT 
          day_before AS "DATE",  
  FROM           
     (SELECT A."DATE" date_range.OFF, add_days(A."DATE", date_range.OFF*-1) AS day_before   
      FROM :tab1 A 
      CROSS JOIN (SELECT  :counter AS OFF FROM DUMMY) date_range) dates  

  END FOR;
END;
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    Mar 30, 2016 at 11:40 AM

    Hello Matthias,

    you have to work with an intermediate variable and then union the results.

    FOR counter IN 1..5 DO

    myResultTMP = SELECT ... ;

    myResult = SELECT * FROM :myResult UNION ALL SELECT * FROM :myResultTMP;

    END FOR;

    Regards,

    Florian

    Add comment
    10|10000 characters needed characters exceeded