October 9, 2012 at 4:49 pm
Hello
I 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 INT
DECLARE @rownumb INT
DECLARE @tam-2 int
-- Cursor Variables (END) --
OPEN cur1
FETCH NEXT FROM cur1 INTO @ref, @Tamanho, @sgtstamp, @SORTIMENTO, @rownumb
WHILE @@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-2
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-2
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
END
CLOSE cur1
DEALLOCATE cur1
Is my Cursor Code build this way is Correct ??
Many thanks
Luis Santos
October 9, 2012 at 5:12 pm
First of all, your current code in your cursor has some bugs:
-you never set @tam-2 variable,
-if no records found in SGT table, @tam-2 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.
October 9, 2012 at 5:15 pm
Hello eugene
could you give me the sample on how to build the code ?
Many thanks
Luis Santos
October 9, 2012 at 5:22 pm
Sorry 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).
October 9, 2012 at 8:38 pm
Hi Luis
As 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)
;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 tamanhoNEtam
UNION ALL
SELECT *
FROM tamanhotam
October 10, 2012 at 2:25 am
Hello Micky
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, 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
October 10, 2012 at 2:56 am
luissantos (10/10/2012)
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
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.
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 #arttamcor
FROM arttamcor
WHERE referencia = '30110' --IN('29913','30110')
GROUP BY referencia, TAMANHO
-- insert tam = 'Y' rows into SGT where they don't already exist
INSERT 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 a
WHERE NOT EXISTS (SELECT 1 FROM SGT s WHERE s.ref = a.ref AND s.tam = 'Y')
-- insert all of the rows
INSERT 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
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 10, 2012 at 3:30 am
Hello Chris
Thanks for your reply, for me is more readable to understand why the others suggestions works.
Best regards,
Luis Santos
October 10, 2012 at 3:41 am
...
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...
That your line is the reason for "not using Cursor"!
October 10, 2012 at 6:35 am
sorry.... Portuguese...
Somente um parenteses Luis...
--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
Para colocar em uma variável
SELECT @tam-2=SUA_COLUNA FROM ...... ter a certeza que retornará somente uma linha...
aí depois vc pode utilizar if @tam-2=.....
Um abraço,
Carlos
Suggestion by Chris perfect - No Cursor...
October 10, 2012 at 9:28 am
Hello Carlos
Your 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 INT
DECLARE @rownumb INT
DECLARE @tam-2 VARCHAR(25)
--New variable to know if @ref changed
DECLARE @refactual VARCHAR(18)
-- Cursor Variables (END) --
OPEN cur1
FETCH NEXT FROM cur1 INTO @ref, @Tamanho, @sgtstamp, @SORTIMENTO, @rownumb
SET @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
END
CLOSE cur1
DEALLOCATE cur1
This Cursor is Ok and make what i pretending
Best regards
Luis Santos
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply