0

I'm trying to create a stored procedure in HANA that should loop through a table. Lets say I have 3 columns, ColumnA, ColumnB and ColumnC. In ColumnA, I have my identifiers that I would like to loop over. In ColumnB, I have related identifiers, but in some cases, the identifiers in ColumnB, can be the same as whats in ColumnA. In ColumnC I have a COUNT.

So the table looks like:

ColumnA | ColumnB | ColumnC
0001    | 0002    | 0
0003    | 0004    | 0
0002    | 0005    | 6

The process should loop over each row and check ColumnC to see if the value in ColumnC is greater than 0. If its not, then take the related identifier from ColumnB, and look for it in ColumnA. If there is a value there greater than 0, the loop should insert that line into a table and break.

Any suggestion would be useful, I'm also open to using different methods, besides a procedure.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Jae
  • 49
  • 1
  • 8
  • It's not quite clear (to me at least) what you are trying to do here, how the "identifiers" relate to one another, and what the significance of the "count" in `ColumnC` is. Please add the expected result so that it is possible to match it against your verbal description of the processing. – Lars Br. May 29 '20 at 03:53
  • `the loop should insert that line into a table` into what table? – Suncatcher May 29 '20 at 10:43

1 Answers1

0
BEGIN
DECLARE V_NO INT;

DECLARE LV_LOOP INT;


SELECT count(*) INTO V_NO FROM "YOURTABLE";

FOR LV_LOOP IN 1..:V_NO DO

  SELECT "COLUMNC" INTO LV_COLUMNC FROM "YOURTABLE";

  IF :LV_COLUMNC > 0 THEN

    INSERT INTO "YOURTABLE1" VALUES (.....);

  ELSE
   SELECT "COLUMNB" INTO LV_COLUMNB FROM "YOURTABLE";
   SELECT "COLUMNA" INTO LV_COLUMNA FROM "YOURTABLE";
    IF :LV_COLUMNB = :LV_COLUMNA THEN
     SELECT 'PASS' FROM DUMMY;
    END IF;
  END IF;
END FOR;

END;

Please let me know if it resolved your issue.

Saurav
  • 48
  • 6