Re: Cursor not Open [32004]

The first return statement inside the loop needs to have the "WITH RESUME" clause appended to it if you want to return more than a single "row" from the function. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Fri, Nov 22, 2013 at 4:48 PM, O KHAN <theultimateboy@hotmail.com> wrote: > Hi, > > I am executing a query which is using a function to return some value as > input > to the query. > > If i am executing the query on the local databases, it is working fine. > > However, when i execute the same query in the distributed environment, > then i > am getting the following error: > > [Error Code: -259, SQL State: 24000] Cursor not open. > > Please note that by distributed environment, I mean i am using the synonyms > for the other database instance. > > Please also note that when I am executing the query using dbaccess, it > works > fine but when the same query is being executed using java application, it > starts giving this error. > > Can someone help me with any idea that how can i resolve the problem? > > Here is the function code: > > create function return_value(p1 varchar(10), p2 date) returning> varchar(10); > > -- > > define v1 varchar(10); > > let v1 = 'AA'; > > foreach p_l for select trim(both from col1) into v1 from table1 > > where number = p1 and p2 between date1 and date2 and is_flag = 'Y' order by > date1 desc > > return nvl(v1,trim(both from 'AA')); > > end foreach; > > return nvl(v1,trim(both from 'AA')); > > -- > end function; > > Thanks in advance! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11345ac61fce3804ebe0a049