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:
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) A socialist is someone who will give you the shirt off *someone else's* back.
-
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) A socialist is someone who will give you the shirt off *someone else's* back.
-
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 1000186297That 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