rows deletion affected by cursor

Hello,

I am using a cursor to navigate on data...of a table...
inside the while @.@.fetch_status = 0 command
I want to delete some rows from the table(temporary table)
in order to not be processed...
The problem is that I want this deletion to affect the rows the cursor has.

I declared a dynamic cursor but it does not work.

Does anyone know how I can do this??

Thanks :)Perhaps the fetch into @.var is already executed, changes to the resultset do not affect values in variables? If not, could you post your code?|||The deleted is executed before the fetch next statement.

My code is:

Set @.items = Cursor
For
Select T.patentrynr,t.patcode,t.patname,cast(t.groupid As Nvarchar(10)),g.groupname,
(select Sum(idaxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid )as Idaxia,
(select Sum(tamaxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid) As Tamaxia,
(select Sum(insuraxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid) As Insuraxia,
Itemnum,indvrate,indvamount,tamrate,
Tamamount,insurrate,insuramount,maxqty,tamname,gro upnum,t.groupid
From @.trans T Inner Join @.rates R
On R.groupid = T.groupid And R.groupid Is Not Null
And T.itemid!=cast(t.groupid As Nvarchar(10))
Inner Join Groups G On G.groupid = T.groupid
Order By T.patentrynr,t.groupid

Open @.items

Fetch From @.items Into
@.patentrynr,@.patcode,@.patname,@.itemid,@.itemname,@.i daxia,@.tamaxia,@.insuraxia,@.itemnum,
@.indvrate,@.indvamount,@.tamrate,
@.tamamount,@.insurrate,@.insuramount,@.maxqty,@.tamnam e,@.groupnum,@.groupid

While @.@.fetch_status = 0
Begin
If @.indvamount Is Not Null And @.groupnum Is Not Null And
@.idaxia Is Not Null And @.idaxia!=0
Begin
If @.groupnum > @.maxqty And @.maxqty Is Not Null
Begin
Set @.indvposo = @.maxqty*@.indvamount
End
Else
Begin
Set @.indvposo = @.groupnum*@.indvamount
End
End
Else If @.indvrate Is Not Null And @.idaxia Is Not Null
Begin
Set @.indvposo = @.idaxia*(@.indvrate/100)
End


Insert Into @.result (patentrynr,patcode,patname,tamname,itemid,itemnam e,indvtziros,
Tamtziros,insurtziros,indvpososto,tampososto,insur pososto,parakrat)
Values(@.patentrynr,@.patcode,@.patname,@.tamname,@.ite mid,@.itemname,@.idaxia,@.tamaxia,@.insuraxia,
@.indvposo,@.tamposo,@.insurposo,@.parakrat)


Set @.idaxia=null
Set @.tamaxia =null
Set @.insuraxia=null
Set @.itemnum=null
Set @.indvrate=null
Set @.indvamount=null
Set @.tamrate=null
Set @.tamamount=null
Set @.insurrate=null
Set @.insuramount=null
Set @.maxqty=null
Set @.indvposo=null
Set @.tamposo=null
Set @.insurposo=null

Delete From @.trans Where Patentrynr = @.patentrynr
And Groupid = @.groupid


Fetch Next From @.items
Into @.patentrynr,@.patcode,@.patname,@.itemid,@.itemname,
@.idaxia,@.tamaxia,@.insuraxia,@.itemnum,
@.indvrate,@.indvamount,@.tamrate,@.tamamount,
@.insurrate,@.insuramount,@.maxqty,@.tamname,@.groupnum ,@.groupid
End --end While|||I don't see a reason why rows would not be deleted from the variable table. Are you saying no rows at all are deleted from the table? What are the values of @.groupid and @.patentrynr prior the delete (what rows match)?

I've setup an example that basically does the same, perhaps it gives you an idea.

use monkey
go

set nocount on

declare @.varTab table ( myInt integer, myValue varchar(3))

insert into @.varTab (myInt, myValue) values (1, 'aaa')
insert into @.varTab (myInt, myValue) values (2, 'aaa')
insert into @.varTab (myInt, myValue) values (3, 'aaa')
insert into @.varTab (myInt, myValue) values (4, 'aaa')

declare @.myint integer
declare @.myvalue varchar(3)
declare cur_tab1 cursor DYNAMIC
for select myInt, myvalue from @.varTab

declare @.mtef cursor
set @.mtef = cur_tab1 -- ?

open @.mtef
fetch next from @.mtef into @.myint, @.myvalue
while @.@.fetch_status = 0
begin
-- update tab1 set myValue = 'bbb' where myInt = @.myInt + 1
delete from @.varTab where myInt = @.myInt
select 'myInt: ', @.myint, @.myvalue

fetch next from @.mtef into @.myint, @.myvalue
end

select * from @.varTab

deallocate @.mtef
go|||I have executed your example an it works...fine
but when i added a select statement in my code
before the fetch next statement to find what the table hoes
I found that the values are deleted...
I have also added a select statement after the fetch next...
to find out the values that will be next processed
and they are the next values found in the table before the deletion...

Do I have to change anything in the cursor declaration?|||I'm not sure what you're saying. So the rows are deleted from @.trans? What do you mean with "Do I have to change anything in the cursor declaration?" (assuming the delete works)?|||The delete works but...
I want the fetch next to fetch the next row in the table after the deletion.
This does not work.
It cursor fetches the next row in the table as it was before the deletion.
It seems that the data in the cursor is static...and it is not affected by the deletion.|||so it's like...

...
for select myInt, myvalue
from @.varTab
order by myInt -- order by clause
...
delete from @.varTab where myInt = @.myInt + 1 -- First run: delete myInt = 2
...
deallocate @.mtef
deallocate cur_tab1

The output includes all four rows...

Does your cursor declaration include an 'ORDER BY'-clause?
If it does, your cursor is converted into a KEYSET-cursor (see BOL on this).
What I know from keyset cursors is from BOL, so I'm a bit guessing here but I think the deletes are not visible because it's not the cursor doing the deletes.

rows column in sysindexes table got overflow

Hi, does anyone have the overflow issue with the column, rows in the
sysindexes table? We have table with over 3 billions records and it throws
error 8115 overflow error when I double click the table, which should return
row counts in the table. There is no issue with all data manipulation on thi
s
table even with count_big. When I checked the sysindexes table for this
table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
always max number of integer (even after new insert).
--
hm100This is because the procedure used by EM to return this information
(sp_MStablespace) is trying to force a bigint into an int variable using the
following code
SELECT @.rows = convert(int, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2 and id = @.id
This is no longer used by management studio in SQL2005
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should
> return
> row counts in the table. There is no issue with all data manipulation on
> this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100|||"double-click the table"... Sounds like some bug in the tool you are using?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should retu
rn
> row counts in the table. There is no issue with all data manipulation on t
his
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100

rows column in sysindexes table got overflow

Hi, does anyone have the overflow issue with the column, rows in the
sysindexes table? We have table with over 3 billions records and it throws
error 8115 overflow error when I double click the table, which should return
row counts in the table. There is no issue with all data manipulation on this
table even with count_big. When I checked the sysindexes table for this
table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
always max number of integer (even after new insert).
--
hm100This is because the procedure used by EM to return this information
(sp_MStablespace) is trying to force a bigint into an int variable using the
following code
SELECT @.rows = convert(int, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2 and id = @.id
This is no longer used by management studio in SQL2005
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should
> return
> row counts in the table. There is no issue with all data manipulation on
> this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100|||"double-click the table"... Sounds like some bug in the tool you are using?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should return
> row counts in the table. There is no issue with all data manipulation on this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100sql

rows become columns help!

I am building a calendar table for the most reason four weeks activitis and I have had a temp table data in table A (See my attached file) and I want to
make it as the format in table B as final. How to convert it? Please help!
Thank you!
Suincut this and paste

This should get you on your way.

More formating needed

select
case when dow = 1 then vactype else null end MON,
case when dow = 2 then vactype else null end TUES,
case when dow = 3 then vactype else null end WED,
case when dow = 4 then vactype else null end THUR,
case when dow = 5 then vactype else null end FRI,
case when dow = 6 then vactype else null end SAT,
case when dow = 7 then vactype else null end SUN,
nameitem,
weeknum
from(
select 'VAC DAY' AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/1/2005' AS FULLWORK,
datepart(dw,'1/1/2005') AS DOW,
1 as weeknum
union
select 'VAC DAY' AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/2/2005' AS FULLWORK,
datepart(dw,'1/2/2005') AS DOW,
2 as weeknum
union
select 'VAC DAY' AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/3/2005' AS FULLWORK,
datepart(dw,'1/3/2005') AS DOW,
2 as weeknum
union
select null AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/4/2005' AS FULLWORK,
datepart(dw,'1/4/2005') AS DOW,
2 as weeknum
union
select null AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/5/2005' AS FULLWORK,
datepart(dw,'1/5/2005') AS DOW,
2 as weeknum) a|||thanks thanks!

Rows as Colums

I know this is possible in DB2 and Oracle, but what about for SQL-server 2005

1) select X number of rows from table1

2) I need colums for each row of table1 in a new table

3) As such, Select (select * from X where x.id = @.ID), a,b,c from table Y where y.Id = @.ID

And I dont want to use IfExists.

Thanks

DK

I don't think it is possible the way your are describing.

You could try the pivot method described in this article:

http://dotnet.sys-con.com/read/45543.htm

rows as columns

is it possible to write a query so that we can have all rows of one column in a single column
TIA
Yes. You could concatenate all the rows in the query as:
SELECT
column1 + column2 + column3
FROM
yourtable
One thing to note here is, since the columns would have different datatypes if you try to concatenate varchar column with int column SQL Server might throw an error. So it is adviced to use CONVERT function to convert all the values into varchar, something like:
SELECT
( CONVERT(varchar(5),intcolumn1) + CONVERT(varchar(10),decimalcolumn2) + regularvarcharcolumn3 )
FROM
yourtable

Rows as Columns

This summary is not available. Please click here to view the post.

Rows and Columns

I have a query that reports all of the data I want. However it creates about 40 columns and 10 rows. I would like to swap the display so that it shows 10 columns and 40 rows, which would cause it to print out more readable. Can it be done?Are you using SQL Server 2005 or SQL Server 2000?|||

Hi,

in SQL Server 200 oyu will have to use appropiate CASE expressions, in SQL Server 2005 you can use the new PIVOT functionality-

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am using SQL 2000 which someone else said needed a CASE statement. What I have seen seems to want to add things. What I have is a list of numbers and then columns that are the results of my Query.

I have columns named Store , Name, Net, Tax, Deposits (and a bunch of others)

I have rows with the store numbers 2, 3, 4, 5 ,6 that I sort by

I would like the columns as rows and the store numbers across the top

Thanks

sql

Rows Affected By Delete

Hello all,
Is there someway to tell how many rows were affected by a delete statement? A variable perhaps?

Any help would be appreciated!
Brian@.@.ROWCOUNT stores the number of rows affected by the last statement. It is continually changing, so you may need to store the value in another variable immediately after your statement is executed.

rows

how do i find number of rows ?

i search almost all through google but i did not find any answer to my question

1) number of rows per page

2) total number of rows

?

thanks a lot in advance

nobody |||

Number of rows in a dataset: you could use for instance the Count() aggregate function. See MSDN for more details: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_1l6b.asp

Number of rows on a page - you can use aggregate functions in the page header or footer that will count how often a certain textbox (e.g. located in list/table details) occurs on the current page. E.g. =Count(ReportItems!textbox1.Value)
See also: http://msdn2.microsoft.com/en-us/library/ms159677.aspx

-- Robert

rownumbering w tsql lost to ADO loop -

Hello
Thanks to all who have been helping me to fight a battel
with tsql for rownumbering of my tables. I humbly admit
defeat for today. I had to throw the towel in and whipped
up an ADO loop to number some rows in a table. That took
me all of 3 minutes, if that. I wrestled with tsql all
day long. Well, here is one more shot at tsql.
create table t (rownum int, area char(1), yearnum int,
monthnum int, areaNum int, productNum int)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 3)
this yields
rownum area yearnum monum areanum prodnum
NULL A 2003 1 1 1
NULL A 2003 1 1 2
NULL A 2003 1 1 3
NULL B 2003 1 2 1
NULL B 2003 1 2 2
NULL B 2003 1 2 3
NULL C 2003 1 3 1
NULL C 2003 1 3 2
NULL C 2003 1 3 3
NULL A 2003 2 1 1
NULL A 2003 2 1 2
NULL A 2003 2 1 3
NULL B 2003 2 2 1
NULL B 2003 2 2 2
NULL B 2003 2 2 3
NULL C 2003 2 3 1
NULL C 2003 2 3 2
NULL C 2003 2 3 3
I need to number these rows in the order of
yearNum, MonthNum, areaNum, productNum
select * from t order by yearnum, monthnum, areanum,
productnum
how can I number the rownum column starting at 1 using
tsql?
Thanks in advance,
RonDo:
UPDATE t
SET rownum = ( SELECT COUNT(*)
FROM t t1
WHERE CAST( t1.yearnum AS VARCHAR) +
RIGHT( '0' + CAST( t1.monthnum AS VARCHAR), 2 ) +
CAST( t1.areaNum AS VARCHAR) +
CAST( t1.productNum AS VARCHAR) <=
CAST( t.yearnum AS VARCHAR) +
RIGHT( '0' + CAST( t.monthnum AS VARCHAR), 2 ) +
CAST( t.areaNum AS VARCHAR) +
CAST( t.productNum AS VARCHAR) )
WHERE rownum IS NULL ;
Btw, do you really need a reason to have such a column in the table.
Generally, it is a good idea to do such derivations while extracting the
data from the table or to use a view with the ranking expression.
Anith|||thanks for your reply. This worked perfectly.
And to answer your question if I really need this
column? Truthfully, no. I don't really need it. Well,
for my purposes, this column facilitates reporting and ad-
hoc queries for data analysts in my dept at the
workplace. My real problem was that I did not know how
to achieve this with t-sql. I was aware of using the
self join, but I did not know that you had to cast int
values as varchars to accomplish this numerating.
I believe that my exercise for today will be very
valuable in the future.
Thanks for your help,
Ron

>--Original Message--
>Do:
>UPDATE t
> SET rownum = ( SELECT COUNT(*)
> FROM t t1
> WHERE CAST( t1.yearnum AS VARCHAR) +
> RIGHT( '0' + CAST( t1.monthnum
AS VARCHAR), 2 ) +
> CAST( t1.areaNum AS VARCHAR) +
> CAST( t1.productNum AS VARCHAR)
<=
> CAST( t.yearnum AS VARCHAR) +
> RIGHT( '0' + CAST( t.monthnum
AS VARCHAR), 2 ) +
> CAST( t.areaNum AS VARCHAR) +
> CAST( t.productNum AS VARCHAR) )
> WHERE rownum IS NULL ;
>Btw, do you really need a reason to have such a column
in the table.
>Generally, it is a good idea to do such derivations
while extracting the
>data from the table or to use a view with the ranking
expression.
>--
>Anith
>
>.
>|||One more question if I may. I hope this isn't too
ignorant sounding, but...
I noticed a semi colon at the end of your code. Does
Enterprise Manager have a wizard that can generate the t-
sql for something like this? Man, I sure hope so. It
would sure save me a lot of heartache.

>AS VARCHAR), 2 ) +
VARCHAR)
><=
>AS VARCHAR), 2 ) +
VARCHAR) )
>in the table.
>while extracting the
>expression.
>.
>|||>> Does Enterprise Manager have a wizard that can generate the t-sql for
No, they are optional. You need not worry about it.
Btw, for the problem you posted, instead of concatenating the columns you
could do a series of OR-ed correlations like:
WHERE t1.yearnum <= t.yearnum
OR ( t1.yearnum = t.yearnum
AND t1.t1.monthnum <= t.t1.monthnum )
OR ( t1.yearnum = t.yearnum
AND t1.monthnum = t.t1.monthnum
AND t1.areaNum <= t.areaNum )
OR ( t1.yearnum = t.yearnum
AND t1.monthnum = t.t1.monthnum
AND t1.areaNum = t.areaNum
AND t1.productNum <= t.productNum )
Anith|||Thanks again for this explanation. I guess I will just
have to keep experimenting. This other method did work.

>--Original Message--
generate the t-sql for
>No, they are optional. You need not worry about it.
>Btw, for the problem you posted, instead of
concatenating the columns you
>could do a series of OR-ed correlations like:
>WHERE t1.yearnum <= t.yearnum
> OR ( t1.yearnum = t.yearnum
> AND t1.t1.monthnum <= t.t1.monthnum )
> OR ( t1.yearnum = t.yearnum
> AND t1.monthnum = t.t1.monthnum
> AND t1.areaNum <= t.areaNum )
> OR ( t1.yearnum = t.yearnum
> AND t1.monthnum = t.t1.monthnum
> AND t1.areaNum = t.areaNum
> AND t1.productNum <= t.productNum )
>--
>Anith
>
>.
>

RowNumber?

I want to add a record number to each record in my report.
I don't have any grouping on my report, so not sure if using
the rownumber() function works in that case'
C.The following should work:
=RowNumber(Nothing)sql

Rownumber()

I am trying to write a stored procedure to be used for custompaging and I get error with the below SP.
"Msg 207, Level 16, State 1, Procedure GetDealersPagedSP, Line 14 Invalid column name 'RowRank'."

What am I doing wrong?

CREATEPROCEDURE dbo.GetDealerSP

(
@.startRowIndexint,
@.maximumRowsint
)
As
SELECT installersemaid,dealerid,[name],address1,address2,city,[state],
zip,phone,fax
From
(
SELECT installersemaid,dealerid,[name],address1,address2,city,[state],
zip,phone,fax,ROW_NUMBER()OVER(ORDERBY [name]DESC)AS Rowbank
FROM dealerenrollment)as DealerWithRowNumbers
WHERE Rowbank> @.startRowIndexAND RowRank<=(@.startRowIndex+ @.maximumRows)
Go

Hi bhavin78,

bhavin78:

"Msg 207, Level 16, State 1, Procedure GetDealersPagedSP, Line 14 Invalid column name 'RowRank'."

...

CREATEPROCEDURE dbo.GetDealerSP

...

WHERE Rowbank> @.startRowIndexANDRowRank<=(@.startRowIndex+ @.maximumRows)
Go

Looks like it's a typo, that RowRank should be Rowbank according to the rest of your query.

Personally, I'd change the three instances of Rowbank to RowRank, as it's a little closer to describing the columns contents (actually, calling it RowNumber would be my first choice ;) ).

I hope that helps.

|||

--zip,phone,fax, ROW_NUMBER() OVER(ORDER BY [name] DESC)ASRowbank

You have used Rowbank not RowRank check it once

Rownumber with grouping

Hi everyone

I'm having a problem that I hope someone will be able to help me with. I've created a report with a table, and using =Rownumber(nothing) to give each row a number - this works fine.

Now, however, I'm trying to create a summary report. So I've introduced grouping on one of the columns, but unfortunately the Rownumber command now doesn't seem to be taking that into account, and still showing the original row numbers. So basically I'm ending up with numbers like 2, 6, 9, 15, etc.

I've tried changing the scope from nothing to the row name and to the group name, but they all do the same thing.

Does anyone know how to solve this?

Thanks,
Matt

The grouping should make the row numbers add up properly. Are you putting the group name within double quotes, like this?

=RowNumber("MyGroupName")|||

Thanks for your reply Darrell.

Yes I am using double quotes like that. Anything else gives me a syntax error when i try to run it.

Any other thoughts. I can't work out what the problem is here. Would it help if I posted a copy of my report?

|||

Hi again

I apologise if I'm sounding petulant here, but I really am at a loss. I can't find any information anywhere on the web, and I can't work out what else I could be doing wrong. If anyone has any ideas I would really appreciate hearing them.

Thanks,
Matt

|||did you ever solve this? i am having the same challenge. there is a serious lack of documentation on this.|||

Just solved this a few minutes ago. I love it when I answer my own questions.

The key is to use CountDistinct on the value you are grouping on. So use:

=RunningValue(Fields!YourGroupField.Value, CountDistinct, Nothing)

For my whole write-up, visit:

http://maxqtech.com/CS/blogs/david_leibowitz/archive/2006/08/22/3372.aspx

David Leibowitz

Business Intelligence Practice Manager

MaxQ Technologies

|||

Hey David

Thanks for your reply. I never did get it sorted out, so I appreciate your solution. Will remember it for next time :)

Matt

RowNumber using SQL Query

Hi All,
I have following table structure,

----------------------
ChallanID ProductID PublicationDate Description Qty Amt
----------------------
43 9 4/1/2006 ABC 1 880
43 10 5/1/2006 BCA 1 930
43 11 5/1/2006 CBA 1 230

I want a sql query which select all the record with a serial number eg:

---------------------
SN# ChallanID ProductID PublicationDate Description Qty Amt
----------------------
1 43 9 4/1/2006 ABC 1 880
2 43 10 5/1/2006 BCA 1 930
3 43 11 5/1/2006 CBA 1 230use a temp table with identity column and insert your result into the temp table and select it back.|||U havent mentioned about how records to be ordered? In what order u want to generate serial No:?|||temp table shemp table...

SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName

ps. I got this example from somewhere and it is not original work. If the original author sees this and takes any offense I am will to erase from the forum.|||SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName
No, that only works if you use a unique key i.e.
select id=1,name='ccc' into #t1 union all
select 3,'bbb' union all
select 4,'aaa' union all
select 9,'bbb'

select count(*) as [ss#], name=min(a.name)
from #t1 a, #t1 b
where a.id>=b.id
group by a.id
order by 1
else use a temp table with identity column as suggested by khtan
select ss#=identity(int,1,1),name into #t2 from #t1 order by name
select * from #t2|||Problem is, either way you have no guarantee that the "serial number" for any record won't change as the contents of the table changes. Seems to me a "serial number" is expected to be static, so you really should add it as a permanent column to your table (perhaps as an identity datatype).

RowNumber Scope

What is the syntax for getting the inner-most scope for RowNumber ie within
a given group so that it behaves as follows:
Group1
rec1
Group2
rec1.1 expect to see Rowcount=1
rec1.2 expect to see Rowcount=2
rec2
Group2
rec2.1 expect to see Rowcount=1
rec2.2 expect to see Rowcount=2After posting this I realized that RowNumber won't give me the specific
rownumber just a total|||Mike
Rownumber SHOULD give you what you want... just add the name of the group as
the parameter ie
=rownumber(group1)
This gives the running count ( which would be the row number), and resets on
each new Group1 group...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and
it''''s
community of SQL Professionals.
"Mike Harbinger" wrote:
> After posting this I realized that RowNumber won't give me the specific
> rownumber just a total
>
>|||That's what I thought but it does not seem to be resetting even though I am
setting the group scope. The first row in the group is an addtional column
heading that I only want to see once per group instance. I was trying to use
RowNumber to test for row1 so I can toggle the visibiilty of the column
headers. Maybe there is a better way to do this?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:60CED50A-C9CB-4F65-B9A8-ECA1F529A69F@.microsoft.com...
> Mike
> Rownumber SHOULD give you what you want... just add the name of the group
> as
> the parameter ie
> =rownumber(group1)
> This gives the running count ( which would be the row number), and resets
> on
> each new Group1 group...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and
> it''''s
> community of SQL Professionals.
>
> "Mike Harbinger" wrote:
>> After posting this I realized that RowNumber won't give me the specific
>> rownumber just a total
>>

RowNumber reset at start of each grouping

I'm creating a report where I'd like to list out the lines of the report. I
deceided to use RowNumber function but it is keeping a running total when I
use it like this: RowNumber(Nothing)
I used the report wizard to create a report and used the page grouping to
seperate pages by "team". How do I find the scope to tell RowNumber to
startover for each team?
ColinYou can set the scope of RowNumber by replacing Nothing with the name of the
group where you want the reset to occur. If your group name is Team then you
would use RowNumber("Team").
"Colin" wrote:
> I'm creating a report where I'd like to list out the lines of the report. I
> deceided to use RowNumber function but it is keeping a running total when I
> use it like this: RowNumber(Nothing)
> I used the report wizard to create a report and used the page grouping to
> seperate pages by "team". How do I find the scope to tell RowNumber to
> startover for each team?
> Colin
>
>sql

RowNumber Problem in Grouped Data

I have multiple (multilevel) groupings in my RDL (based on line level data
from SQL server). While using the RowNumber("GrouName") I am getting strange
results. For example If that grouping was made out of 3 individual lines from
data set, then I am getting Row number as 3.
What I want is irrespective of the no of lines involved in such aggregation,
I want the rownumbers based on the final aggregated line and not based on the
no of detailed lines involved in such groupings.
Any help will be appreciated!
Thanks,
VisualcppI get this also. With my groupings, RowNumber behaves identically to
CountRows. I have something that looks like:
Group1
Group 2
Details
Group2 and details are initially hidden so I want to alternate row
colors for the group1 header and footer rows. RowNumber("Group1")
returns the number of rows in that group, not the number of the
group. It's identical to CountRows("Group1").
If you figure out the solution to this, please let me know!
On Feb 15, 1:51 am, Visualcpp <Visual...@.discussions.microsoft.com>
wrote:
> I have multiple (multilevel) groupings in my RDL (based on line level data
> from SQL server). While using theRowNumber("GrouName") I am getting strange
> results. For example If that grouping was made out of 3 individual lines from
> data set, then I am getting Row number as 3.
> What I want is irrespective of the no of lines involved in such aggregation,
> I want the rownumbers based on the final aggregated line and not based on the
> no of detailed lines involved in such groupings.
> Any help will be appreciated!
> Thanks,
> Visualcpp|||I don't know if this is the proper way to get around this, but the
following works correctly. If you happened to have blank or null
group headings this method would not work.
=IIF(RunningValue(Fields!name.Value,CountDistinct,nothing) mod 2,
"Transparent", "LightYellow")
On Mar 11, 10:35 am, brandon.rich...@.gmail.com wrote:
> I get this also. With my groupings,RowNumberbehaves identically to
> CountRows. I have something that looks like:
> Group1
> Group 2
> Details
> Group2 and details are initially hidden so I want to alternate row
> colors for the group1 header and footer rows. RowNumber("Group1")
> returns the number of rows in that group, not the number of the
> group. It's identical to CountRows("Group1").
> If you figure out the solution to this, please let me know!
> On Feb 15, 1:51 am, Visualcpp <Visual...@.discussions.microsoft.com>
> wrote:
>
> > I have multiple (multilevel) groupings in my RDL (based on line level data
> > from SQL server). While using theRowNumber("GrouName") I am getting strange
> > results. For example If that grouping was made out of 3 individual lines from
> > data set, then I am getting Row number as 3.
> > What I want is irrespective of the no of lines involved in such aggregation,
> > I want the rownumbers based on the final aggregated line and not based on the
> > no of detailed lines involved in such groupings.
> > Any help will be appreciated!
> > Thanks,
> > Visualcpp- Hide quoted text -
> - Show quoted text -

Rownumber in result of query

Hi there,
Anyone knows how to get the rownumber in the result of the query?
thank you,
Max
Hi
Do you want to get the row of the original table or do you want to add a
numbering sequence to your result?
If it is the 2nd option, you need to add that manually before returning the
result to the client by means of a temp table.
Regards
Mike
"Max" wrote:

> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
>
|||Here is an example.
use pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
|||Max,
See:
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/?id=186133
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Max wrote:
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
|||use Northwind
select
(select count(*) from orders as A where A.orderid <= B.orderid) as
row_num,
orderid, customerid, convert(varchar(10), orderdate, 120) as
orderdate from orders as B order by orderid
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>

Rownumber in result of query

Hi there,
Anyone knows how to get the rownumber in the result of the query?
thank you,
MaxHi
Do you want to get the row of the original table or do you want to add a
numbering sequence to your result?
If it is the 2nd option, you need to add that manually before returning the
result to the client by means of a temp table.
Regards
Mike
"Max" wrote:
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
>|||Here is an example.
use pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>|||Max,
See:
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/?id=186133
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Max wrote:
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>|||use Northwind
select
(select count(*) from orders as A where A.orderid <= B.orderid) as
row_num,
orderid, customerid, convert(varchar(10), orderdate, 120) as
orderdate from orders as B order by orderid
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>