Help with stored procedures result

Hi My background in MsSQL have some lacks when it comes to informix. How to retun select value as result of procedure. Basicaly i would like to have results of ret_value after this procedure. CREATE PROCEDURE up_get(in_report_id CHAR(10), in_lng_id INTEGER, in_period CHAR(9), in_period_p CHAR(9)) -- RETURNING CHAR(10), DECIMAL(15,2), DECIMAL(15,2),DECIMAL(15,2), DECIMAL(15,2), CHAR(50), CHAR(500); DEFINE in_period_c , in_period_f CHAR(9); SELECT d.source_id, SUM (CASE d.period_id WHEN 9 THEN value_c ELSE NULL END) period, SUM (CASE d.period_id WHEN 30 THEN value_c ELSE NULL END) period_c, SUM (CASE d.period_id WHEN 41 THEN value_c ELSE NULL END) period_f, SUM (CASE d.period_id WHEN 1 THEN value_c ELSE NULL END) period_p, ut.unit_trans,sd.report_desc FROM data_c d JOIN period p ON d.period_id=p.period_id JOIN source_desc sd ON d.source_id=sd.source_id JOIN source s ON d.source_id=s.source_id JOIN unit_trans ut ON s.unit_id=ut.unit_id WHERE d.source_id IN (SELECT source_id FROM used_in WHERE report_id='test') AND ut.lng_id = 1 AND sd.lng_id = 1 GROUP BY d.source_id,ut.unit_trans,sd.report_desc INTO TEMP ret_value; END PROCEDURE;

If you only get one row, follow these instructions: [SNIP] > > CREATE PROCEDURE up_get(in_report_id CHAR(10), in_lng_id INTEGER, > in_period CHAR(9), in_period_p CHAR(9)) > -- RETURNING CHAR(10), DECIMAL(15,2), DECIMAL(15,2),> DECIMAL(15,2), DECIMAL(15,2), CHAR(50), CHAR(500); Add RETURNING keyword in a line above! As I can see, number of returning values is not the same as number of selected columns in your SELECT clause -> something is wrong! > > DEFINE in_period_c , in_period_f CHAR(9); Define new variables having same types as returning values: DEFINE ret_source_id INT; DEFINE ret_period INT(?); ... DEFINE ret_report_desc CHAR(500); > > SELECT d.source_id, > SUM (CASE d.period_id WHEN 9 THEN value_c ELSE NULL END) period, > SUM (CASE d.period_id WHEN 30 THEN value_c ELSE NULL END) period_c, > SUM (CASE d.period_id WHEN 41 THEN value_c ELSE NULL END) period_f, > SUM (CASE d.period_id WHEN 1 THEN value_c ELSE NULL END) period_p, > ut.unit_trans,> sd.report_desc > Add INTO clause: INTO ret_source_id, ret_period, ret_period_c, ret_period_f,ret_periodp,ret_unit_trans,ret_report_desc > > FROM data_c d > JOIN period p ON d.period_id=p.period_id > JOIN source_desc sd ON d.source_id=sd.source_id > JOIN source s ON d.source_id=s.source_id > JOIN unit_trans ut ON s.unit_id=ut.unit_id > WHERE d.source_id IN (SELECT source_id FROM used_in WHERE > report_id='test') > AND ut.lng_id = 1 > AND sd.lng_id = 1 > GROUP BY d.source_id,ut.unit_trans,sd.report_desc > INTO TEMP ret_value; Remove INTO TEMP clause. Add RETURN clause: RETURN ret_source_id, ret_period, ret_period_c, ret_period_f,ret_periodp,ret_unit_trans,ret_report_desc; > > END PROCEDURE; Gorazd

"Wojciech Kusnierek" <kusnier@bigfoot.com> wrote in message news:c3aeb2.0311200254.30b36d4c@posting.google.com... > Hi > My background in MsSQL have some lacks when it comes to informix. > How to retun select value as result of procedure. Basicaly i would > like to have results of ret_value after this procedure. > > CREATE PROCEDURE up_get(in_report_id CHAR(10), in_lng_id INTEGER, > in_period CHAR(9), in_period_p CHAR(9)) > -- RETURNING CHAR(10), DECIMAL(15,2), DECIMAL(15,2),> DECIMAL(15,2), DECIMAL(15,2), CHAR(50), CHAR(500); > > DEFINE in_period_c , in_period_f CHAR(9); > > SELECT d.source_id, > SUM (CASE d.period_id WHEN 9 THEN value_c ELSE NULL END) period, > SUM (CASE d.period_id WHEN 30 THEN value_c ELSE NULL END) period_c, > SUM (CASE d.period_id WHEN 41 THEN value_c ELSE NULL END) period_f, > SUM (CASE d.period_id WHEN 1 THEN value_c ELSE NULL END) period_p, > ut.unit_trans,> sd.report_desc > FROM data_c d > JOIN period p ON d.period_id=p.period_id > JOIN source_desc sd ON d.source_id=sd.source_id > JOIN source s ON d.source_id=s.source_id > JOIN unit_trans ut ON s.unit_id=ut.unit_id > WHERE d.source_id IN (SELECT source_id FROM used_in WHERE > report_id='test') > AND ut.lng_id = 1 > AND sd.lng_id = 1 > GROUP BY d.source_id,ut.unit_trans,sd.report_desc > INTO TEMP ret_value; > > END PROCEDURE; You are storing the result in a tempory table called 'ret_value'. You'll have to execute up_get, and then select * from ret_value. I suspect, however, what you really want is CREATE PROCEDURE up_get(...) RETURNING CHAR(10), ... ; DEFINE source_id CHAR(10); ... FOREACH SELECT d.source_id, ... INTO source_id, ... FROM ... RETURN source_id, ... WITH RESUME; END FOREACH END PROCEDURE; You'll have to iterate thru the results.