How to implement a for loop over a table type in HANA?

[Former Member]

Hello, I am developing a procedure in HANA where I am Selecting some values from a table into a table type, and then I will use those values to refine my search. For example, here is my code so far:

PROCEDURE "EVALUATE_PATTERN" (IN patternID BIGINT, OUT result BIGINT)

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  DEFAULT SCHEMA "ETD"

  --READS SQL DATA

  AS

BEGIN

lt_main_table = SELECT * FROM "MAINTABLE";

  -- Get the pattern SUBJECT features from the Subject table

  ltt_constraints = SELECT "SubjectName" AS "ConstraintName", "SubjectValue" AS "ConstaintValue" FROM "Subject" WHERE "PatternID.id" = :patternID;

-- Here is the part where I would like to refine my search for my main table (based on the constraints retried earlier), something like:

FOR EACH ConstraintName, ConstraintValue FROM ltt_constraints:

     lt_main_table = SELECT * FROM :lt_main_table WHERE ConstraintName = ConstraintValue;

......

END;

OK, I know this syntax does not exist (I just made it up to show what I would like to do). I have looked for some examples, but no success. The only solution I have found is to use cursors, but these have to be defined at the beginning of the procedure. But in my case, the procedure consists of refining the search a lot, on future constraints,  because I am gathering constraints from several tables (I only showed you the ones from "Subject" table, but I actaully have like 8 or 9 tables from where I select the constraints, and then I want to apply them).

Message was edited by: Tom Flanagan

[justin_molenaur2]

Hi there, have a look at this thread, I am thinking you can definitely use this as a starting point for your requirement. As long as you have some portioning criteria columns, you can loop however you like.

http://scn.sap.com/community/hana-in-memory/blog/2014/01/23/using-loops-within-stored-procedures-for...

Happy HANA,

Justin