Problem using While to loop from record

  • Hello,

    I have build this While Statment to loop for record based on primary key DOSTAMP for later create an insert statment for each dostamp(In practice, for each document that have this primary key).

    I do something wrong because some dostamp are repeated on the print test

    This is my SQL While loop :

    DECLARE @iterator int
    DECLARE @MaxIterator int
    DECLARE @dostamp varchar(25)
    DECLARE @dilno int
    DECLARE @dinome varchar(30)


    SELECT @MaxIterator = max(x.row_num), @iterator = min(x.row_num)
    from
    (
    select dostamp,dilno,
    ROW_NUMBER() OVER ( Order by datepart(yy,do.data), dostamp) row_num
    FROM DO
    where datepart(yy,do.data) = 2020
    group by datepart(yy,do.data), dostamp,dilno
    having sum((edebfin+edebana ) - (ecrefin + ecreana)) <> 0

    ) X ;

    BEGIN TRY
    WHILE @Iterator <= @MaxIterator
    BEGIN

    SELECT
    Distinct @dostamp = dostamp , @dilno = dilno, @dinome = dinome
    FROM DO
    where datepart(yy,do.data) = 2020
    group by dostamp, dilno,dinome
    having sum(edebfin+edebana ) - sum(ecrefin + ecreana) <> 0 ;

    print cast(@iterator as varchar) + ' - ' + @dostamp + 'nrlanc: ' + CAST(@dilno as varchar) + '- ' + @dinome
    SET @Iterator = @Iterator +1;

    END
    END TRY

    BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber ,
    ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;

    now the print console return sometimes for different Row_num the same dostamp and this is not possible, i miss something.

    here the result:

    test

    As you can see the row_numb change for each line, but the primary key are repeated, and this is not possible because is unique for each document.

     

    Can someone could give me some help, because sincerely i don´t understand why this happen.

    Best regards,

    LS

  • Sorry, i forget to mencioned, this query above return for each row_num different dostamp and this is correct:

    SELECT 
    Distinct dostamp ,dilno,dinome
    FROM DO
    where datepart(yy,do.data) = 2020 --and
    group by dostamp, dilno,dinome
    having sum(edebfin+edebana ) - sum(ecrefin + ecreana) <> 0
    order by dilno asc;

    Thanks again,

    LS

  • You don't have any WHERE condition in the SELECT in the loop.  Thus, so it's essentially random which row SQL will return.

    Btw, don't use functions against table columns in the WHERE clause if you can avoid it, so the WHERE should be:

    ...

    where yy.do_data >= '20200101' and yy.do_data < '20210101'

    ...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Hy Scott,

     

    Thanks for your reply and useful advises, but what kind of filter i must put in the Where condition inside the Begin ?

    i think that the field dostamp (Primary key) is the best choice? do i must declare a variable for them ?

    could post me the small part for the Where, please.

    Thanks,

    LS

  • luissantos wrote:

    Hy Scott,

    Thanks for your reply and useful advises, but what kind of filter i must put in the Where condition inside the Begin ?

    i think that the field dostamp (Primary key) is the best choice? do i must declare a variable for them ?

    could post me the small part for the Where, please.

    Thanks,

    LS

    I appreciate your thanks.  I did put the code for the WHERE condition in my last post.  Please review that post again.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • What is the primary key of the DO table if you script out the full key?

  • Hello ZZartin,

    The primary key of my table DO is : DOSTAMP varchar(25)

    Best regards,

    LS

  • What is returned by your original query?

     

    select dostamp,dilno,
    ROW_NUMBER() OVER ( Order by datepart(yy,do.data), dostamp) row_num
    FROM DO
    where datepart(yy,do.data) = 2020
    group by datepart(yy,do.data), dostamp,dilno
    having sum((edebfin+edebana ) - (ecrefin + ecreana)) <> 0
  • Hi,

    The return values are:

    dostampdilnorow_num
    ADM20031364447,005225399 20000991
    ADM20031365571,851071416 20001002
    ADM20040634771,728659519 30000703
    ADM20040635306,695939080 30000724
    FG20013034216,630189892 10000325
    FG20013034217,706720187 10000336
    FG20013034219,422088797 10000347
    FG20020342241,504588145 10000468
    FG20020433591,736201415 20000019
    FG20020457560,244096324 100004310
    FG20020644327,006253430 100005411
    FG20021034272,112384239 100005012
    FG20021034612,489579070 100005113
    FG20021034759,285493011 100005214
    FG20021035329,123128988 100005315
    FG20021067599,240759009 200001816
    FG20021242813,247603585 100009817
    FG20021266303,174797122 100011318
    FG20021267674,682560336 100012419
    FG20021267899,393058867 100012520
    FG20021440187,130472701 200003421
    FG20021734002,972600466 100007322
    FG20022145275,874463365 200004723
    FG20022656106,264825377 200004924
    FG20022737344,213442864 200005225
    FG20030452041,608896898 300001026
    FG20030460053,916652158 100015427
    FG20030460510,529241160 100015528
    FG20030461985,319244171 100015629
    FG20030462146,136277203 100015730
    FG20030462978,340014578 100015831
    FG20030464378,625347202 100016232
    FG20030563317,029710925 100016533
    FG20031060259,663524667 200006734
    FG20031242762,077902547 200008135
    FG20031243065,275275324 200008236
    FG20031243270,468742338 200008337
    FG20031243389,833412864 200008438
    FG20031243696,778996996 200008539
    FG20031243863,766518574 200008640
    FG20031363179,669113599 200009841
    FG20032466772,921070475 300003442
    FG20040336767,627710113 200010843
    FG20040342246,894061857 200011644
    FG20040357317,205498955 400000545
    FG20040654216,430600611 300007346
    FG20040654671,463907303 300007447
    FG20040743421,910465886 200013048
    FG20040933489,436922878 200013249
    FG20040934514,045777874 200013350
    FG20040934646,240629573 300004151
    FG20041462470,733787398 300010552
    FG20041540844,076658022 400003553
    FG20041737680,447808769 300005554
    FG20041739195,978405660 300005655
    FG20041752338,891529133 400002756
    FG20041753187,369357711 400002857
    FG20041753663,020553024 400002958
    FG20041754128,942695497 400003059
    FG20041756430,727524326 200014360
    FG20042038883,710170180 300008161
    FG20042039022,344407027 300008262
    FG20042039115,672689839 300008363
    FG20042039259,294642823 300008464
    FG20042039375,017514967 300008565
    FG20042040270,219231632 300008666
    FG20042053583,302235327 400004567
    FG20042053737,377411031 400004668
    FG20042053853,798763436 400004769
    FG20042053948,295470000 400004870
    FG20042062683,595403278 300012271
    FG20042063092,133740451 300012372
    FG20042063190,962575113 300012473
    FG20042064804,937758743 300012774
    FG20042065017,608082207 300012875
    FG20042133324,513391077 300012976
    FG20042133477,347882169 300013077
    FG20042133569,200832414 300013178
    FG20042164747,261224345 400003479
    FG20042241793,974311098 300013580
    FG20042242150,376023215 300013681
    FG20042244545,473878729 300013782
    FG20042244782,837513708 300013883
    FG20042441790,365768879 400003884
    FG20042455585,690025944 300015585
    FG20042456165,749736749 300015686
    FG20042855949,802700071 400004187
    FG20042951431,575382614 400007088
    FG20051151867,009273315 400006389
    FG20051350487,337198068 300016790
    FG20051351818,930667889 300016891
    FG20051453312,703340759 400007892
    FG20051461439,573682656 400010393
    FG20051536425,661650229 400011294
    FG20051536648,111931794 400011395
    FG20051536798,414955874 400011496
    FG20051536971,705869054 400011597
    FG20051954531,532620008 500003598
    FG20052938410,131445944 500005199
    FG20052962401,913019884 4000135100
    FG20060138414,623532213 5000056101
    FG20060154237,719348215 4000139102
    FG20060154928,981125351 4000141103
    FG20060264764,924933155 4000086104
    FG20060265172,770496328 4000087105
    FG20060342023,351411999 5000076106
    FG20060343708,453260913 5000061107
    FG20060352889,201544070 5000087108
    FG20060354766,430529522 5000091109
    FG20060355159,615439397 5000092110
    FG20060355287,239282543 5000093111
    FG20060355370,405464801 5000094112
    FG20060360724,362704441 5000095113
    FG20060363537,197028875 4000146114
    FG20060536783,431544562 6000010115
    FG20060842272,009125632 4000147116
    FG20060951863,385278170 5000079117
    FG20061662041,169790997 5000082118
    FG20061663483,562182295 5000083119
    FG20061664353,161859385 5000084120
    FG20061833438,031202490 6000033121
    FG20061936068,950161368 6000050122
    FG20061936390,193139633 6000051123
    FG20061936557,379087499 6000052124
    FG20061936694,901575101 6000053125
    FG20061936865,457950246 6000054126
    FG20062442578,351593415 6000037127
    FG20062651214,855232327 6000039128
    FG20062933593,882767584 6000062129
    FG20070232742,484190761 6000076130
    FG20070633843,216486984 6000078131
    FG20070945018,989943432 6000082132
    FG20071336963,665314796 6000091133
    FG20071536381,905663225 6000119134
    FG20071536527,781903469 6000120135
    FG20071652340,788400815 7000035136
    FG20071736842,876907325 6000135137
    FG20071737633,283498140 6000146138
    FG20071756167,928349671 6000153139
    FG20071757147,992517948 6000158140
    FG20071757427,689242713 6000159141
    FG20071759352,613860045 7000039142
    FG20071759353,583097995 7000040143
    FG20072051002,252816415 7000045144
    FG20072052114,096652097 7000039145
    FG20072052215,418571907 7000040146
    FG20072052324,742588494 7000041147
    FG20072052438,441481107 7000042148
    FG20072234661,570682793 6000161149
    FG20072255630,130536931 7000055150
    FG20072255633,929919530 7000056151
    FG20072458619,548933659 7000072152
    FG20072736077,798481517 5000147153
    FG20072737342,461043863 6000164154
    FG20072937137,595765778 7000065155
    FG20072937367,773393439 7000066156
    FG20072939647,576956595 7000070157
    FG20072939814,263884512 7000071158
    FG20080353497,539548603 7000092159
    FG20080353584,353698505 7000093160
    FG20080358732,325495839 7000091161
    FG20080552943,367092249 7000099162
    FG20080552943,696282615 7000100163
    FG20080634719,668273818 7000157164
    FG20080634847,011103579 7000158165
    FG20080635000,484895966 7000159166
    FG20080635141,477703070 7000160167
    FG20080635259,304461553 7000161168
    FG20080636906,094288290 7000104169
    FG20080751148,067955631 7000183170
    FG20080751263,054150010 7000184171
    FG20080751511,984014999 7000185172
    FG20080755376,766404524 7000210173
    FG20080757621,574294279 7000114174
    FG20080757623,737717200 8000015175
    FG20082659037,677171265 7000123176
    FG20082661663,042052126 7000125177
    FG20082735708,825717053 7000222178
    FG20082737097,822500051 7000223179
    FG20082737443,176435042 7000224180
    FG20082754366,274609060 8000027181
    FG20082852598,326109668 8000019182
    FG20083139175,826600558 8000031183
    FG20083139833,323150433 8000033184
    FG20090161884,773017919 7000229185
    FG20090165312,092588053 7000128186
    FG20090233354,107642587 8000043187
    FG20090234546,510988471 8000046188
    FG20090357546,459310645 8000065189
    FG20090357681,405025930 8000066190
    FG20090358493,152258820 8000067191
    FG20090358697,441119996 8000068192
    FG20090358900,267911687 8000069193
    FG20090455277,113060550 8000052194
    FG20090455278,913349744 9000008195
    FG20090457518,918775513 8000075196
    FG20090457900,674232790 8000079197
    FG20090742565,362121273 8000101198
    FG20090742894,218936027 8000102199
    FG20090743007,756656003 8000103200
    FG20091834403,164680098 9000029201
    FG20092254224,416612673 9000043202
    FG20092344232,542659267 9000034203
    FG20092351874,017245782 9000039204
    FG20092352005,650952657 9000040205
    FG20092352222,023151505 9000041206
    FG20092535395,373057901 9000050207
    FG20092958238,719193241 9000063208
    FG20100136100,562689561 9000073209
    FG20100863361,611333571 9000061210
    FG20100864625,951393947 9000062211
    FG20100866372,700627425 9000063212
    FG20100867177,573648908 9000069213
    FG20100867268,205061649 9000070214
    FG20100954630,881444331 9000083215
    FG20101250816,053270440 9000097216
    FG20101251581,936974925 9000098217
    FG20101253381,953085216 8000112218
    FG20101253769,830785307 8000113219
    FG20101254062,287582667 8000114220
    FG20101259140,907514018 9000100221
    FG20101261164,706008473 9000103222
    FG20101261367,095956112 9000104223
    FG20101357138,751302427 9000125224
    FG20101357248,201599152 9000126225
    FG20101952316,598482336 10000029226
    FG20101952318,273779045 10000030227
    FG20102137680,419323977 10000038228
    FG20102138231,584132626 10000039229
    FG20102138363,700714566 10000040230
    FG20102138578,232361625 10000041231
    FG20102151345,215385550 10000042232
    FG20102159020,396434796 10000045233
    FG20102163392,897437640 10000053234
    FG20102163649,346329389 10000054235
    FG20102333249,149757383 10000053236
    FG20102338114,724721147 10000056237
    FG20102950625,696502790 10000060238
    FG20110463788,488013368 10000103239
    FG20110464087,214463280 10000104240
    FG20110536351,734614431 10000105241
    FG20110536463,820478013 10000106242
    FG20110536599,587783506 10000107243
    FG20110536933,506488594 10000108244
    FG20110537037,386438121 10000109245
    FG20110537154,839060442 10000110246
    FG20110537806,124647106 10000111247
    FG20110544422,537962211 10000121248
    FG20111154980,646738123 10000096249
    FG20111352047,929207629 10000137250
    FG20111352987,791306441 11000024251
    FG20111858189,614064947 9000149252
    FG20111859997,017312986 10000142253
    FG20112053194,584710310 11000048254
    FG20112333354,635392211 11000033255
    FG20112662852,422782429 11000046256
    FG20112666617,810755075 11000053257
    FG20112667138,889827216 11000076258
    FG20112737642,575618782 11000078259
    FG20120432903,409734988 11000100260
    FG20120456726,153062641 11000101261
    FG20121033099,066546124 11000105262
    FG20121135180,010741105 11000083263
    FG20121136064,971567330 11000084264
    FG20121137764,632967194 11000085265
    FG20121138033,493480445 11000086266
    FG20121138397,190276402 11000087267
    FG20121140860,440780515 11000100268
    FG20121141059,508380769 11000101269
    FG20121443941,997755898 11000172270
    FG20121454810,508073309 11000186271
    FG20121458100,971748882 11000188272
    FG20121458256,492891587 11000189273
    FG20121458706,052061876 11000191274
    FG20121734363,675640469 11000192275
    FG20121735449,666772740 12000034276
    FG20122133332,320989543 12000054277
    FG20122150652,127327043 12000043278
    FG21010555822,681657601 12000061279
    FG21010555822,991502382 12000062280
    FG21010643860,218021662 12000073281
    FG21010761728,522586760 12000070282
    FG21011134248,356392684 12000081283
    FG21011135573,723511120 12000082284
    FG21011135668,431981637 12000083285
    FG21011137711,067610266 12000086286
    FG21011157987,463158899 12000090287
    FG21012662353,507346351 12000164288
    FG21020351269,592631817 3000169289
    FG21020356309,402847024 12000174290
    FG21020356881,342179281 12000175291
    FG21020362110,656812341 12000177292
    FG21020362239,806518117 12000178293
    FG21020363189,377158822 12000179294
    FG21020363712,107785052 12000180295
    FG21020364026,247006928 12000181296
    FG21031949018,455127210 1000186297

    That is correct without no DOSTAMP primary key repeated.

    LS

  • Hi Scott,

    I´am to slow today 🙂

    My changes for the Whole While query is this:

    DECLARE @iterator int
    DECLARE @MaxIterator int
    DECLARE @dostamp varchar(25)
    DECLARE @dilno int
    DECLARE @dinome varchar(30)
    declare @rn int


    SELECT @MaxIterator = max(x.row_num), @iterator = min(x.row_num)
    from
    (
    select dostamp,dilno,
    ROW_NUMBER() OVER ( Order by datepart(yy,do.data), dostamp) row_num
    FROM DO
    where do.data>= '20200101' and do.data <= '20201231'
    group by datepart(yy,do.data), dostamp,dilno
    having sum((edebfin+edebana ) - (ecrefin + ecreana)) <> 0

    ) X ;


    BEGIN TRY
    WHILE @Iterator <= @MaxIterator
    BEGIN

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

    print cast(@iterator as varchar) + ';' + @dostamp + ';' + CAST(@dilno as varchar) + ';' + @dinome
    SET @Iterator = @Iterator +1;

    END
    END TRY

    BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber ,
    ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;

    But my primary key DOSTAMP are repeated again.

    sorry for the inconvenience, but you can see what is wrong with the query.

    Many thanks,

    LS

  • Why are you using the while loop at all?

    Would just this work for the entire thing if you just want a list of records with a row num?

     

    select cast(ROW_NUMBER() OVER ( Order by datepart(yy,do.data), dostamp) as varchar) + ';' + dostamp + ';' + CAST(dilno as varchar) + ';' + dinome

    FROM DO
    where do.data>= '20200101' and do.data <= '20201231'
    group by datepart(yy,do.data), dostamp,dilno,dinome
    having sum((edebfin+edebana ) - (ecrefin + ecreana)) <> 0
  • Hi, ZZartin,

    I don´t want just a list of records.

    I want the list of records to use for making an INSERT (New line) on the Table ML that is the line of accounts of my table DO where the relation is :  ML.DOSTAMP = DO.DOSTAMP.

    as I only have to insert 1 line for each document, it is much more practical if you do it this way.

    Best regards,

    LS

  • Hmm... you can insert records directly from a select statement, there's no need to insert them one at a time in a loop.

  • ZZartin is right.    It's not only easier to code, it's faster when executing.

    INSERT INTO TargetTable (ColX, ColY, ColZ)
    SELECT ColA, ColB, ColC
    FROM SourceTable
    WHERE ...

    Plenty of examples are found here:

    https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Hi Dixie and ZZartin,

     

    I know that using this script is much faster, I use this  technique many times, but i this particulary case i need to make several validations before Inserting, like account, current account balance by account to define Debit or Credit and more one or two, that´s why i need to use the cycle WHILE.


    INSERT INTO TargetTable (ColX, ColY, ColZ)
    SELECT ColA, ColB, ColC
    FROM SourceTable
    WHERE ...

    Best regards,

    LS

Viewing 15 posts - 1 through 15 (of 16 total)

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