UNNEST function in DB2 is throwing an error

1.2k views Asked by At

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!!

0

There are 0 answers