Thanks for your time! I've asked a number of questions recently - thanks to all who have responded. Your responses have been very helpful and appreciated. :)
I'm writing a function that generates summary information. I want to return the summary information in a single record set. How do you build a result set using variables, and then return the variables is a record set. (sorry if I'm not explainit it well)
Below is my current version of the function. It compiles and executes. How ever, it returns only one record - the last select into statement
CREATE OR REPLACE FUNCTION "public"."fnc_sg_get_emp_sg_total_type" (int_emp_id integer, int_grid_id integer, int_rev integer, int_lvl_cnt integer, dt_hiredate date) RETURNS SETOF "public"."tp_sg_get_emp_sg_total_type" AS$body$DECLARE
int_lvl_cntr integer; nmr_lvl_total numeric; int_lvl_type integer; nmr_nrml_total numeric; nmr_ct_total numeric; nmr_spclty_total numeric; nmr_sg_total numeric; nmr_bs_wage numeric; nmr_rp_total numeric; rec public.tp_sg_get_emp_sg_total_type;
BEGIN
int_lvl_cntr = 1;nmr_sg_total = 0;nmr_nrml_total = 0;nmr_ct_total = 0;nmr_spclty_total = 0;
nmr_bs_wage = wg_amt from sg_bs_wg where sg_bs_wg.actv = true;
/* get level totals by skill type */WHILE int_lvl_cntr <= int_lvl_cnt loop
nmr_lvl_total = * from fnc_sg_get_lvl_total(int_emp_id, int_grid_id, int_rev, int_lvl_cntr, dt_hiredate) ;
if nmr_lvl_total is null then nmr_lvl_total = 0; end if;
int_lvl_type = distinct skill_type from sg_emp_skllgrd where emp_id = int_emp_id AND grid_id=int_grid_id AND rev = int_rev AND lvl = int_lvl_cntr; if int_lvl_type=1 THEN nmr_nrml_total = nmr_nrml_total + nmr_lvl_total; elseif int_lvl_type=2 THEN nmr_ct_total = nmr_ct_total + nmr_lvl_total; ELSE nmr_spclty_total = nmr_spclty_total + nmr_lvl_total; END if;
int_lvl_cntr = int_lvl_cntr + 1;
end loop;
/* get role pay total */nmr_rp_total = Sum(rp_emp_role_pay.rwrd_amt) AS ttlFROM rp_emp_role_payWHERE rp_emp_role_pay.emp_id=int_emp_id;
if nmr_rp_total is null then nmr_rp_total = 0;end if;
nmr_sg_total = nmr_bs_wage + nmr_nrml_total + nmr_ct_total + nmr_spclty_total + nmr_rp_total;
select into rec 'Base Wage', nmr_bs_wage; select into rec 'Skill Block', nmr_nrml_total; select into rec 'Cross Train', nmr_ct_total; select into rec 'Specialy', nmr_spclty_total; select into rec 'Role Pay', nmr_rp_total;
return NEXT rec;END;$body$LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
_________________________________________________________________
Get the Live.com Holiday Page for recipes, gift-giving ideas, and more.
www.live.com/?addtemplate=holiday