I am developing a routine that randomly selects inventory items from a
SQL Server table, for inventory control purposes.
The item code is numeric, ie: 2345, 7867, 127435, etc. However, there
are less than 2000 records in the table
So I can apply the vfp rand function like so
RandomNbr = int( rand(-1) * 1987 )
to select a specific record, say 469, which could have an id number like
8976 and contain an item code like 789678
If I were in VFP I would simply locate for recno() = 469 and get the
item code.
But, what is the equivalent to the recno() function in TSQL, if there is
one? And if there is not, how do I get to the particular record?
Rafael Copquin
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4DE5118C.9010503@ciudad.com.ar
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On Tue, May 31, 2011 at 11:04 AM, Rafael Copquin <rcopquin@ciudad.com.ar> wrote:
> I am developing a routine that randomly selects inventory items from a
> SQL Server table, for inventory control purposes.
>
> The item code is numeric, ie: 2345, 7867, 127435, etc. However, there
> are less than 2000 records in the table
>
> So I can apply the vfp rand function like so
>
> RandomNbr = int( rand(-1) * 1987 )
>
> to select a specific record, say 469, which could have an id number like
> 8976 and contain an item code like 789678
>
> If I were in VFP I would simply locate for recno() = 469 and get the
> item code.
>
> But, what is the equivalent to the recno() function in TSQL, if there is
> one? And if there is not, how do I get to the particular record?
-------------------
How many do you need to get?
A For loop with this command:
SELECT TOP 1 * FROM TheTableIneedToAudit ORDER BY NEWID()
Will give you what you need.
--
Stephen Russell
Unified Health Services
60 Germantown Court
Suite 220
Cordova, TN 38018
Telephone: 888.510.2667
901.246-0159 cell
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/BANLkTi=brW=a1gGndm=678Smm=uefTDXVQ@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
As you have found SQL Server has no concept of record numbers...
What you could do is add an integer column to that SQL Table (assuming you
have rights to do so), and under the settings for that column expand
"Identity Specification" and set (is Identity) to yes. This will set the
column up much the way VFP does with its Auto-incrementing integer.
Then reference that field with your randomly-generated number using the
max() of the new field which You'll have to make a call to SQL to obtain
first. Be sure to make sure you have a record returned when you make the
call, if records are deleted from that table the ID's are not recycled so
you could end up doing a query against a record that has been deleted and
therefore not get a record back.
Lou
-----Original Message-----
From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com] On
Behalf Of Rafael Copquin
Sent: Tuesday, May 31, 2011 9:04 AM
To: profoxtech@leafe.com
Subject: vfp vs tsql
I am developing a routine that randomly selects inventory items from a
SQL Server table, for inventory control purposes.
The item code is numeric, ie: 2345, 7867, 127435, etc. However, there
are less than 2000 records in the table
So I can apply the vfp rand function like so
RandomNbr = int( rand(-1) * 1987 )
to select a specific record, say 469, which could have an id number like
8976 and contain an item code like 789678
If I were in VFP I would simply locate for recno() = 469 and get the
item code.
But, what is the equivalent to the recno() function in TSQL, if there is
one? And if there is not, how do I get to the particular record?
Rafael Copquin
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/0cf701cc1faf$2656eab0$7304c010$@com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
It kind of does:
SELECT Name, ROW_NUMBER() OVER (ORDER BY Name ASC) AS RowNumber
FROM Contacts
Returns:
Hammarberg, jesper 1
(Shanghai) Co.Ltd 2
1Dagskassa 3
733 VA Syd 4
Aastedt, Eva-Kristina 5
Ab Banankompaniet Er 43 6
This is useful where working with 'paged' data. i.e. you are
presenting the customer with 50 rows of data at a time and don't want
to load the entire database into memory...
I used this technique with dot net WinForms DataGridViews in virtual mode.
Paul
On Tue, May 31, 2011 at 5:23 PM, Lou Syracuse <lous@iconmediadirect.com> wrote:
> As you have found SQL Server has no concept of record numbers...
>
> What you could do is add an integer column to that SQL Table (assuming you
> have rights to do so), and under the settings for that column expand
> "Identity Specification" and set (is Identity) to yes. This will set the
> column up much the way VFP does with its Auto-incrementing integer.
>
> Then reference that field with your randomly-generated number using the
> max() of the new field which You'll have to make a call to SQL to obtain
> first. Be sure to make sure you have a record returned when you make the
> call, if records are deleted from that table the ID's are not recycled so
> you could end up doing a query against a record that has been deleted and
> therefore not get a record back.
>
> Lou
>
>
>
> -----Original Message-----
> From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com] On
> Behalf Of Rafael Copquin
> Sent: Tuesday, May 31, 2011 9:04 AM
> To: profoxtech@leafe.com
> Subject: vfp vs tsql
>
> I am developing a routine that randomly selects inventory items from a
> SQL Server table, for inventory control purposes.
>
> The item code is numeric, ie: 2345, 7867, 127435, etc. However, there
> are less than 2000 records in the table
>
> So I can apply the vfp rand function like so
>
> RandomNbr = int( rand(-1) * 1987 )
>
> to select a specific record, say 469, which could have an id number like
> 8976 and contain an item code like 789678
>
> If I were in VFP I would simply locate for recno() = 469 and get the
> item code.
>
> But, what is the equivalent to the recno() function in TSQL, if there is
> one? And if there is not, how do I get to the particular record?
>
> Rafael Copquin
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/BANLkTikpGkn+iSpsZN3406Ttq724NbBbmA@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On 31/05/2011 01:04 p.m., Rafael Copquin wrote:
> I am developing a routine that randomly selects inventory items from a
> SQL Server table, for inventory control purposes.
>
> The item code is numeric, ie: 2345, 7867, 127435, etc. However, there
> are less than 2000 records in the table
>
> So I can apply the vfp rand function like so
>
> RandomNbr = int( rand(-1) * 1987 )
>
> to select a specific record, say 469, which could have an id number like
> 8976 and contain an item code like 789678
>
> If I were in VFP I would simply locate for recno() = 469 and get the
> item code.
>
> But, what is the equivalent to the recno() function in TSQL, if there is
> one? And if there is not, how do I get to the particular record?
>
> Rafael Copquin
Maybe :
select IDENTITY(int, 1, 1) as recno, *
into #Temp
from sometable
select *
from #Temp
where recno = 40073
drop table #Temp
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4DE51967.8000100@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On Tue, May 31, 2011 at 11:23 AM, Lou Syracuse <lous@iconmediadirect.com> wrote:
> As you have found SQL Server has no concept of record numbers...
>
> What you could do is add an integer column to that SQL Table (assuming you
> have rights to do so), and under the settings for that column expand
> "Identity Specification" and set (is Identity) to yes. This will set the
> column up much the way VFP does with its Auto-incrementing integer.
>
> Then reference that field with your randomly-generated number using the
> max() of the new field which You'll have to make a call to SQL to obtain
> first. Be sure to make sure you have a record returned when you make the
> call, if records are deleted from that table the ID's are not recycled so
> you could end up doing a query against a record that has been deleted and
> therefore not get a record back.
>
------------------
Sorry but TSQL has had row numbers for the past 3 years.
USE AdventureWorks2008R2;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
-- Now fetch rows 50 -> 60
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
The over() clause allows you designate how to apply recno to the set.
The problem requested was a Random audit so having a RowNumber is
still bizarre in that this is a disconnected environment and you may
have a table with 25 million rows. The way you index your table has
everything to do with how the BASE output from SQL Server is going to
come to you. Always has and always will.
Why not create an array or a temp table to hold the keys from N random IDs
SELECT TOP 1 ID
FROM TheTableIneedToAudit
Where createDate between @FirstofLastMonth and @LastofLastMonth
ORDER BY NEWID()
--
Stephen Russell
Unified Health Services
60 Germantown Court
Suite 220
Cordova, TN 38018
Telephone: 888.510.2667
901.246-0159 cell
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/BANLkTin4raU_46Q1Bkw9drBFoRDrw1wGyw@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
That is precisely what I needed!!!
I had no clue of what the newid() function was good for, but it really
selects one random record from the table, every time it is called.
So I do not need to use the VFP rand() function at all, simply send the
command:
select top 1 * from inventory order by newid()
as you suggested, and call it in a loop that will iterate as many times
as needed to pull the desired number of records, like this
cCmd = 'select top 1 * from inventory order by newid()'
for i = 1 to 20 && just want to select 20 records at random
sqlexec(nHandle,cCmd,'curAudit')
select myauditingcursor
append from dbf('curAudit')
endfor
Very cool, thank you very much
BTW Lou, I do have an autoincrementing column, set as identity, but as
you said, if the record is deleted, I might try to get a record that
does not exist, and then I would be screwed (excuse my Spanish, please)
Rafael Copquin
El 31/05/2011 13:11, Stephen Russell escribió:
> On Tue, May 31, 2011 at 11:04 AM, Rafael Copquin<rcopquin@ciudad.com.ar> wrote:
>> I am developing a routine that randomly selects inventory items from a
>> SQL Server table, for inventory control purposes.
>>
>> The item code is numeric, ie: 2345, 7867, 127435, etc. However, there
>> are less than 2000 records in the table
>>
>> So I can apply the vfp rand function like so
>>
>> RandomNbr = int( rand(-1) * 1987 )
>>
>> to select a specific record, say 469, which could have an id number like
>> 8976 and contain an item code like 789678
>>
>> If I were in VFP I would simply locate for recno() = 469 and get the
>> item code.
>>
>> But, what is the equivalent to the recno() function in TSQL, if there is
>> one? And if there is not, how do I get to the particular record?
> -------------------
>
>
> How many do you need to get?
>
> A For loop with this command:
> SELECT TOP 1 * FROM TheTableIneedToAudit ORDER BY NEWID()
>
> Will give you what you need.
>
>
>
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4DE5512A.1070005@ciudad.com.ar
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On Tue, 31 May 2011 13:04 -0300, "Rafael Copquin"
<rcopquin@ciudad.com.ar> wrote:
> But, what is the equivalent to the recno() function in TSQL, if there is
> one? And if there is not, how do I get to the particular record?
Wait ... are you telling me the first thing you did when designing the
SQL Server table wasn't to add an auto-incrementing id field? :)
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/1306916415.9592.1458257881@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Of course not!!
In fact, that is the first thing I always do, whether in SQL or in VFP.
The problem is that if a record is deleted, the id is no good,
especially if id numbers go from 10000 to 49000 , the code number goes
from 1 to 999999 and there are only 2000 records in the table.
BTW, I used Stephen's suggestion and it works very well
Rafael Copquin
El 01/06/2011 5:20, Alan Bourke escribió:
>
> On Tue, 31 May 2011 13:04 -0300, "Rafael Copquin"
> <rcopquin@ciudad.com.ar> wrote:
>> But, what is the equivalent to the recno() function in TSQL, if there is
>> one? And if there is not, how do I get to the particular record?
>
> Wait ... are you telling me the first thing you did when designing the
> SQL Server table wasn't to add an auto-incrementing id field? :)
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4DE6828F.4070908@ciudad.com.ar
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.