I am trying to insert records into a history table. One of the columns in the table must be set to a specific value in the first record inserted and then incremented by 1 for each new record inserted. The column is not an identity column. In the code below I have commented out the "insert" statement because I want to view my results before running the "insert". @newval is the variable I am trying to increment for each new record. My Code so far:
Use GoldTest
Go
Update esxgenid
set val=val where module = 'hr' and keyname = 'eshshstd'
declare @maxval int
select @maxval = val from esxgenid where module = 'hr' and keyname = 'eshshstd'
declare @newval Int
set @newval = @maxval + 1
While
(Select sal_group from esypostr) = 'service' and
(Select step from esyapptr) ='G' and
(Select active_date from esygradr) = '07/01/2006' and
(Select step from esygradd) = 'H'
Begin
select esyemplr.emp_id,esyapptr.appoint_id,@newval,'07/01/2006',esypostr.pos_no,esyapptr.hourly_rate,
esygradd.hourly_rate,'MOU Change-New Step A','03/01/2006',esyapptr.grade_code,esyapptr.step,
esyapptr.grade_code,esygradd.step
/*insert into eshshstd (shst_emp_id,shst_appoint_id,shst_id,shst_chg_date,shst_pos_no,
shst_old_hrly_rate,shst_hourly_rate,shst_description,shst_entered_date,
shst_old_grade,shst_old_step,shst_grade_code,shst_step)*/
from esyapptr inner join esypostr on esyapptr.pos_id=esypostr.pos_id
inner join esygradr on esyapptr.grade_code= esygradr.grade_code
inner join esyemplr on esyapptr.emp_id = esyemplr.emp_id
inner join esygradd on esygradr.grade_id = esygradd.grade_id
where esypostr.sal_group = 'service' and
esyapptr.step ='G' and
esygradr.active_date = '07/01/2006' and
esygradd.step = 'H'
set @newval = @newval + 1
End
When I execute the above code I get the following error:
(2 row(s) affected)
Server: Msg 512, Level 16, State 1, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I must be approaching the from the wrong angle.
Can anyone help me with this?
Thanks,
MrBill
Use GoldTest
Go
Update esxgenid
set val=val where module = 'hr' and keyname = 'eshshstd'
declare @maxval int
select @maxval = val from esxgenid where module = 'hr' and keyname = 'eshshstd'
declare @newval Int
set @newval = @maxval + 1
While
(Select sal_group from esypostr) = 'service' and
(Select step from esyapptr) ='G' and
(Select active_date from esygradr) = '07/01/2006' and
(Select step from esygradd) = 'H'
Begin
select esyemplr.emp_id,esyapptr.appoint_id,@newval,'07/01/2006',esypostr.pos_no,esyapptr.hourly_rate,
esygradd.hourly_rate,'MOU Change-New Step A','03/01/2006',esyapptr.grade_code,esyapptr.step,
esyapptr.grade_code,esygradd.step
/*insert into eshshstd (shst_emp_id,shst_appoint_id,shst_id,shst_chg_date,shst_pos_no,
shst_old_hrly_rate,shst_hourly_rate,shst_description,shst_entered_date,
shst_old_grade,shst_old_step,shst_grade_code,shst_step)*/
from esyapptr inner join esypostr on esyapptr.pos_id=esypostr.pos_id
inner join esygradr on esyapptr.grade_code= esygradr.grade_code
inner join esyemplr on esyapptr.emp_id = esyemplr.emp_id
inner join esygradd on esygradr.grade_id = esygradd.grade_id
where esypostr.sal_group = 'service' and
esyapptr.step ='G' and
esygradr.active_date = '07/01/2006' and
esygradd.step = 'H'
set @newval = @newval + 1
End
When I execute the above code I get the following error:
(2 row(s) affected)
Server: Msg 512, Level 16, State 1, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I must be approaching the from the wrong angle.
Can anyone help me with this?
Thanks,
MrBill
