Returning a named data row
Solaris 2.6/9.20UC2 I've got a named data row which is built of 5 elements and I'm selecting into the type no problems. But what I can't do is return more than one row from the SPL - always bombs with a 9791. Basically CREATE ROW TYPE n_data ( title CHAR(40), name VARCHAR(255), id INTEGER, valid SMALLINT, cnt SMALLINT, id SMALLINT ); in the SPL define l_date n_data; foreach execute function myfunc into l_data return l_data.title, l_date etc -- with resume it bomb without it -- returns the data correctly end foreach running myfunc directly produces all the rows correctly, but they are returned as a LVARCHAR hence the cast into the named data type. Anyone with any ideas. They are always something simple:-)) -- Paul Watson # WF Software Ltd # You are only young once Tel: +44 1436 674729 # but you can be immature Fax: +44 1436 678693 # for ever www.wfsoftware.com #
Just return the entire object:
CREATE FUNCTION Do_Something ( With_This Data_Type )RETURNS Some_Data_Type
DEFINE rtRetVal Some_Data_Type;
FOREACH EXECUTE FUNCTION I_Wonder_What("Something" || With_This_Type )
INTO rtRetVal
RETURN rtRetVal WITH RESUME;
END FOREACH
END FUNCTION;
Then in whatever you are calling this from, you will get back a named row
type, which you can pick apart as if it were a row.
Or am I missing the point?
Paul Watson wrote:
> Solaris 2.6/9.20UC2
>
> I've got a named data row which is built of 5 elements and I'm selecting
> into the type no problems. But what I can't do is return more than
> one row from the SPL - always bombs with a 9791. Basically
>
> CREATE ROW TYPE n_data
> (
> title CHAR(40),
> name VARCHAR(255),
> id INTEGER,
> valid SMALLINT,
> cnt SMALLINT,
> id SMALLINT
> );
>
> in the SPL
>
> define l_date n_data;
>
> foreach execute function myfunc
> into l_data
>
> return l_data.title, l_date etc -- with resume it bomb without it
> -- returns the data correctly
>
> end foreach
>
> running myfunc directly produces all the rows correctly, but they are
> returned as a LVARCHAR hence the cast into the named data type.
>
> Anyone with any ideas. They are always something simple:-))
>
> --
> Paul Watson #
> WF Software Ltd # You are only young once
> Tel: +44 1436 674729 # but you can be immature
> Fax: +44 1436 678693 # for ever
> www.wfsoftware.com #
>Subject: Returning a named data row >From: Paul Watson paulw@wfsoftware.com >Date: 24.04.00 13:19 W. Europe Daylight Time >Message-id: <39042DD2.65C4DB8A@wfsoftware.com> > >Solaris 2.6/9.20UC2 > >I've got a named data row which is built of 5 elements and I'm selecting >into the type no problems. But what I can't do is return more than >one row from the SPL - always bombs with a 9791. Basically > >CREATE ROW TYPE n_data >( >title CHAR(40), >name VARCHAR(255), >id INTEGER, >valid SMALLINT, >cnt SMALLINT, >id SMALLINT >); > >in the SPL > >define l_date n_data; > >foreach execute function myfunc > into l_data > > return l_data.title, l_date etc -- with resume it bomb without it > -- returns the data correctly > >end foreach > >running myfunc directly produces all the rows correctly, but they are >returned as a LVARCHAR hence the cast into the named data type. > >Anyone with any ideas. They are always something simple:-)) > >-- >Paul Watson # >WF Software Ltd # You are only young once >Tel: +44 1436 674729 # but you can be immature >Fax: +44 1436 678693 # for ever >www.wfsoftware.com # > > > > > > see the Informix SQL Syntax volume 2, page 2-274 for the types of things that you can return from an SPL. Nona