Re: Problem with Views AND Stored Procedures
If you use a stored procedure in a SELECT statement, it must
return a single value -- single row AND single column.
Your procedure returns multiple columns, violating the constraint.
Incidentally, finder -684 says:
-684 Procedure procedure-name returns too many values.
The number of returned values from a procedure is more than the number of
values that the caller expects.
Example of error:
CREATE PROCEDURE testproc (arg INT)
RETURNING INT, INT; RETURN 1,2;
END PROCEDURE
SELECT col FROM tab WHERE col = testproc(1); -- error
This gives an example of the constraint, admittedly not in the SELECT-list,
but still it gives a fairly good hint, don't you think?
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
}Date: Thu, 29 Aug 1996 15:29:57 -0500 (CDT)
}From: Rajeev SRK Nimmagadda <rajeevn@lmis.jcdc.doleta.gov>
}X-Informix-List-Id: <list.11187>
}
}I have a problem with SP and Views.
}( I have waited on 10 minutes on line without Answer from Tech. Service)
}
}I am creating the procedure in the following manner.
}
}CREATE PROCEDURE get_status(pssn char(9), penroll_num smallint)
} RETURNING CHAR(2), DATE, SMALLINT, SMALLINT;
}
} DEFINE num_train_days SMALLINT;
} DEFINE num_cal_days SMALLINT;
} DEFINE stat_code CHAR(2) ;
} DEFINE stat_date DATE ;
} DEFINE new_date DATE ;
} DEFINE old_date DATE ;
}
}-- Code to caluculate these values.
}
} RETURN stat_code, stat_date, num_train_days, num_cal_days ;
}
}END PROCEDURE ;
}
}If I run with execute procedure it works great.
}EXECUTE PROCEDURE get_status(ssn, 1) it returns four values.
}
}Bt I can't use it in the following Format.
}
}create view v_tempstatus (ssn , stat_cd, status_dt, cl_dys, tr_days)
}AS
} SELECT ssn, get_status(ssn, 1) from Student
} where ssn < "100000000";
}
}It gives the Error 684 : The procedure returns too many values.
}
}We are running 7.x on RS6K machine.