Strange error about For - loop with Array?

[Former Member]

Dear all,

I am trying to write an select in a For - loop . The procedure logic looks like blow.The procedure pass the compile. But when I call the procedure , the error shows SAP DBTech JDBC: [1287]: identifier must be declared: K . which I had declared.

If i delete "TAB2 = " in the loop, it's fine.

CREATE COLUMN TABLE TRAFFIC.TTEST(

    AB1 INT,

    AB2 INT

);

INSERT INTO TRAFFIC.TTEST VALUES(1,2);

INSERT INTO TRAFFIC.TTEST VALUES(2,3);

DROP PROCEDURE TRAFFIC.TTTEST;

CREATE PROCEDURE TRAFFIC.TTTEST()

LANGUAGE SQLSCRIPT AS

BEGIN

DECLARE K INT :=1;

DECLARE TEMP INT ARRAY;

TAB1 = SELECT AB1 FROM TRAFFIC.TTEST;

TEMP := ARRAY_AGG(:TAB1.AB1);

FOR K IN 1..2 DO

  TAB2 = SELECT * FROM TRAFFIC.TTEST WHERE TO_INT(AB1) = :TEMP[:K];

END FOR;

END;

CALL TRAFFIC.TTTEST();

Thanks in advance.

Best,

Shu

[former_member210482 · Accepted solution]

Hi Shu,

tab2 is not an array right. what is the use of looping it there. Because each time your tab2 will be overwritten. And which is your output?. Also try without initializing k to 1.

If removing tab2 works fine, try this too SELECT * FROM TRAFFIC.TTEST into tab2 WHERE TO_INT(AB1) = :TEMP[:K];

Regards,

Safiyu

[Former Member · Accepted solution]

Hi Safiyu,

Thank you for your answer.

What I am going to do later is to inner join tab2 with another table. That's why I want to rewrite the Tab2 every loop.

I think I Should try other "complex" SQL select scripts instead of this with your idea.

Best,

Shu.

[former_member210482 · Accepted solution]

Hi Shu,

Try this.

DECLARE TEM INT;

.

.

.

FOR K IN 1..2 DO

  TEM := :TEMP[:K];

  TAB2 = SELECT * FROM TRAFFIC.TTEST WHERE TO_INT(AB1) = :TEM;

END FOR;

ie,

I am storing temp[k] in an intermediate variable. I tried executing it and it worked. Hope this helps.

Regards,

Safiyu

[Former Member · Accepted solution]

Hi Safiyu,

Great idea!! It worked. Thank you very much!

Best regards,

Shu.

[former_member210482 · Accepted solution]

Hi Shu,

Your welcome. I guess there is some bug in sqlscript when using array variable in where clause. Seen some similar issues like this.

Cheers,

Safiyu

[Former Member · Accepted solution]

Hi Safiyu,

That's OK. Cause you have given the substitute solution.

cheers,

Shu