Re: informix 9.4 named columns from stored procedures
thanks for the responses...i have a proc that looks like this:
create procedure "informix".get_code_desc(i_cid integer
)
returning varchar(30,0) as TYPE ;
define wk_desc varchar(30,0) ;
let wk_desc = ( select description from code
where id = i_cid
) ;
return wk_desc ;
end procedure ;
it seems to me the column should now be name TYPE - i thought maybe i
was doing something wrong but this is the syntax that is supposed to
work, but the field still comes back titled "expression".
instead, when i run the select with the field named here:
select get_code_desc(type_cid) as type from role
then the column will be titled type.
not how i thought this was being implemented.
thanks for any clarification
Tom
"Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2004.01.22.17.03.13.51338.12806@bloomberg.net>...
> On Thu, 22 Jan 2004 10:00:59 -0500, tomL wrote:
>
> > i am told stored procedures can now return named columns instead of it just
> > being titled "expression". what is the syntax for doing so? thanks
>
>
> Add 'AS name' clause to each return variable in the RETURNING clause of the
> CREATE PROCEDURE/FUNCTION statement:
>
> CREATE FUNCTION freddy ( input INT )
> RETURNING INT AS barney;>
> ....
> END FUNCTION
>
> Art S. Kagel
On Fri, 23 Jan 2004 09:07:36 -0500, tomL wrote:
Works for me. IDS9.40UC2E1 on Linux RH8. To wit:
> create procedure testname( inproc integer) returning varchar(30,0) as type;> define outp varchar(30,0);
> let outp = inproc;
> return outp;
> end procedure;
Routine created.
> execute procedure testname( 4 );
type
4
1 row(s) retrieved.
>
Art S. Kagel
> thanks for the responses...i have a proc that looks like this: create
> procedure "informix".get_code_desc(i_cid integer
> )
> returning varchar(30,0) as TYPE ;
>
> define wk_desc varchar(30,0) ;
>
> let wk_desc = ( select description from code
> where id = i_cid
> ) ;
> return wk_desc ;
> end procedure ;
>
> it seems to me the column should now be name TYPE - i thought maybe i was
> doing something wrong but this is the syntax that is supposed to work, but
> the field still comes back titled "expression".
>
> instead, when i run the select with the field named here: select
> get_code_desc(type_cid) as type from role then the column will be titled
> type.
> not how i thought this was being implemented.
>
> thanks for any clarification
>
> Tom
>
> "Art S. Kagel" <kagel@bloomberg.net> wrote in message
> news:<pan.2004.01.22.17.03.13.51338.12806@bloomberg.net>...
>> On Thu, 22 Jan 2004 10:00:59 -0500, tomL wrote:
>>
>> > i am told stored procedures can now return named columns instead of it
>> > just being titled "expression". what is the syntax for doing so? thanks
>>
>>
>> Add 'AS name' clause to each return variable in the RETURNING clause of the
>> CREATE PROCEDURE/FUNCTION statement:
>>
>> CREATE FUNCTION freddy ( input INT )
>> RETURNING INT AS barney;>>
>> ....
>> END FUNCTION
>>
>> Art S. Kagel
On Fri, 23 Jan 2004 11:38:31 -0500, Art S. Kagel wrote:
Hmm, make sure you did not end up with two functions with the same name and
different signatures such that the old one without the AS TYPE clauses is the
one being selected by the engine for execution.
Art S. Kagel
> On Fri, 23 Jan 2004 09:07:36 -0500, tomL wrote:
>
> Works for me. IDS9.40UC2E1 on Linux RH8. To wit:
>
>> create procedure testname( inproc integer) returning varchar(30,0) as type;>> define outp varchar(30,0);
>> let outp = inproc;
>> return outp;
>> end procedure;
>
> Routine created.
>
>> execute procedure testname( 4 );>
>
> type
>
> 4
>
> 1 row(s) retrieved.
>
>
>
>>
> Art S. Kagel
>
>> thanks for the responses...i have a proc that looks like this: create
>> procedure "informix".get_code_desc(i_cid integer
>> )
>> returning varchar(30,0) as TYPE ;
>>
>> define wk_desc varchar(30,0) ;
>>
>> let wk_desc = ( select description from code
>> where id = i_cid
>> ) ;
>> return wk_desc ;
>> end procedure ;
>>
>> it seems to me the column should now be name TYPE - i thought maybe i was
>> doing something wrong but this is the syntax that is supposed to work, but
>> the field still comes back titled "expression".
>>
>> instead, when i run the select with the field named here: select
>> get_code_desc(type_cid) as type from role then the column will be titled
>> type.
>> not how i thought this was being implemented.
>>
>> thanks for any clarification
>>
>> Tom
>>
>> "Art S. Kagel" <kagel@bloomberg.net> wrote in message
>> news:<pan.2004.01.22.17.03.13.51338.12806@bloomberg.net>...
>>> On Thu, 22 Jan 2004 10:00:59 -0500, tomL wrote:
>>>
>>> > i am told stored procedures can now return named columns instead of it
>>> > just being titled "expression". what is the syntax for doing so? thanks
>>>
>>>
>>> Add 'AS name' clause to each return variable in the RETURNING clause of
>>> the CREATE PROCEDURE/FUNCTION statement:
>>>
>>> CREATE FUNCTION freddy ( input INT )
>>> RETURNING INT AS barney;>>>
>>> ....
>>> END FUNCTION
>>>
>>> Art S. Kagel
Art S. Kagel wrote:
> Hmm, make sure you did not end up with two functions with the same name
and
> different signatures such that the old one without the AS TYPE clauses is
the
> one being selected by the engine for execution.
>
> Art S. Kagel
>
> > On Fri, 23 Jan 2004 09:07:36 -0500, tomL wrote:
> >
> > Works for me. IDS9.40UC2E1 on Linux RH8. To wit:
> >
> >> create procedure testname( inproc integer) returning varchar(30,0) astype;
> >> define outp varchar(30,0);
> >> let outp = inproc;
> >> return outp;
> >> end procedure;
> >
> > Routine created.
> >
> >> execute procedure testname( 4 );> >
> >
> > type
> >
> > 4
> >
> > 1 row(s) retrieved.
> >
> >
> >
> >>
> > Art S. Kagel
> >
> >> thanks for the responses...i have a proc that looks like this: create
> >> procedure "informix".get_code_desc(i_cid integer
> >> )
> >> returning varchar(30,0) as TYPE ;
> >>
> >> define wk_desc varchar(30,0) ;
> >>
> >> let wk_desc = ( select description from code
> >> where id = i_cid
> >> ) ;
> >> return wk_desc ;
> >> end procedure ;
> >>
> >> it seems to me the column should now be name TYPE - i thought maybe i
was
> >> doing something wrong but this is the syntax that is supposed to work,
but
> >> the field still comes back titled "expression".
> >>
> >> instead, when i run the select with the field named here: select
> >> get_code_desc(type_cid) as type from role then the column will be
titled
> >> type.
> >> not how i thought this was being implemented.
> >>
> >> thanks for any clarification
> >>[...]
The problem seems to be using 'SELECT' versus 'EXECUTE'. I was experiencing
the same problem that Tom stated (I used 'SELECT'), but Art's example with
'EXECUTE' worked perfectly. IDS9.40.FC2 on AIX 5.2.0.0, BTW.
--
June Hunt