InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 14:30:47 GMT20http://www.sqlservercentral.com/Forums/Topic1370624-392-1.aspxHello CarlosYour Portuguese is OK, Carlos.For everybody to answer me,i solve my problem after some changes on my original cursor and i test ir with 2 "referencia":DECLARE cur1 CURSOR LOCAL FORWARD_ONLY FOR SELECT referencia, TAMANHO, LEFT(NEWID(),23) as 'sgtstamp', min(QTD) AS 'SORTIMENTO', ROW_NUMBER() OVER(PARTITION BY referencia ORDER BY referencia,tamanho ASC) AS 'Rownumb' FROM arttamcor WHERE referencia IN ('29913','30110') <-- 2 articles GROUP BY referencia, TAMANHO ORDER BY referencia,tamanho,rownumb-- Cursor Variables --DECLARE @ref VARCHAR(18)DECLARE @TAMANHO VARCHAR(25)DECLARE @sgtstamp VARCHAR(25)DECLARE @SORTIMENTO INTDECLARE @rownumb INTDECLARE @TAM VARCHAR(25)--New variable to know if @ref changedDECLARE @refactual VARCHAR(18) -- Cursor Variables (END) --OPEN cur1FETCH NEXT FROM cur1 INTO @ref, @Tamanho, @sgtstamp, @SORTIMENTO, @rownumbSET @refactual = ''WHILE @@FETCH_STATUS = 0 BEGIN IF @refactual <> @ref BEGIN INSERT INTO SGT (sgtstamp, ref, Tam,pos,dimcol, ousrdata, ousrhora, ousrinis, usrdata, usrhora, usrinis) VALUES (RTRIM(LEFT(NEWID(),23)),RTRIM(@ref), 'Z', 99,@SORTIMENTO, CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()), 'ADM', CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()),'ADM') SET @refactual = @ref END INSERT INTO SGT (sgtstamp, ref, Tam,pos,dimcol, ousrdata, ousrhora, ousrinis, usrdata, usrhora, usrinis) VALUES (RTRIM(@sgtstamp),RTRIM(@ref), RTRIM(@Tamanho),@rownumb,@SORTIMENTO, CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()), 'ADM', CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()),'ADM') FETCH NEXT FROM cur1 INTO @ref, @Tamanho, @sgtstamp, @SORTIMENTO, @rownumb ENDCLOSE cur1DEALLOCATE cur1 This Cursor is Ok and make what i pretendingBest regardsLuis SantosWed, 10 Oct 2012 09:28:49 GMTluissantoshttp://www.sqlservercentral.com/Forums/Topic1370624-392-1.aspxsorry.... Portuguese... Somente um parenteses Luis... --Here i need an IF condition to testSELECT tam FROM sgt WHERE tam = 'Y' AND ref = @ref -- I need to check if already exist Y on field TAM ofPara colocar em uma variável SELECT @TAM=SUA_COLUNA FROM ...... ter a certeza que retornará somente uma linha... aí depois vc pode utilizar if @tam=..... Um abraço, Carlos Suggestion by Chris perfect - No Cursor... Wed, 10 Oct 2012 06:35:12 GMTcarlosaamaralhttp://www.sqlservercentral.com/Forums/Topic1370624-392-1.aspx[quote]...The purpose for using a Cursor is because i need to run an Update not for one "referencia" = Article, but for more and less 170000...[/quote]That your line is the reason for "not using Cursor"!Wed, 10 Oct 2012 03:41:06 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1370624-392-1.aspxHello ChrisThanks for your reply, for me is more readable to understand why the others suggestions works.Best regards,Luis SantosWed, 10 Oct 2012 03:30:34 GMTluissantoshttp://www.sqlservercentral.com/Forums/Topic1370624-392-1.aspx[quote][b]luissantos (10/10/2012)[/b][hr]Hello MickyThe purpose for using a Cursor is because i need to run an Update not for one "referencia" = Article, but for more and less 170000, and for each one the number of "tam" could be different like 34,35,36,37,38,39 or for another one ; 32,33,34,35 and so on....Note : for testing my cursor the WHERE condition have only one referencia.I think it is possible to alter my cursor to make an IF condition, ok i not doing this right, but if you can explain how to do this i will be grateful.I hope you can understand what i pretend.Many thanks for your reply,Luis Santos[/quote]Looks to me like Eugene and Micky are correct - there's no need to cripple this process by using a cursor. Have a look through the following code, I think the logic should be very clear. If it doesn't meet your requirement then I think the spec needs a few more details.[code="sql"]SELECT referencia = RTRIM(referencia), TAMANHO = RTRIM(TAMANHO), sgtstamp = RTRIM(LEFT(NEWID(),23)), SORTIMENTO = min(QTD), Rownumb = ROW_NUMBER() OVER(PARTITION BY referencia ORDER BY referencia,tamanho ASC) INTO #arttamcorFROM arttamcor WHERE referencia = '30110' --IN('29913','30110')GROUP BY referencia, TAMANHO-- insert tam = 'Y' rows into SGT where they don't already existINSERT INTO SGT ( sgtstamp, ref, Tam, pos, dimcol, ousrdata, ousrhora, ousrinis, usrdata, usrhora, usrinis)SELECT sgtstamp, referencia, 'Y', rownumb, SORTIMENTO, CAST(GETDATE() AS DATE), CONVERT (time, GETDATE()), 'ADM', CAST(GETDATE() AS DATE), CONVERT (time, GETDATE()), 'ADM'FROM #arttamcor aWHERE NOT EXISTS (SELECT 1 FROM SGT s WHERE s.ref = a.ref AND s.tam = 'Y')-- insert all of the rowsINSERT INTO SGT( sgtstamp, ref, Tam, pos, dimcol, ousrdata, ousrhora, ousrinis, usrdata, usrhora, usrinis)SELECT sgtstamp, referencia, Tamanho, rownumb, SORTIMENTO, CAST(GETDATE() AS DATE), CONVERT (time, GETDATE()), 'ADM', CAST(GETDATE() AS DATE), CONVERT (time, GETDATE()), 'ADM'FROM #arttamcor a[/code]Wed, 10 Oct 2012 02:56:37 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1370624-392-1.aspxHello MickyThe purpose for using a Cursor is because i need to run an Update not for one "referencia" = Article, but for more and less 170000, and for each one the number of "tam" could be different like 34,35,36,37,38,39 or for another one ; 32,33,34,35 and so on....Note : for testing my cursor the WHERE condition have only one referencia.I think it is possible to alter my cursor to make an IF condition, ok i not doing this right, but if you can explain how to do this i will be grateful.I hope you can understand what i pretend.Many thanks for your reply,Luis SantosWed, 10 Oct 2012 02:25:09 GMTluissantoshttp://www.sqlservercentral.com/Forums/Topic1370624-392-1.aspxHi LuisAs Eugene said, this could be replaced without using a cursor with something like the following.This example may not be the best way to do it, but it has the elements from you procedure sort of replicated to make it a bit easier to follow. Also as I have nothing to test this against I can't guarantee that it will even work :ermm: (may have some typos or logic errors)[code="sql"];with -- Using the query for the cursor cursorReplacement AS ( SELECT referencia, TAMANHO, LEFT(NEWID(),23) as sgtstamp, min(QTD) AS SORTIMENTO, ROW_NUMBER() OVER(PARTITION BY referencia ORDER BY referencia,tamanho ASC) AS Rownumb FROM arttamcor WHERE referencia = '30110'--IN('29913','30110') GROUP BY referencia, TAMANHO ), -- select the tam for each row in the cursor replacement. Only used for the NOT EQUAL query tam as ( select isnull(tam,'#') tam from sgt s left outer join cursorReplacement c ON s.ref = c.referencia where tam = 'Y' ), -- return a row for insert from cursor replacement with tam set to 'Y' tamanhoNEtam AS ( select sgtstamp, referencia as ref, 'Y' as tam, rownumb as pos, sortimento as dimcol, CONVERT(date,GETDATE()) as ousrdata,CONVERT(time, GETDATE()) as ousrhora,'ADM' as ousrinis, CONVERT(date,GETDATE()) as usrdata,CONVERT(time,GETDATE()) as usrhora,'ADM' as usrinis from cursorReplacement c where not exists ( select 1 from tam t WHERE t.tam = c.tamanho ) ), -- return a row for the insert from the cursor replacement (covers the second insert for the IF NOT EQUAL as well as the IF EQUAL insert) tamanhotam AS ( select sgtstamp, referencia as ref, tamanho as tam, rownumb as pos, sortimento as dimcol, CONVERT(date,GETDATE()) as ousrdata,CONVERT(time, GETDATE()) as ousrhora,'ADM' as ousrinis, CONVERT(date,GETDATE()) as usrdata,CONVERT(time,GETDATE()) as usrhora,'ADM' as usrinis from cursorReplacement c )-- INSERT INTO SGT(sgtstamp, ref, Tam,pos,dimcol, ousrdata, ousrhora, ousrinis, usrdata, usrhora, usrinis)SELECT *FROM tamanhoNEtamUNION ALLSELECT *FROM tamanhotam[/code]Tue, 09 Oct 2012 20:38:29 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1370624-392-1.aspxSorry mate, it's 20 past midnight in my place. You will need to wait for someone in US to pick up this thread. I can give you some ideas how to proceed. 1. Please specify more requirements details 2. If you follow the link at the bottom of my signature, you will find tips about how to make your post helpful to helpers ) 3. You need to clarify the rule you apply for checking what exists and what doesn't. What about if two rows already exits in STG? etc.Good Night (to me :-D).Tue, 09 Oct 2012 17:22:51 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1370624-392-1.aspxHello eugenecould you give me the sample on how to build the code ?Many thanksLuis SantosTue, 09 Oct 2012 17:15:23 GMTluissantoshttp://www.sqlservercentral.com/Forums/Topic1370624-392-1.aspxFirst of all, your current code in your cursor has some bugs: -you never set @TAM variable, -if no records found in SGT table, @TAM will be NULL and your IF with "<>" is not going to work-END for the BEGIN for the first IF is out of place. It should not be after FETCH NEXT, but before the second IF The second, and most important: you don't need a cursor at all to do what you're doing.Tue, 09 Oct 2012 17:12:15 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1370624-392-1.aspxHelloI have build this cursor that work correctly, but i need to changing then to out an IF condition if @tamanho exist.my cursor code modified with IF Condition is below:DECLARE cur1 CURSOR LOCAL FORWARD_ONLY FOR SELECT referencia, TAMANHO, LEFT(NEWID(),23) as 'sgtstamp', min(QTD) AS 'SORTIMENTO', ROW_NUMBER() OVER(PARTITION BY referencia ORDER BY referencia,tamanho ASC) AS 'Rownumb' FROM arttamcor WHERE referencia = '30110'--IN('29913','30110') GROUP BY referencia, TAMANHO -- Cursor Variables --DECLARE @ref VARCHAR(18)DECLARE @TAMANHO VARCHAR(25)DECLARE @sgtstamp VARCHAR(25)DECLARE @SORTIMENTO INTDECLARE @rownumb INTDECLARE @TAM int -- Cursor Variables (END) --OPEN cur1FETCH NEXT FROM cur1 INTO @ref, @Tamanho, @sgtstamp, @SORTIMENTO, @rownumbWHILE @@FETCH_STATUS = 0 BEGIN --Here i need an IF condition to test SELECT tam FROM sgt WHERE tam = 'Y' AND ref = @ref -- I need to check if already exist Y on field TAM of -- my table SGT -- I need to make 2 INSERT Statements IF @tamanho <> @tam BEGIN INSERT INTO SGT(sgtstamp, ref, Tam,pos,dimcol, ousrdata, ousrhora, ousrinis, usrdata, usrhora, usrinis) VALUES (RTRIM(@sgtstamp),RTRIM(@ref), 'Y',@rownumb,@SORTIMENTO, CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()), 'ADM', CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()),'ADM') INSERT INTO SGT(sgtstamp, ref, Tam,pos,dimcol, ousrdata, ousrhora, ousrinis, usrdata, usrhora, usrinis) VALUES (RTRIM(@sgtstamp),RTRIM(@ref), RTRIM(@Tamanho),@rownumb,@SORTIMENTO, CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()), 'ADM', CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()),'ADM') -- If exist, i make only one INSERT STATEMENT IF @tamanho = @tam BEGIN INSERT INTO SGT(sgtstamp, ref, Tam,pos,dimcol, ousrdata, ousrhora, ousrinis, usrdata, usrhora, usrinis) VALUES (RTRIM(@sgtstamp),RTRIM(@ref), RTRIM(@Tamanho),@rownumb,@SORTIMENTO, CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()), 'ADM', CONVERT ( date,GETDATE()),CONVERT (time, GETDATE()),'ADM') END FETCH NEXT FROM cur1 INTO @ref, @Tamanho, @sgtstamp, @SORTIMENTO, @rownumb END ENDCLOSE cur1DEALLOCATE cur1 Is my Cursor Code build this way is Correct ??Many thanksLuis SantosTue, 09 Oct 2012 16:49:58 GMTluissantos