In my proc I have created a temp table:create table #JDE_EqmHrsDetailTable ( jde_job_date datetime, jde_cost_code nvarchar(15), jde_detail_hrs decimal(8,2), jde_eqm_id int, jde_eqm_name nvarchar(12) )
I am populating the temp table (1st 4 fields) with a call to a proc on a db2/400:insert into #JDE_EqmHrsDetailTable(jde_job_date, jde_cost_code, jde_detail_hrs, jde_eqm_id)Exec ('Call QGPL.get_eqmdet(?,?,?,?)', @JobNumber1, @ItemNumber1, @JdeDateFrom, @JdeDateTo) AT AS400SRV_MSDASQLThis works fine. Here is where I need to go and I am unceratin how to do this:I need update the last field in the temp table (jde_eqm_name). To do this I have to read the temp table and use the 4th field (jde_eqm_id) as a parm to call another proc on db2/400. I have created the proc Exec ('Call QGPL.get_itmmst (?)', jde_eqm_id) AT AS400SRV_MSDASQLThe call to the proc (on db2/400) will return jde_eqm_namecreate procedure get_itmmst (in @jde_eqm_id int) result set 1 language sql reads sql data begin declare c1 scroll cursor with return for select faapid as jde_eqm_name from vgiprddta/f1201la where fanumb = @jde_eqm_id group by faapid; open c1; end;
Read a temp table and update from another select
Posted - 2008-12-08 : 16:13:03
