AllExperts > Experts 
Search      

Ingres

Volunteer
Answers to thousands of questions
 Home · More Questions · Answer Library  · Encyclopedia ·
More Ingres Answers
Question Library

Ask a question about Ingres
Volunteer
Experts of the Month
Expert Login

Awards

About Us
Tell friends
Link to Us
Disclaimer

 
 
 
 
About Jean-Pierre Zuate
Expertise
Any questions about database Ingres (owned by Computer Associates then Ingres Corp) : - Ingres 6.4 - OpenINGRES from 1.0 to 2.0 - IngresII from 2.0 to 2.6 - Ingres R3, Ingres 2006 (Open Source version) - All tool around Ingres : ABF, Report Writer, Replicator, OpenROAD, Ingres/NET Ingres/STAR, ...

Experience
16 years of computing experience as :
- AS400 programmer
- AIX / Ingres administrator and developer (OpenROAD and korn shell)
- Ingres DataBase Administrator
- Ingres expert - Data modelisation - ETL - Reporting - Many of Computer Associates sofwares - ITIL / CMDB / Change Management
 
   

You are here:  Experts > Computing/Technology > Databases > Ingres > Ingres Procedure

Topic: Ingres



Expert: Jean-Pierre Zuate
Date: 8/3/2007
Subject: Ingres Procedure

Question
Hello,

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)

Hope this help you.

Best regards

Add to this Answer    Ask a Question



  Rate this Answer
   Was this answer helpful?
Not at allDefinitely              
   12345  

     
About Us | Advertise on This Site | User Agreement | Privacy Policy | Help
Copyright  © 2008 About, Inc. About and About.com are registered trademarks of About, Inc. The About logo is a trademark of About, Inc. All rights reserved.