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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
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:
dostamp dilno row_num
ADM20031364447,005225399 2000099 1
ADM20031365571,851071416 2000100 2
ADM20040634771,728659519 3000070 3
ADM20040635306,695939080 3000072 4
FG20013034216,630189892 1000032 5
FG20013034217,706720187 1000033 6
FG20013034219,422088797 1000034 7
FG20020342241,504588145 1000046 8
FG20020433591,736201415 2000001 9
FG20020457560,244096324 1000043 10
FG20020644327,006253430 1000054 11
FG20021034272,112384239 1000050 12
FG20021034612,489579070 1000051 13
FG20021034759,285493011 1000052 14
FG20021035329,123128988 1000053 15
FG20021067599,240759009 2000018 16
FG20021242813,247603585 1000098 17
FG20021266303,174797122 1000113 18
FG20021267674,682560336 1000124 19
FG20021267899,393058867 1000125 20
FG20021440187,130472701 2000034 21
FG20021734002,972600466 1000073 22
FG20022145275,874463365 2000047 23
FG20022656106,264825377 2000049 24
FG20022737344,213442864 2000052 25
FG20030452041,608896898 3000010 26
FG20030460053,916652158 1000154 27
FG20030460510,529241160 1000155 28
FG20030461985,319244171 1000156 29
FG20030462146,136277203 1000157 30
FG20030462978,340014578 1000158 31
FG20030464378,625347202 1000162 32
FG20030563317,029710925 1000165 33
FG20031060259,663524667 2000067 34
FG20031242762,077902547 2000081 35
FG20031243065,275275324 2000082 36
FG20031243270,468742338 2000083 37
FG20031243389,833412864 2000084 38
FG20031243696,778996996 2000085 39
FG20031243863,766518574 2000086 40
FG20031363179,669113599 2000098 41
FG20032466772,921070475 3000034 42
FG20040336767,627710113 2000108 43
FG20040342246,894061857 2000116 44
FG20040357317,205498955 4000005 45
FG20040654216,430600611 3000073 46
FG20040654671,463907303 3000074 47
FG20040743421,910465886 2000130 48
FG20040933489,436922878 2000132 49
FG20040934514,045777874 2000133 50
FG20040934646,240629573 3000041 51
FG20041462470,733787398 3000105 52
FG20041540844,076658022 4000035 53
FG20041737680,447808769 3000055 54
FG20041739195,978405660 3000056 55
FG20041752338,891529133 4000027 56
FG20041753187,369357711 4000028 57
FG20041753663,020553024 4000029 58
FG20041754128,942695497 4000030 59
FG20041756430,727524326 2000143 60
FG20042038883,710170180 3000081 61
FG20042039022,344407027 3000082 62
FG20042039115,672689839 3000083 63
FG20042039259,294642823 3000084 64
FG20042039375,017514967 3000085 65
FG20042040270,219231632 3000086 66
FG20042053583,302235327 4000045 67
FG20042053737,377411031 4000046 68
FG20042053853,798763436 4000047 69
FG20042053948,295470000 4000048 70
FG20042062683,595403278 3000122 71
FG20042063092,133740451 3000123 72
FG20042063190,962575113 3000124 73
FG20042064804,937758743 3000127 74
FG20042065017,608082207 3000128 75
FG20042133324,513391077 3000129 76
FG20042133477,347882169 3000130 77
FG20042133569,200832414 3000131 78
FG20042164747,261224345 4000034 79
FG20042241793,974311098 3000135 80
FG20042242150,376023215 3000136 81
FG20042244545,473878729 3000137 82
FG20042244782,837513708 3000138 83
FG20042441790,365768879 4000038 84
FG20042455585,690025944 3000155 85
FG20042456165,749736749 3000156 86
FG20042855949,802700071 4000041 87
FG20042951431,575382614 4000070 88
FG20051151867,009273315 4000063 89
FG20051350487,337198068 3000167 90
FG20051351818,930667889 3000168 91
FG20051453312,703340759 4000078 92
FG20051461439,573682656 4000103 93
FG20051536425,661650229 4000112 94
FG20051536648,111931794 4000113 95
FG20051536798,414955874 4000114 96
FG20051536971,705869054 4000115 97
FG20051954531,532620008 5000035 98
FG20052938410,131445944 5000051 99
FG20052962401,913019884 4000135 100
FG20060138414,623532213 5000056 101
FG20060154237,719348215 4000139 102
FG20060154928,981125351 4000141 103
FG20060264764,924933155 4000086 104
FG20060265172,770496328 4000087 105
FG20060342023,351411999 5000076 106
FG20060343708,453260913 5000061 107
FG20060352889,201544070 5000087 108
FG20060354766,430529522 5000091 109
FG20060355159,615439397 5000092 110
FG20060355287,239282543 5000093 111
FG20060355370,405464801 5000094 112
FG20060360724,362704441 5000095 113
FG20060363537,197028875 4000146 114
FG20060536783,431544562 6000010 115
FG20060842272,009125632 4000147 116
FG20060951863,385278170 5000079 117
FG20061662041,169790997 5000082 118
FG20061663483,562182295 5000083 119
FG20061664353,161859385 5000084 120
FG20061833438,031202490 6000033 121
FG20061936068,950161368 6000050 122
FG20061936390,193139633 6000051 123
FG20061936557,379087499 6000052 124
FG20061936694,901575101 6000053 125
FG20061936865,457950246 6000054 126
FG20062442578,351593415 6000037 127
FG20062651214,855232327 6000039 128
FG20062933593,882767584 6000062 129
FG20070232742,484190761 6000076 130
FG20070633843,216486984 6000078 131
FG20070945018,989943432 6000082 132
FG20071336963,665314796 6000091 133
FG20071536381,905663225 6000119 134
FG20071536527,781903469 6000120 135
FG20071652340,788400815 7000035 136
FG20071736842,876907325 6000135 137
FG20071737633,283498140 6000146 138
FG20071756167,928349671 6000153 139
FG20071757147,992517948 6000158 140
FG20071757427,689242713 6000159 141
FG20071759352,613860045 7000039 142
FG20071759353,583097995 7000040 143
FG20072051002,252816415 7000045 144
FG20072052114,096652097 7000039 145
FG20072052215,418571907 7000040 146
FG20072052324,742588494 7000041 147
FG20072052438,441481107 7000042 148
FG20072234661,570682793 6000161 149
FG20072255630,130536931 7000055 150
FG20072255633,929919530 7000056 151
FG20072458619,548933659 7000072 152
FG20072736077,798481517 5000147 153
FG20072737342,461043863 6000164 154
FG20072937137,595765778 7000065 155
FG20072937367,773393439 7000066 156
FG20072939647,576956595 7000070 157
FG20072939814,263884512 7000071 158
FG20080353497,539548603 7000092 159
FG20080353584,353698505 7000093 160
FG20080358732,325495839 7000091 161
FG20080552943,367092249 7000099 162
FG20080552943,696282615 7000100 163
FG20080634719,668273818 7000157 164
FG20080634847,011103579 7000158 165
FG20080635000,484895966 7000159 166
FG20080635141,477703070 7000160 167
FG20080635259,304461553 7000161 168
FG20080636906,094288290 7000104 169
FG20080751148,067955631 7000183 170
FG20080751263,054150010 7000184 171
FG20080751511,984014999 7000185 172
FG20080755376,766404524 7000210 173
FG20080757621,574294279 7000114 174
FG20080757623,737717200 8000015 175
FG20082659037,677171265 7000123 176
FG20082661663,042052126 7000125 177
FG20082735708,825717053 7000222 178
FG20082737097,822500051 7000223 179
FG20082737443,176435042 7000224 180
FG20082754366,274609060 8000027 181
FG20082852598,326109668 8000019 182
FG20083139175,826600558 8000031 183
FG20083139833,323150433 8000033 184
FG20090161884,773017919 7000229 185
FG20090165312,092588053 7000128 186
FG20090233354,107642587 8000043 187
FG20090234546,510988471 8000046 188
FG20090357546,459310645 8000065 189
FG20090357681,405025930 8000066 190
FG20090358493,152258820 8000067 191
FG20090358697,441119996 8000068 192
FG20090358900,267911687 8000069 193
FG20090455277,113060550 8000052 194
FG20090455278,913349744 9000008 195
FG20090457518,918775513 8000075 196
FG20090457900,674232790 8000079 197
FG20090742565,362121273 8000101 198
FG20090742894,218936027 8000102 199
FG20090743007,756656003 8000103 200
FG20091834403,164680098 9000029 201
FG20092254224,416612673 9000043 202
FG20092344232,542659267 9000034 203
FG20092351874,017245782 9000039 204
FG20092352005,650952657 9000040 205
FG20092352222,023151505 9000041 206
FG20092535395,373057901 9000050 207
FG20092958238,719193241 9000063 208
FG20100136100,562689561 9000073 209
FG20100863361,611333571 9000061 210
FG20100864625,951393947 9000062 211
FG20100866372,700627425 9000063 212
FG20100867177,573648908 9000069 213
FG20100867268,205061649 9000070 214
FG20100954630,881444331 9000083 215
FG20101250816,053270440 9000097 216
FG20101251581,936974925 9000098 217
FG20101253381,953085216 8000112 218
FG20101253769,830785307 8000113 219
FG20101254062,287582667 8000114 220
FG20101259140,907514018 9000100 221
FG20101261164,706008473 9000103 222
FG20101261367,095956112 9000104 223
FG20101357138,751302427 9000125 224
FG20101357248,201599152 9000126 225
FG20101952316,598482336 10000029 226
FG20101952318,273779045 10000030 227
FG20102137680,419323977 10000038 228
FG20102138231,584132626 10000039 229
FG20102138363,700714566 10000040 230
FG20102138578,232361625 10000041 231
FG20102151345,215385550 10000042 232
FG20102159020,396434796 10000045 233
FG20102163392,897437640 10000053 234
FG20102163649,346329389 10000054 235
FG20102333249,149757383 10000053 236
FG20102338114,724721147 10000056 237
FG20102950625,696502790 10000060 238
FG20110463788,488013368 10000103 239
FG20110464087,214463280 10000104 240
FG20110536351,734614431 10000105 241
FG20110536463,820478013 10000106 242
FG20110536599,587783506 10000107 243
FG20110536933,506488594 10000108 244
FG20110537037,386438121 10000109 245
FG20110537154,839060442 10000110 246
FG20110537806,124647106 10000111 247
FG20110544422,537962211 10000121 248
FG20111154980,646738123 10000096 249
FG20111352047,929207629 10000137 250
FG20111352987,791306441 11000024 251
FG20111858189,614064947 9000149 252
FG20111859997,017312986 10000142 253
FG20112053194,584710310 11000048 254
FG20112333354,635392211 11000033 255
FG20112662852,422782429 11000046 256
FG20112666617,810755075 11000053 257
FG20112667138,889827216 11000076 258
FG20112737642,575618782 11000078 259
FG20120432903,409734988 11000100 260
FG20120456726,153062641 11000101 261
FG20121033099,066546124 11000105 262
FG20121135180,010741105 11000083 263
FG20121136064,971567330 11000084 264
FG20121137764,632967194 11000085 265
FG20121138033,493480445 11000086 266
FG20121138397,190276402 11000087 267
FG20121140860,440780515 11000100 268
FG20121141059,508380769 11000101 269
FG20121443941,997755898 11000172 270
FG20121454810,508073309 11000186 271
FG20121458100,971748882 11000188 272
FG20121458256,492891587 11000189 273
FG20121458706,052061876 11000191 274
FG20121734363,675640469 11000192 275
FG20121735449,666772740 12000034 276
FG20122133332,320989543 12000054 277
FG20122150652,127327043 12000043 278
FG21010555822,681657601 12000061 279
FG21010555822,991502382 12000062 280
FG21010643860,218021662 12000073 281
FG21010761728,522586760 12000070 282
FG21011134248,356392684 12000081 283
FG21011135573,723511120 12000082 284
FG21011135668,431981637 12000083 285
FG21011137711,067610266 12000086 286
FG21011157987,463158899 12000090 287
FG21012662353,507346351 12000164 288
FG21020351269,592631817 3000169 289
FG21020356309,402847024 12000174 290
FG21020356881,342179281 12000175 291
FG21020362110,656812341 12000177 292
FG21020362239,806518117 12000178 293
FG21020363189,377158822 12000179 294
FG21020363712,107785052 12000180 295
FG21020364026,247006928 12000181 296
FG21031949018,455127210 1000186 297That 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