Can I Dynamically Modify a Remote View's Sort?

[Mike555]

Hi. I have a Remote View in VFP that does not have a SORT BY clause. However, I want to apply a SORT BY clause at runtime. Basically each time I call the remote view I'd like to 'pass in' a SORT BY clause. This would allow the view to dynamically sort based on the current situation. Is this possible?

Thanks.

 

[Mike Lewis]

Mike,

First, I take it you mean an ORDER BY clause. SQL does not have a SORT BY clause.

If that's so, the short answer is no. What you want to do is to parameterise the sort order. A remote view doesn't let you do that.

However, there are a couple of easy workaounds. You can construct the remote view dynamically, using the CREATE SQL VIEW command.

Or, much more easily, create a remote view in the usual way without ordering, then get the results into a cursor, which you can then order. For example:

lcSortKey = < the field you want to sort on >
USE MyView
SELECT * FROM MyView ORDER BY &lcSortKey

Is that any use to you?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[alvechurchdata]

You could index the view and SET ORDER TO ...

Don't know if that would be faster or slower than Mike's idea of a second query.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[JRB-Bldr]

Or you can build an Index on the View once you have acquired the View's records.

lcSortKey = < the field you want to sort on >
USE MyView IN 0
INDEX ON lcSortKey TAG SortKey
GO TOP

Good Luck,
JRB-Bldr

 

[Mike555]

Mike Lewis, Thanks for that very thorough explanation.

I'm going to implement the INDEX ON method as suggested by jrbbldr. It seems to be the best method for my situation.

Thank you all for your help!

Mike

 

[Olaf Doschke]

Hi Mike Lewis!

Is ORDER BY <columnnumber> not standard SQL? Then this could be parameterized, couldn't it?

Let's see: A view definition like this works, if nOrder is undefined:

Release nOrder
CREATE SQL VIEW myview As select test1, test2, test3 from table1 order by ?nOrder

During view definition VFP asks you to input nOrder, and you can eg enter 1.

But a requery raises an error "column NORDER not found" if you do eg:

nOrder = 2
Requery()

So unfortunately the view has to be created dynamically or you need several views with each possible ORDER clause.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

Is ORDER BY <columnnumber> not standard SQL? Then this could be parameterized, couldn't it?

Yes, of course it's standard SQL. But, as you've found, it can't be paramterised using the ? syntax, like you can with values in the WHERE clause.

Personally, I would create the view dynamically. But the other options (selecting it into a second cursor, or indexing it) are easier, albeit slower.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)