I am new to Ingres and I am creating a complex stored procedure with calculation involve. The sample code in the documentation is so basic and it cant help.
My question is, is it possible in Ingres Stored Proc to have more than one SELECT statement?
For example:
I have these 3 SELECT INTO statement and the return rows are based on the into variables.
second, I have really a hard time thinking why ingres doesnt support this kind of syntax.
select id,
( select col1 from table 2 where id=id) as 'name',
(select col2 from table 3 where id = id ) as 'sample',
(select sum(total) from table 4 where id=id) as 'total'
from table 1 ;
I have so many complex SQL in this format. And converting it to Ingres is painful. So that is why, stored procedures might be the best solution or maybe i missed something that i do not know.
If possible, i would really appreciate if you post a sample stored procedure that might give me some idea.
Thanks a lot.
Regards,
JB
Answer Hello,
First, welcome to the Ingres World :-)
Yes you can have more than one SELECT/INSERT/UPDATE/DELETE in a DB Proc. Take care if you DB proc is fired thru a rule you have some restrictions in this case (do not issue COMMIT nor ROLLBACK, etc.)
The SQL syntax you are looking for is named, in the Ingres vocabulary, the "derived SELECT" and exist only since Ingres 2006 R3 (9.2.0). It is not on the form you know, but more something like this :
select
id,
name,
sample,
total
from
table_1,
( select col1 "name" from table 2 where id=id),
(select col2 "sample" from table 3 where id = id ),
(select sum(total) "total" from table 4 where id=id);
With DB Proc you can also play with global temporary tables as parameter of a DB proc (see DECLARE GLOBAL TEMPORARY TABLE and "set of parameters" in the CREATE PROCEDURE in the SQL reference guide)
