June 11, 2012 at 12:41 pm
Hi,
I'm not getting the same results of placeing in the table the prior value, when doing a VARCHAR... But when I change it to an INT, it works great...
Can anyone tell me or show me what I am not understanding???
Thank you,
JohnCREATE TABLE #TEMPJAS
(
VAL VARCHAR(1)
, P_VAL VARCHAR(1)
)
CREATE CLUSTERED INDEX
[IDX_TEMP]
ON
#TEMPJAS
([VAL])
INSERT INTO #TEMPJAS
(VAL)
VALUES ('A')
INSERT INTO #TEMPJAS
(VAL)
VALUES ('B')
INSERT INTO #TEMPJAS
(VAL)
VALUES ('C')
INSERT INTO #TEMPJAS
(VAL)
VALUES ('D')
INSERT INTO #TEMPJAS
(VAL)
VALUES ('E')
INSERT INTO #TEMPJAS
(VAL)
VALUES ('F')
INSERT INTO #TEMPJAS
(VAL)
VALUES ('G')
INSERT INTO #TEMPJAS
(VAL)
VALUES ('H')
DECLARE @P_VALAS VARCHAR(1)
DECLARE @C_VALAS VARCHAR(1)
UPDATE
J
SET
@P_VAL= J.P_VAL=@C_VAL
, @C_VAL= J.VAL
FROM
#TEMPJAS J WITH (INDEX ([IDX_TEMP]))
SELECT * FROM #TEMPJAS
CREATE TABLE #TEMPJAS
(
VAL INT
, P_VAL INT
)
CREATE CLUSTERED INDEX
[IDX_TEMP2]
ON
#TEMPJAS
([VAL])
INSERT INTO #TEMPJAS
(VAL)
VALUES (1)
INSERT INTO #TEMPJAS
(VAL)
VALUES (2)
INSERT INTO #TEMPJAS
(VAL)
VALUES (3)
INSERT INTO #TEMPJAS
(VAL)
VALUES (4)
INSERT INTO #TEMPJAS
(VAL)
VALUES (5)
INSERT INTO #TEMPJAS
(VAL)
VALUES (7)
INSERT INTO #TEMPJAS
(VAL)
VALUES (8)
INSERT INTO #TEMPJAS
(VAL)
VALUES (9)
DECLARE @P_VALAS INT
DECLARE @C_VALAS INT
UPDATE
J
SET
@P_VAL= J.P_VAL=@C_VAL
, @C_VAL= J.VAL
FROM
#TEMPJAS J WITH (INDEX ([IDX_TEMP2]))
SELECT * FROM #TEMPJAS
June 11, 2012 at 1:06 pm
I don't quite get what you are trying to do with your updates. They are quite strange. I agree the behavior is a bit odd but so is the logic. You can get your updates to work if you remove the assignment to the variable from both of them.
UPDATE J
SET J.P_VAL = @C_VAL
,@C_VAL = J.VAL
FROM #TEMPJAS J WITH (INDEX ([IDX_TEMP2]))
SELECT *
FROM #TEMPJAS
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2012 at 1:25 pm
Hi,
Appreciate the reply...
It's for use in a calculation...
but I don't wanna focus on that...
I want the P_VAL to show me what the Prior value was...
thus...
row 1 VAL = A P_VAL = null
row 2 VAL = B P_VAL = A
row 2 VAL = C P_VAL = B
row 2 VAL = D P_VAL = C
ETC...
It is not doing that with VARCHAR, it does however with an INT????
The code you posted, does apples to apples... (That would work with INT, not VARCHAR????)
row 1 VAL = A P_VAL = A
row 2 VAL = B P_VAL = B
row 2 VAL = C P_VAL = C
row 2 VAL = D P_VAL = D
June 11, 2012 at 1:28 pm
I was kind of wondering if you were trying to do a "previous" row type of thing. Of course what is previous? You have no order by. If you can explain a bit more clearly what you are trying to do I can certainly help you figure it out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2012 at 1:40 pm
I'm really not sure how to explain any simplier than this...
row 1 VAL = A P_VAL = null
row 2 VAL = B P_VAL = A
row 2 VAL = C P_VAL = B
row 2 VAL = D P_VAL = C
That is what I need my end result to look like...
Did you run my code and see the difference between doing an INT and a VARCHAR???
I don't have an order by because I use the WITH (INDEX()), thus, that is my order by...
Thanks,
John
June 11, 2012 at 2:23 pm
Hi yup your thread is interesting, assuming that in your table all the varchar values are in order as per your DDL then you could just inner join the table to itself on the ASCII value of you varchar character + 1 this will then return the results that you have requested.
update A set a.P_VAL=b.val
FROM #TEMPJAS A inner join #TEMPJAS b on ASCII(a.val)=ascii(b.val)+1
select * from #TEMPJAS
***The first step is always the hardest *******
June 11, 2012 at 2:39 pm
I don't have an order by because I use the WITH (INDEX()), thus, that is my order by...
There is 1 and only 1 way to ensure the order of data returned in sql server, it is the order by clause.
What you should do is a self join. Just join the table to itself on your column = column + 1 for int. Or ascii(col) = ascii(col) + 1 for varchar.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2012 at 2:54 pm
Hi,
Why does the previos value work on the numbers and not the VARCHAR???
I'm using the UPDATE to tell me my Previos val (which works with the INT), not joining... (also, that would involve an extra step for me)...
I tried the ORDER BY... Doesn't like it...
But for arguments sake, who cares about the order, if B were first and E was 2nd, then I should see
Row 1 B, NULL
ROW 2 E, B
ROW 3 C, E
Thank you all,
John
June 11, 2012 at 2:59 pm
OK so we ignore the order by and assume a random order. The way you coded it is making what should be a set based update into an RBAR update. The join presented above in code and my half-witted pseudocode will perform far better because they are set based. They are also a lot easier to understand what the code is doing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2012 at 3:00 pm
Did you run my code and see the difference between doing an INT and a VARCHAR???
I did and found it rather odd what it was doing. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2012 at 3:03 pm
LOL... yes it is interesting... lol
Not sure I follow how it's RBAR, and not SET BASED??
Thanks,
John
June 11, 2012 at 3:06 pm
jsteinbeck-618119 (6/11/2012)
LOL... yes it is interesting... lolNot sure I follow how it's RBAR, and not SET BASED??
Thanks,
John
Well I didn't actually look at an execution plan so maybe I am not correct. 😉 It seems to me it would treat this like a recursive cte. It is however incredibly confusing, to me at least.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply