Problem using While to loop from record

Question

Problem using While to loop from record

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

If these are validations that can't be done inline on your original query you could just make a cursor on that original query and loop through that. Do the validation and either insert or handle the exception as needed for each row.

That should be cleaner than trying to require based on row nums every loop.

luissantos SSCertifiable Points: 7391 More actions · Answer 2

Hello ZZartin,

Thanks for your reply.

I solve my problem like this and they work great.

DECLARE @temptable TABLE( i int identity(1,1) primary key,dostamp varchar(25), dilno numeric(10,0), Saldo Numeric(18,2) )

INSERT INTO @temptable 
SELECT 
 dostamp, dilno, sum(edebfin+edebana ) - sum(ecrefin + ecreana)[saldo]
FROM do where do.data>= '20200101' and do.data <= '20201231' 
group by dostamp, dilno,dinome
having sum(edebfin+edebana ) - sum(ecrefin + ecreana) <> 0 ;



DECLARE @i int 
set @i = 1

DECLARE @count int 
SET @count = (select COUNT(*) from @temptable)

WHILE(@i <= @count)
BEGIN

DECLARE @dostamp varchar(25) , @dilno numeric(10,0), @Saldo numeric(18,2)

Select @dostamp = dostamp, @dilno =dilno , @Saldo = Saldo from @temptable where i=@i

IF @Saldo < 0
BEGIN

INSERT INTO ML ([mlstamp] ,dinome ,dilno ,docnome ,adoc ,data ,mes ,dia , conta ,descricao ,rubrica ,deb ,cre ,edeb ,ecre ,dino ,descritivo ,cct ,debl ,vemdedc ,recapit ,ncont ,recapval ,docno ,doctipo ,ordem ,separa ,dostamp ,vemdoext ,erecapval ,lordem ,npt ,bastamp ,intid ,idorigem ,origem ,reco ,extracto ,oristamp ,olcodigo ,sgrupo ,grupo ,debm ,crem ,pncont ,conf1 ,conf2 ,ousrinis ,ousrdata ,ousrhora ,usrinis ,usrdata ,usrhora ,marcada ,codis ,czonag ,codisconf ,obs ,chave ,numcontrepres ,codprovincia ,cambio ,paistamp ,cecope ,operext ,tabiva ,iva ,ivareg ,basereg ,ndeciva ,modalidade ,reg ,u_stampori ,u_adim1 ,u_adim2 ,u_adim3 ,u_adim4 ,u_adim5 )
( SELECT TOP(1) LEFT(Newid(),23) [mlstamp] ,dinome ,dilno ,docnome ,adoc ,data ,mes ,dia ,'991' AS conta ,'Balanceamento Analitica' AS descricao ,rubrica ,ABS(@saldo * 200.482) as deb ,0.00 as cre ,ABS(@saldo) as edeb ,0.00 as ecre ,dino ,descritivo ,cct ,debl ,vemdedc ,recapit ,ncont ,recapval ,docno ,doctipo ,ordem ,separa ,dostamp ,vemdoext ,erecapval ,lordem ,npt ,bastamp ,intid ,idorigem ,origem ,reco ,extracto ,oristamp ,olcodigo ,sgrupo ,grupo ,debm ,crem ,pncont ,conf1 ,conf2 ,ousrinis ,ousrdata ,ousrhora ,usrinis ,usrdata ,usrhora ,marcada ,codis ,czonag ,codisconf ,obs ,chave ,numcontrepres ,codprovincia ,cambio ,paistamp ,cecope ,operext ,tabiva ,iva ,ivareg ,basereg ,ndeciva ,modalidade ,reg ,u_stampori ,u_adim1 ,u_adim2 ,u_adim3 ,u_adim4 ,u_adim5 from   ml  WHERE ml.dostamp = @dostamp ) ;
  
END
ELSE
BEGIN


INSERT INTO ML ([mlstamp] ,dinome ,dilno ,docnome ,adoc ,data ,mes ,dia , conta ,descricao ,rubrica ,deb ,cre ,edeb ,ecre ,dino ,descritivo ,cct ,debl ,vemdedc ,recapit ,ncont ,recapval ,docno ,doctipo ,ordem ,separa ,dostamp ,vemdoext ,erecapval ,lordem ,npt ,bastamp ,intid ,idorigem ,origem ,reco ,extracto ,oristamp ,olcodigo ,sgrupo ,grupo ,debm ,crem ,pncont ,conf1 ,conf2 ,ousrinis ,ousrdata ,ousrhora ,usrinis ,usrdata ,usrhora ,marcada ,codis ,czonag ,codisconf ,obs ,chave ,numcontrepres ,codprovincia ,cambio ,paistamp ,cecope ,operext ,tabiva ,iva ,ivareg ,basereg ,ndeciva ,modalidade ,reg ,u_stampori ,u_adim1 ,u_adim2 ,u_adim3 ,u_adim4 ,u_adim5 )
( SELECT TOP(1) LEFT(Newid(),23) [mlstamp] ,dinome ,dilno ,docnome ,adoc ,data ,mes ,dia ,'991' AS conta ,'Balanceamento Analitica' AS descricao ,rubrica ,0.00 as deb ,ABS(@saldo * 200.482) as cre ,0.00 as edeb ,ABS(@saldo) as ecre ,dino ,descritivo ,cct ,debl ,vemdedc ,recapit ,ncont ,recapval ,docno ,doctipo ,ordem ,separa ,dostamp ,vemdoext ,erecapval ,lordem ,npt ,bastamp ,intid ,idorigem ,origem ,reco ,extracto ,oristamp ,olcodigo ,sgrupo ,grupo ,debm ,crem ,pncont ,conf1 ,conf2 ,ousrinis ,ousrdata ,ousrhora ,usrinis ,usrdata ,usrhora ,marcada ,codis ,czonag ,codisconf ,obs ,chave ,numcontrepres ,codprovincia ,cambio ,paistamp ,cecope ,operext ,tabiva ,iva ,ivareg ,basereg ,ndeciva ,modalidade ,reg ,u_stampori ,u_adim1 ,u_adim2 ,u_adim3 ,u_adim4 ,u_adim5 from   ml  WHERE ml.dostamp = @dostamp ) ;
  
END


SET @i = @i + 1
END

Best regards,

LS