-686, stumped
A user on IDS 10.00.UC8W4 had an SPL function returning multiple rows and tried to use it as 'WHERE key IN (myfunction())', which failed with error -686 (function returned more than one row). Art Kagel suggested rewriting the query as a join against the function's result set using the TABLE() construct, e.g. FROM sometable st, TABLE(FUNCTION myfunction()) AS mf(key) WHERE st.key = mf.key. The poster confirmed this worked. An alternative mentioned was looping over the function's results and running the select per key with a host variable.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I had this working a minute ago, then I put data in and it broke. I have a recursive function which returns multiple rows. I want to use those in a select statement ala: WHERE key in (myfunction()) .. and of course I get a -686 Function (myfunction) has returned more than one row. Obviously I'm thinking about this wrong. Suggestions? cheers j.
IDS Version? Front-end (SPL, ESQL/C, 4GL)? Front-end version? Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 20, 2009 at 11:39 AM, JACK PARKER <jack.parker4@verizon.net>wrote: > I had this working a minute ago, then I put data in and it broke. > > I have a recursive function which returns multiple rows. I want to use > those > in a select statement ala: > > WHERE key in (myfunction()) > > ... and of course I get a -686 Function (myfunction) has returned more than > one row. > > Obviously I'm thinking about this wrong. Suggestions? > > cheers > j. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015173ff3e44980fe047194976b
IDS Version? Front-end (SPL, ESQL/C, 4GL)? Front-end version?
IDS 10.00.UC8W4. procedure is written in SPL. Using dbaccess to test.
I could use an SPL with a FOREACH to pull the ids I want, but it would be more
elegant to use a simple select.
cheers
j.
This would be easier in 11.50, but there is a way. Change this to a join
using the TABLE() feature to join to the function return:
FOREACH
SELECT ...
INTO ...
FROM sometable as st, TABLE(FUNCTION myfunction()) as mf(key)
WHERE st.key = mf.key....;
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Thu, Aug 20, 2009 at 12:04 PM, JACK PARKER <jack.parker4@verizon.net>wrote:
> IDS Version? Front-end (SPL, ESQL/C, 4GL)? Front-end version?
>
> IDS 10.00.UC8W4. procedure is written in SPL. Using dbaccess to test.
>
> I could use an SPL with a FOREACH to pull the ids I want, but it would be
> more
> elegant to use a simple select.
>
> cheers
> j.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0023545bd8146d37550471955968
Interesting. Works a treat (as in thank you very much). How would you handle it V11? cheers j.
In 11.50 you MIGHT make an outer loop out of the function call results then issue the select once with each key inside the loop. using dynamic SQL. Hmm, I guess you could do that in 10.00 also just using a host variable in the inner query filled by the outer loop. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 20, 2009 at 12:47 PM, JACK PARKER <jack.parker4@verizon.net>wrote: > Interesting. Works a treat (as in thank you very much). > > How would you handle it V11? > > cheers > j. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015173fe6c6193113047198aa84