January 25, 2008 at 6:50 am
I need a row counter.
I have a view that displays the following:
Name
Account
Zip
I need a UDF that will incremented number and return it to the view:
Name Account Zip counter
jone 97000 33805 1
smith 85000 33501 2
etc.. 3,4,5,......:cool:
Below is my attempt - wrong!!
alter PROCEDURE sp_GetCounter(@ccnt int) as
declare @counter int
set @counter = @counter+@ccnt
return @ccnt
January 25, 2008 at 7:01 am
Below is my attempt - wrong!!
alter PROCEDURE sp_GetCounter(@ccnt int) as
declare @counter int
set @counter = @counter+@ccnt
return @ccnt
-------------------------------------------
Just a thought, why r u returning @ccnt when ur incrementing @counter in ur Proc.?
January 25, 2008 at 7:01 am
Debra
I think the simplest way to achieve what you want is with an identity column. If this is a new table, add the identity column before you insert any data. If you already have data in your table, create a new table with an identity column, and insert the data from your existing table into it.
John
January 25, 2008 at 7:07 am
John, thanks for the quick response.
I do not need to update a table on display data via a view.
More info:
The view will be used by a IVR.
The user will enter a date the IVR program will query the view
with user name and date.
The view returns, for example, 10 items with a matching date.
The IVR program needs a counter to loop through the rows.
Does that help?
January 25, 2008 at 7:28 am
There isn't an exact solution that I know of. Since a view can't pass parameters to a table-value function, you might have trouble doing this.
If you can use a table-value function instead of a view, here's a possible solution:
create function udf_NameAccountZip ()
returns @NAZ table (
Name varchar(25),
Account int,
Zip char(10),
RowNumber int identity primary key)
as
begin
insert into @naz(name, account, zip)
select name, account, zip
from (... table(s) here...)
where ...
order by ...
return
end
You'll need to modify it to take input parameters.
If that won't work (if you can't change the front-end code from accessing a view to accessing a udf, for example), I can't think of a solution for SQL 2000.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 25, 2008 at 8:17 am
I tried something like this....
cast(rand()*DATEPART(ms,getdate())*300 as int)
.. it creates a number however the numbers are identical.
I need something that creates a unique number. It does not have to be sequencial just unique per row.:cool:
January 25, 2008 at 8:34 am
Debra
Have you tried NEWID()?
John
January 25, 2008 at 8:40 am
Has to be numeric....
January 25, 2008 at 9:18 am
Debra
If you don't have more than one update in any particualr second, you could do something like this:
select datediff(s,'01 Jan 1980',getdate())
If your updates are more frequent than that, you could try multiplying by 1000, adding the milliseconds, and casting as bigint.
Good luck
John
January 25, 2008 at 9:20 am
Got it!!!
1. create a view
CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber
2. create a udf
CREATE FUNCTION RandNumber()
RETURNS float
AS
BEGIN
RETURN 7 + (SELECT RandNumber FROM vRandNumber) * 113.00
END
3. add it to view
SELECT name, date, replace(dbo.RandNumber(),'.','') 'cnt',.....
Got this from http://weblogs.sqlteam.com/jeffs/jeffs/archive/2004/11/22/2927.aspx
January 25, 2008 at 9:34 am
And - there's NO guarantee that it won't return the same number twice... The larger the set of numbers, the more likely it WILL duplicate the number.
Try this instead:
select top 1000 checksum(newID()),* from myTable
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 25, 2008 at 10:46 am
That works also.
I'll use your since!
Thanks!!
January 25, 2008 at 11:03 am
checksum doesn't guarantee unique results. It'll probably be unique, but it's not guaranteed. More likely to be unique than Rand() is, though.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 25, 2008 at 11:21 am
After running several test the top 1000... will not work with the query.
The user name and date is being queried, member quired is not one of the 1000 returned therefore, no items selected.
When I run it with the udf random it works.
January 25, 2008 at 11:38 am
GSquared (1/25/2008)
checksum doesn't guarantee unique results. It'll probably be unique, but it's not guaranteed. More likely to be unique than Rand() is, though.
Per BOL:
CHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator. For the purpose of this definition, NULL values of a given type are considered to compare as equal. If one of the values in the expression list changes, the checksum of the list also usually changes. However, there is a small chance that the checksum will not change.
From the way I read that it, running a checksum against unique values will return unique values. That's what a hash function does, after all: it returns a unique value that can be used to determine equality. If the hash codes are equal, then so are the values. NEWID() does in fact guarantee uniqueness, so I'm relying on that.
From testing, I've generated sets of 6 million unique numbers using that and have yet to run into a conflict.
Am I misreading that somehow? I am making some assumptions, but I thought they were pretty solid.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply