September 19, 2002 at 9:36 am
Using @@ROWCOUNT gives me the total row count returned by a query. Is there a function I can use to get the current row counts so that as I roll through a query and it is returning the nth row, I have access to that number?
September 19, 2002 at 10:20 am
Don't think so. I think it would be a relatively easy task to keep track of the number of records fetched while you are fetch each records via a cursor.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 19, 2002 at 10:47 am
Here is what I need to do. I need to roll through a query, and if a value is null, I need to grab the value that preceded it and replace the null with that value. Essentially, I need to do this - ISNULL(value, [current row -1]). How can this be done?
September 19, 2002 at 10:55 am
When you have a row, save the column values in a local variable. When you get to the next row, if it is null then replace it with the local variable. Just before you get the next row, save the column values.
Does any of this make sense?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 19, 2002 at 11:22 am
Am I using a cursor?
September 19, 2002 at 11:28 am
I don't know. How are you processing through your records (programatically)? Send sample code might help.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 19, 2002 at 11:39 am
All I have at this point is the query. Some of the values return as NULL which need to be replaced with the preceding value. You mentioned saving the columns for each row. Is there a way to do this programtically without a cusor. Here is the query:
SELECT DISTINCT v1.[ID], Wrksta.[Name], CONVERT(VARCHAR, v1.[TimeStamp], 101) + ' ' + CONVERT(VARCHAR, v1.[TimeStamp], 108) AS [Time Stamp],
v1.Value AS [Processor Time], v2.[Value] AS [Server Sessions]
FROM Wrksta
LEFT JOIN AeXSMReportsTemp v1
ON Wrksta.WrkstaId = v1.WrkstaID
AND v1.MetricGUID = '{5e46c35a-390e-483f-895b-733b3b1467cd}'
LEFT JOIN AeXSMReportsTemp v2
ON v1.[TimeStamp] = v2.[TimeStamp]
AND v2.MetricGUID = '{1133ae38-13eb-4d54-bce2-60d79032caaf}'
WHERE Wrksta.[Name] = UPPER('zeus')
v1.Value and v2.Value have the potential to be NULL.
September 19, 2002 at 1:16 pm
Here is an example that takes values from prior row, based on ID. Maybe something like this might work:
create table abc (id int, v1 int, v2 int)
insert into abc values (1,1,1)
insert into abc (id) values (2)
insert into abc (id,v2) values (3,3)
insert into abc (id) values (4)
insert into abc values (5,5,5)
insert into abc (id) values (6)
select * from abc
select id, case
when v1 is null
then (select top 1 v1 from abc where id < a.id and v1 is not null order by id desc)
else v1
end as v1,
case
when v2 is null
then (select top 1 v2 from abc where id < a.id and v2 is not null order by id desc)
else v2
end as v2
from abc a
drop table abc
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 19, 2002 at 11:37 pm
This is another way to do this.....
drop table abc
create table abc (id int, v1 int, v2 int)
insert into abc values (1,1,1)
insert into abc (id) values (2)
insert into abc (id,v2) values (3,3)
insert into abc (id) values (4)
insert into abc values (5,5,5)
insert into abc (id) values (6)
select * from abc
declare @Hellobye int,
@Hellobye2 int
set @Hellobye = 0
set @Hellobye2 = 0
update abc set
@Hellobye = v1 = isnull(v1,@Hellobye),
@Hellobye = v2 = isnull(v2,@Hellobye2)
select * from abc
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
September 24, 2002 at 7:26 am
Prakash, a real guru ... ¿where did you get that man?
Nice solution, congratulations
quote:
This is another way to do this.....drop table abc
create table abc (id int, v1 int, v2 int)
insert into abc values (1,1,1)
insert into abc (id) values (2)
insert into abc (id,v2) values (3,3)
insert into abc (id) values (4)
insert into abc values (5,5,5)
insert into abc (id) values (6)
select * from abc
declare @Hellobye int,
@Hellobye2 int
set @Hellobye = 0
set @Hellobye2 = 0
update abc set
@Hellobye = v1 = isnull(v1,@Hellobye),
@Hellobye = v2 = isnull(v2,@Hellobye2)
select * from abc
Prakash
September 25, 2002 at 4:19 pm
I am entirely confused by the last posting. Can you explain a little better?
September 25, 2002 at 4:43 pm
It looks to me, that Prakash's solution sets the V1 to the prior V1, and V2 to the prior V2, by saving the prior v1 in variable @Hellobye, and prior v2 in variable @Hellobye2. Note the update command below:
update abc set
@Hellobye = v1 = isnull(v1,@Hellobye),
@Hellobye = v2 = isnull(v2,@Hellobye2)
When the first row is update it set v1 either v1, or @hellobye, if v1 is null. Also it sets @Hellobye to the same value that v1 is update to. Basically the update command sets both @Hellobye and V1 at the same time. Pretty cool stuff.
Hope this helps. Also I think there might be small typo in the update clause I think it should read:
update abc set
@Hellobye = v1 = isnull(v1,@Hellobye),
@Hellobye2 = v2 = isnull(v2,@Hellobye2
This way column v2 gets update correctly.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 25, 2002 at 4:48 pm
make that typo:
update abc set
@Hellobye = v1 = isnull(v1,@Hellobye),
@Hellobye2 = v2 = isnull(v2,@Hellobye2)
basically I think you want to set @Hellobye2 (instead of @Hellobye)to v2, or @hellobye2.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 26, 2002 at 11:38 am
Ok, I get it now. Thanks for all the help.
September 26, 2002 at 4:06 pm
I have been playing with this and thought I understood. What i really dont understand is how do all the variables get set with an updated statement. It appears to me that
update abc set @Hellobye = v1 = isnull(v1,@Hellobye)
@Hellobye gets set to the first value in the table and then the second and so on. How does this happen with an update or am i totally missing the boat?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply