0

I'm working on DB2 database and using TOAD to execute the below statement. STRINGARRAY is already created in MYSCHEMA as Arraytype.

BEGIN 
DECLARE CASE_ID_LIST MYSCHEMA.STRINGARRAY; 
SET CASE_ID_LIST = ARRAY['A001','A002','A003','A004']; 
SELECT T.ID,T.NUM FROM UNNEST(CASE_ID_LIST) AS T(ID,NUM); 
END

This statement is throwing an error saying "SQL0104N An unexpected token "UNNEST" was found following ....."

My actual intention is to pass arraylist in IN clause of the where condition. I'm trying this select statement to test the use of UNNEST function.

My final query will look something like this:

BEGIN 
DECLARE CASE_ID_LIST MYSCHEMA.STRINGARRAY; 
SET CASE_ID_LIST = ARRAY['A001','A002','A003','A004']; 
SELECT * FROM MYSCHEMA.TABLENAME WHERE CASE_ID IN (SELECT T.NUM FROM UNNEST(CASE_ID_LIST) AS T(NUM));
END

Please advice how I can get this work. Thank you in advance!!

Drew
  • 24,851
  • 10
  • 43
  • 78
Aravind
  • 462
  • 1
  • 7
  • 22
  • In SQL PL context you must `SELECT` `INTO` something. – mustaccio Aug 26 '15 at 00:04
  • What platform and version of DB2? Is UNNEST() even available? – Charles Aug 26 '15 at 12:20
  • Hi, I am using 9.5 version and AIX platform. I checked in other forums too and if I understand them correctly, arrays can only be used in stored procedures and CALL statements only. Is that true?? And UNNEST is available in DB2 9.5 version. Please let me know if there is any other way to pass an array in IN clause. – Aravind Aug 26 '15 at 17:33

0 Answers0