I've made this update:
declare
@rx_num varchar(20),
@id_oit_max int,
@id_exm_rea int,
@id_xfc_novo int,
@id_xfc int = 0
declare oit_cur cursor
for
select pl.rx_num, er.id_exm_rea, er.id_xfc from t_cmo_planilha_leitura pl
inner join T_CMO_EXAME_REALIZADO er on pl.ID_XFC = er.ID_XFC and pl.RX_NUM = er.NO_RX
open oit_cur
fetch next from oit_cur into @rx_num, @id_exm_rea, @id_xfc_novo
select @id_oit_max = max(id_oit) from t_cmo_oit1980
while @@fetch_status = 0
begin
if @id_xfc <> @id_xfc_novo
set @id_oit_max = @id_oit_max + 1
update t_cmo_planilha_leitura set id_oit = @id_oit_max
where rx_num = @rx_num
set @id_xfc = (select er.id_xfc from t_cmo_planilha_leitura pl
inner join T_CMO_EXAME_REALIZADO er on pl.ID_XFC = er.ID_XFC and pl.RX_NUM = er.NO_RX
where er.id_xfc = @id_xfc_novo)
fetch next from oit_cur into @rx_num, @id_exm_rea, @id_xfc_novo
end
close oit_cur
deallocate oit_cur
How to read this: I have two important variables, which are @id_xfc_novo and @id_xfc . This block the intention is as follows: I enter with new with value zero (0). And when I do the comparison, @ id_xfc will have a nonzero value and in the comparison I do, it returns true, and I increment the variable and fill in my UPD. Then, after the UPD, I hedge the variable @id_xfc to get the current value of the field. And soon after, I give a fetch next and the variable New, theoretically will come with another value or not. If it comes with another value, the if returns true and increments the other var and uses in the UPD, if it is false (New with same value as the old one), does not increment and the var is with the old value, repeating in the UPD the same value (wanted). This is not happening, that is, the var @id_oit_max is always increasing, as if the condition was always true and is not. I have some repeated records. What's wrong with my logic?
I noticed that it gives an error every now and then in the subquery and returns that way. I put a print to see this coming:
(1 row (s) affected) Message 512, Level 16, State 1, Line 32 Subquery returned more than 1 value. This is not permitted when the subquery follows =,! =, & lt ;, < =, & gt ;, > = or when the subquery is used as an expression.
old: 232 new: 243
(1 row (s) affected) Message 512, Level 16, State 1, Line 32 Subquery returned more than 1 value. This is not permitted when the subquery follows =,! =, & lt ;, < =, & gt ;, > = or when the subquery is used as an expression.
old: 232 new: 243
(1 row (s) affected) Message 512, Level 16, State 1, Line 32 Subquery returned more than 1 value. This is not permitted when the subquery follows =,! =, & lt ;, < =, & gt ;, > = or when the subquery is used as an expression.
old: 232 new: 243
(1 row (s) affected) Message 512, Level 16, State 1, Line 32 Subquery returned more than 1 value. This is not permitted when the subquery follows =,! =, & lt ;, < =, & gt ;, > = or when the subquery is used as an expression.
old: 232 new: 243