stored proc and optional parameters
hello,
running ids 10.fc5 on aix 5.3
i have a proc in development that accepts up to 6 paramaters.
when i call the proc thru an 'execute procedure' statement or walk
thru it by debugging, all is well and one datetime and a string is
returned.
but when i embed the call in a sql select, i get the error " Function
returns too many values" - no error number is displayed.
the proc whittles down everything to only one row, so i am stumped to
this behavior.
any clues to what is going on here?
Thanks
--------------------------------------------------------------------------
...this returns the error
select tour.*, get_max_datetime(32,2969733,1328)
from tour
where id = 2755780;
.............this works fine
execute procedure get_max_datetime (32,2969733,1328)
--------------------------------------------------------------------------
the stored procedure:
drop function
"informix".get_max_datetime(integer,integer,integer,integer,integer,integer,integer,integer);
create procedure "informix".get_max_datetime( i_from_class_id
integer ,
i_from_id integer,
i_type_cid integer,
i_type_cid2_opt integer default 2222,
i_type_cid3_opt integer default 3333,
i_type_cid4_opt integer default 4444,
i_type_cid5_opt integer default 5555,
i_type_cid6_opt integer default 6666
)
returning datetime year to second, varchar(40);
define wk_datetime datetime year to second ;
define wk_max_datetime datetime year to second ;
define wk_type_cid integer;
define wk_type_cid2 integer;
define w_type_cid integer;
define w_id integer;
define wk_event_id integer;
define wk_create_date datetime year to second;
define wk_event_type varchar(40);
-- For debugging only
--trace on;
create temp table tmp_event
(id serial,
event_id integer,
date_time datetime year to second,
create_date datetime year to second,
type_cid integer,
event_type varchar(40))
with no log;
foreach
select id, date_time , create_date, type_cid, get_code_desc
(t1.type_cid)
into wk_event_id, wk_datetime, wk_create_date, wk_type_cid,
wk_event_type
from event t1
where t1.status_cid = 1140
and t1.from_class_id = i_from_class_id
and t1.from_id = i_from_id
and t1.type_cid in
(i_type_cid,i_type_cid2_opt,i_type_cid3_opt,i_type_cid4_opt,i_type_cid5_opt,i_type_cid6_opt)
insert into tmp_event
(event_id,date_time,create_date, type_cid, event_type)
values
(wk_event_id,wk_datetime,wk_create_date, wk_type_cid,wk_event_type);
end foreach ;
select first 1 date_time, event_type
from tmp_event
group by date_time,event_type
order by date_time desc ,event_type
into temp a with no log;
select date_time, event_type
into wk_max_datetime, wk_event_type
from a;
drop table tmp_event;
drop table a;
return wk_max_datetime,wk_event_type ;
end procedure
;
-- Permissions for get_max_datetime
grant execute on procedure
"informix".get_max_datetime(integer,integer,integer,integer,integer,integer,integer,integer)
to 'public';
update statistics for procedure get_max_datetime
On Jun 7, 2:05 pm, "tomc...@yahoo.com" <tomc...@yahoo.com> wrote:
> hello,
>
> running ids 10.fc5 on aix 5.3
> i have a proc in development that accepts up to 6 paramaters.
> when i call the proc thru an 'execute procedure' statement or walk
> thru it by debugging, all is well and one datetime and a string is
> returned.
> but when i embed the call in a sql select, i get the error " Function
> returns too many values" - no error number is displayed.
> the proc whittles down everything to only one row, so i am stumped to
> this behavior.
>
> any clues to what is going on here?
> Thanks
>
> --------------------------------------------------------------------------
> ...this returns the error
> select tour.*, get_max_datetime(32,2969733,1328)
> from tour
> where id = 2755780;
>
> .............this works fine
> execute procedure get_max_datetime (32,2969733,1328)
> -------------------------------------------------------------------------->
> the stored procedure:
>
> drop function
> "informix".get_max_datetime(integer,integer,integer,integer,integer,integer,integer,integer);
>
> create procedure "informix".get_max_datetime( i_from_class_id
> integer ,
> i_from_id integer,
> i_type_cid integer,
> i_type_cid2_opt integer default 2222,
> i_type_cid3_opt integer default 3333,
> i_type_cid4_opt integer default 4444,
> i_type_cid5_opt integer default 5555,
> i_type_cid6_opt integer default 6666
>
> )
>
> returning datetime year to second, varchar(40);
<SNIP>
Here's your
problem>>>>>>>>>>>>>>>>>>>>^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
your proc is returning more than one value into a context that
expects a single value. You can
cast the function call results to a MULTISET or put the call into a
subquery in the FROM clause
since you are using IDS 10.
Art S. Kagel
> cast the function call results to a MULTISET or put the call into a or return a rowtype. Superboer.