0
I did 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
Let’s read this: I have two important variables, which are @id_xfc_novo and @id_xfc. This block is intended as follows: I enter the new one with zero(0). And when I do the comparison, @id_xfc will have a non-zero value and in the comparison I do, it returns true, and I enter the variable and fill in my UPD. Then, after the UPD, I select the @id_xfc variable to take 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, if returns true and increments the other var and use in the UPD, if it is false (New with the same value as the old one), no increment and var with the old value, repeating in the UPD the same value (desired). This is not happening, ie to var @id_oit_max is always increasing, as if the condition is always true and is not. I have some repeated records. What is wrong in my logic?
I noticed that there is an error from time to time in the subquery and returns this way. I put a print to see is coming this way:
(1 line(s) affected) Message 512, Level 16, Status 1, Line 32 Subquery returned more than 1 value. This is not permitted when the subquery Follows =, != , <, <= , >, >= or when the subquery is used as an Expression.
old: 232 new: 243
(1 line(s) affected) Message 512, Level 16, Status 1, Line 32 Subquery returned more than 1 value. This is not permitted when the subquery Follows =, != , <, <= , >, >= or when the subquery is used as an Expression.
old: 232 new: 243
(1 line(s) affected) Message 512, Level 16, Status 1, Line 32 Subquery returned more than 1 value. This is not permitted when the subquery Follows =, != , <, <= , >, >= or when the subquery is used as an Expression.
old: 232 new: 243
(1 line(s) affected) Message 512, Level 16, Status 1, Line 32 Subquery returned more than 1 value. This is not permitted when the subquery Follows =, != , <, <= , >, >= or when the subquery is used as an Expression.
old: 232 new: 243