Collection Derived Table Error

IDS 9.30 UC2/Linux Kernel 2.4.X I am trying to do a query and use a collection derived table to keep a function from being hit multiple (6) times. The portion of the query that is giving me troubles returns the error "9610: A collection data type must be supplied within this context.". I don't know where to put the cast to get rid of the warning. Thanks, John Moore select c_table.s_weekstart from table ( (select person_week_starts_on(34, 0, '2003-08-12 17:30:00.0') from TABLE(SET{1})) ) c_table(s_weekstart); I am hoping that using c_table.s_weekstart to compare to 6 different values will eliminiate the extra calls to the function. Something like: select count(w.wassign_no_in), (select c_table.s_weekstart from TABLE ((select person_week_starts_on(34, 0, '2003-08-12 17:30:00.0') from table(set{1}) )) c_table(s_weekstart)) from work_assignment_tbl w where w.person_no_in =34 and (( w.period_start_dt >= c_table.s_weekstart AND w.period_start_dt <= c_table.s_weekstart + 7 UNITS DAY) OR ( w.period_end_dt >= c_table.s_weekstart AND w.period_end_dt <= c_table.s_weekstart + 7 UNITS DAY) OR ( c_table.s_weekstart > w.period_start_dt AND c_table.s_weekstart < w.period_end_dt) ) The inefficient method (using trace and explain) calls the function once for each row, even if w.person_no_in is passed as a literal 1199 I would have thought calling the a function with the same arguments in a single select would not require the function be called over and over. select count(*) from work_assignment_tbl w WHEREw.person_no_in = 1199 AND ((w.period_start_dt >= person_week_starts_on( w.person_no_in, 0, '2003-08-12 17:30:00.0') AND w.period_start_dt <= person_week_starts_on( w.person_no_in, 0, '2003-08-12 17:30:00.0') + 7 UNITS DAY) OR (w.period_end_dt >= person_week_starts_on( w.person_no_in, 0, '2003-08-12 17:30:00.0') AND w.period_end_dt <= person_week_starts_on( w.person_no_in, 0, '2003-08-12 17:30:00.0') + 7 UNITS DAY) OR (person_week_starts_on( w.person_no_in, 0, '2003-08-12 17:30:00.0') > w.period_start_dt AND person_week_starts_on( w.person_no_in, 0,'2003-08-12 17:30:00.0') < w.period_end_dt));

Hi John, The rows returned from the subquery or the inner query should result in a collection for the Collection derived table (CDT) to work. You can convert the subquery into a collection subquery like below for the CDT to work. Also please note that Collection subquery can only be specified in the context of MULTISETs. Replacing the MULTISET below with SET and LIST will not work. Please change your query to select c_table.s_weekstart from table (MULTISET(select person_week_starts_on(34, 0, '2003-08-12 17:30:00.0') from ^^^^^^^^ TABLE(SET{1})) ) c_table(s_weekstart); Hope this helps, Vinayak Shenoi Staff Software Engineer IBM Informix Server Engineering "John Moore " <JohnMoore@PDSI-So To: ids@iiug.org ftware.COM> cc: Sent by: Subject: Collection Derived Table Error [1726] forum.subscriber@i iug.org 08/19/2003 06:28 PM IDS 9.30 UC2/Linux Kernel 2.4.X I am trying to do a query and use a collection derived table to keep a function from being hit multiple (6) times. The portion of the query that is giving me troubles returns the error "9610: A collection data type must be supplied within this context.". I don't know where to put the cast to get rid of the warning. Thanks, John Moore select c_table.s_weekstart from table ( (select person_week_starts_on(34, 0, '2003-08-12 17:30:00.0') from TABLE(SET{1})) ) c_table(s_weekstart); I am hoping that using c_table.s_weekstart to compare to 6 different values will eliminiate the extra calls to the function. Something like: select count(w.wassign_no_in), (select c_table.s_weekstart from TABLE ((select person_week_starts_on(34, 0, '2003-08-12 17:30:00.0') from table(set{1}) )) c_table(s_weekstart)) from work_assignment_tbl w where w.person_no_in =34 and (( w.period_start_dt >= c_table.s_weekstart AND w.period_start_dt <= c_table.s_weekstart + 7 UNITS DAY) OR ( w.period_end_dt >= c_table.s_weekstart AND w.period_end_dt<= c_table.s_weekstart + 7 UNITS DAY) OR ( c_table.s_weekstart > w.period_start_dt AND c_table.s_weekstart < w.period_end_dt) ) The inefficient method (using trace and explain) calls the function once for each row, even if w.person_no_in is passed as a literal 1199 I would have thought calling the a function with the same arguments in a single select would not require the function be called over and over. select count(*) from work_assignment_tbl w WHEREw.person_no_in = 1199 AND ((w.period_start_dt >= person_week_starts_on( w.person_no_in, 0, '2003-08-12 17:30:00.0') AND w.period_start_dt <= person_week_starts_on( w.person_no_in, 0, '2003-08-12 17:30:00.0') + 7 UNITS DAY) OR (w.period_end_dt >= person_week_starts_on( w.person_no_in, 0, '2003-08-12 17:30:00.0') AND w.period_end_dt <= person_week_starts_on( w.person_no_in, 0, '2003-08-12 17:30:00.0') + 7 UNITS DAY) OR (person_week_starts_on( w.person_no_in, 0, '2003-08-12 17:30:00.0') > w.period_start_dt AND person_week_starts_on( w.person_no_in, 0,'2003-08-12 17:30:00.0') < w.period_end_dt));