I'm attempting to grab a variable number of records off the top of an
ordered SQL-Select statement. Everything else about the SELECT statement
will be the same.
I will continue trying things but thought I'd throw this e-mail out to
perhaps short-circuit the cycle.
In essence, the following code demonstrates what I'd *like* to do:
SELECT parenttable
SCAN
SELE TOP parenttable.nQty ;
fld1, fld2, fld3 ;
FROM childtable ;
ORDER BY 1
*-- subsequent processing
ENDSCAN
I've tried storing the following three items to variables and then
macro-expanding them as appropriate, but all without success:
"TOP " + TRANS(parenttable.nQty)
"TOP " + TRANS(parenttable.nQty) + " fld1, fld2, fld3"
TRANS(parenttable.nQty)
What they return is *EVERYTHING* from the child table, not the TOP x.
--Brad "I want the TOP x" Jones
On 7/12/2000 2:15 PM, Brad Jones supposedly said:
>I'm attempting to grab a variable number of records off the top of an
>ordered SQL-Select statement. Everything else about the SELECT statement
>will be the same.
Use parentheses.
SELE TOP (parenttable.nQty) ;
fld1, fld2, fld3 ;
FROM childtable ;
ORDER BY 1
___/
/
__/
/
____/
Ed Leafe
Which reminds me... I tried that one too; also to no avail.
Thanks though!
I feel like I'm missing something obvious here. Ed, do you *know* that what
you suggested should work? I can't recall ever using the TOP clause before.
--Brad "Still Strugglin'" Jones
> -----Original Message-----
> From: Ed Leafe
> Sent: Wednesday, July 12, 2000 2:22 PM
>
> Use parentheses.
>
> SELE TOP (parenttable.nQty) ;
> fld1, fld2, fld3 ;
> FROM childtable ;
> ORDER BY 1
Actually... Nevermind my previous response. I knew it was something
obvious. I was assuming that I knew what the variable number was and that
it was much lower than what I was receiving.
Never assume. Never assume. Never assume.
Incidentally, the parentheses do indeed work -- but I'm sure you knew that.
--Brad
> -----Original Message-----
> From: Ed Leafe
> Sent: Wednesday, July 12, 2000 2:22 PM
>
> Use parentheses.
>
> SELE TOP (parenttable.nQty) ;
> fld1, fld2, fld3 ;
> FROM childtable ;
> ORDER BY 1
I have a legacy vfp 6 database that I am migrating to SQL Server 2008.
The items table has a field named "itemcode" with a character field type
of width 6
This field would suposedly contain alphanumeric codes, but the client
always used numeric codes, starting in '1' and ending in '999999'
I migrated the data to a SQL Server table, making the itemcode field of
type char(6) not null.
Now I want to bring a limited number of records from this table, to show
in a VFP grid. Because the grid can only show 20 records at a time, I
developed a pagination routine that only brings 20 records at a time,
when the user presses the next page or the previous page buttons on the
form.
My problem is with the previous page routine. My statement is:
Text to cCmd textmerge noshow flags 2 pretext 15
select top 20 itemcode,(some more records)
from silver.dbo.items
where itemcode< 23
order by itemcode desc
endtext
SQLExec(thisform.nHandle,cCmd,'curItems')
The cursor curItems brings the correct list of items (13 to 22) but
ordered from 22 to 13. Since I want them to be ordered like: 13 to 22, I
change the order by clause as: order by itemcode asc.
But I get records 1 to 10, and I want 13 to 22.
I am aware that I can live with curItems the way SQL Server generates it
and then simply issue:
select curItems
index on itemcode tag itemcode
or alternatively
select * from curItems into cursor curAnotherCursor order by itemcode
But I do not want to have a VFP index in this cursor or have to create
another cursor from the obtained recordset. I just want to get the
cursor directly from SQL Server, ordered from 13 to 22.
Any suggestions?
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/4D626853.7040007@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.
Since they're all numeric, add a column in SQL Server of type integer,
copy the contents of itemcode into it, and use that instead.
--
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/1298301370.5095.1422172933@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.
That's another way of doing it, thanks, but I'd rather have SQL give me
the right answer.
El 21/02/2011 12:16, Alan Bourke escribió:
> Since they're all numeric, add a column in SQL Server of type integer,
> copy the contents of itemcode into it, and use that instead.
_______________________________________________
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/4D62A23B.90303@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.
You can create a cursor on the server and move back and forth on it,
but that can be pretty complex and tie up the connection, expensive
both on client and server. I've used Progressive Fetch before, but
it's likely not what you want. However, try this:
A hack I ran into in a PHP framework (CodeIgniter) used this
workaround to emulate the "LIMIT" clause that other db have:
Take your SELECT <fieldlist> FROM <tables and joins> WHERE <clause>
ORDER BY <expression ASC/DESC>
Drop the 'top 20' and change to:
SELECT (row_number() OVER (order by expression), <fieldlist> FROM
<tables and joins> WHERE <clause> ORDER BY <expression ASC/DESC>) AS A
WHERE A.rownum BETWEEN (offset+1) AND (offset + limit)
Where offset is the starting row number (typically zero-based, hence
the plus one) and limit is the number of rows you want retrieved, 20
in your case.
This works with newer SQL Servers (I'm running it on 2008R2) but not
on older ones (I've heard it doesn't work on 2000, but you shouldn't
be using that anyway, right?)
On Mon, Feb 21, 2011 at 12:34 PM, Rafael Copquin <rcopquin@ciudad.com.ar> wrote:
> That's another way of doing it, thanks, but I'd rather have SQL give me
> the right answer.
>
>
> El 21/02/2011 12:16, Alan Bourke escribió:
>> Since they're all numeric, add a column in SQL Server of type integer,
>> copy the contents of itemcode into it, and use that instead.
>
--
Ted Roche
Ted Roche & Associates, LLC
_______________________________________________
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/AANLkTim0EUqGsvBj=Dit13t3b6nmEuBXc8csQZoEMSNf@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 Mon, Feb 21, 2011 at 11:34 AM, Rafael Copquin <rcopquin@ciudad.com.ar> wrote:
> That's another way of doing it, thanks, but I'd rather have SQL give me
> the right answer.
-----------------
I would make a SP to give you the right answer. A param to pass in
would be what page do you want? If user was on page 1 and wanted to
go to page 5 allow them to tell you last page # instead of next, next,
next.
No cursors either on the SQL side please.
Now do you know how to identify how many pages you are going to get?
Can you pull subsets form one group to another easily?
Is there a SQL person on site at this location to run this by, or is that you?
--
Stephen Russell
Sr. Production Systems Programmer
CIMSgts
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/AANLkTikkQAj4nw8QPwn9-1Qs-v-ZHx+r-2N+-mmypahO@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.
>
> But I do not want to have a VFP index in this cursor or have to create
> another cursor from the obtained recordset. I just want to get the
> cursor directly from SQL Server, ordered from 13 to 22.
>
> Any suggestions?
You may use a nested subquery :
SELECT * FROM (;
select top 20 itemcode,(some more records) ;
from silver.dbo.items ;
where itemcode< 23 ;
order by itemcode desc ;
) AS xx ;
ORDER BY itemcode
Hope this helps.
Gérard.
_______________________________________________
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/84D9FCB67732409CA4DC94B5BCA0E9DB@MuriellePC
** 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.