Re: SPL Return Value problem
Ram Muhunthan wrote:
|Hi can someone help me with this SPL problem?
|
|SELECT so_no, cust_name, so_getnextdate(so_no)
|FROM so_custinfo WHERE so_status = 0 and blt = '01172'
|
|Above is my SQL statment which calls a SPL named so_getnextdate, which
|returns two values.
|
|When I run the SQL I get error code 684 (Procedure
|(informix.so_getnextdate) returns too many values.).
|
|How do I get the returned values and is there a way to label the
|returned values within the SQL statment? I tried the following code
|too, which gave a syntax error.
|
|SELECT soon, cust_name, so_getnextdate(so_no) nextdate, soamount
|FROM so_custinfo WHERE so_status = 0 and blt = '01172'
|
|what am I doing wrong??
--- SNIP ---
For starters, you are placing a multiple value return where the syntax
expects a single value.
Consider the following silly query:
select or.customer_num,
or.order_num,
(select c.lname from customer c
where c.customer_num = or.customer_num),
order_date
from orders;
I have not tested the above but I have experimented with something like
it. I know it's dumb (and correlated to boot) but the point here is the
syntax.
The above is valid syntax because the subquery returns a single row,
single column. If the subquery were asking for (fname, lname) you would
get the syntax error because you are trying to force 2 columns to be
accepted as one in the outer query.
This is nearly identical to your syntax error with the procedure. I
don't think there is a way to get what you want. You might have to wrap
the procedure with a pair of procs; the first calls your so_getnextdate
procedure and stores both values in global but secret variables. It then
returns the first value. The second procedure retrieves the second
[secret] global variable and returns that.
There is [as yet] no way to assign display-column names to the values
returned from a stored procedure. Sad but true; I have an entry in the
oubliette known as FRDB requesting this but I'm not holding my breath
for it. If I were an informix manager, I would classify such a feature
as a "nice to have" but not essential.
--
-- Jake (Never yelled "CROWDED THEATER!" during a fire)
+------------------------------------------------------------+
| The expedient performance of a task with excessive concern |
| regarding its duration-to-completion engenders a virtual |
| certainty of diminished benefit therefrom. |
| -- Benjamin Franklin (but he said it in 3 words) |
+------------------------------------------------------------+