>>>I read a tip in July's Foxpro Advisor that you can use paramters for other than the where clause in an SQL view, fox example the Order by (provided you use the numeric construct, e.g. ORDER BY 2, instead of the field name). I couldn't get it to work however (got an error when I included ORDER BY ?testorde after setting testorde to 2 in the command window). Has anyone had any success or attempted this?
>>>
>>>I was intrigued by the idea as I'm trying to create an SQL for a report, but my users want to have flexibility in how it is ordered: Alphabetically, by District, or by Category. I don't want to create many different instances of the same SQL for this purpose. Perhaps I'm overlooking something obvious or another way to allow for this flexibility.
>>>
>>>I'd appreciate any ideas.
>>>
>>>TIA
>>>
>>>Sylvia
>>Sylvia,
>>I never done it with "?testorde" style but generally use numeric instead of field name (ie: select field1+field2+substr(field3,4,3) as somedata,* order by 1 works but not order by field1+field2+substr(field3,4,3) or somedata). If you would provide testorde just before the SQL then only replacing "?" with "&" would do the job :
testorder="2"
>>select * from mytable order by &testorder
Cetin
>
>Cetin -
>
>Thanks - this is interesting. I assume you concatenate your fields in this example because of your substring? When I was testing my original SQL for syntax, I was using ORDER BY field5, field4, field3, so didn't run into this problem, but this is good to know.
>
>Do you allow your users to select the way in which the result is ordered, i.e. by selecting the available fields in some way, then populating the 'testorder' variable with the result (that is what I am hoping to do) or do you let them choose between several sort options (e.g. 1, 2, or 3)?
>
>By the way, I vaguely remember that in using the Select
AS clause in a program I wrote several years ago for a browse (I conveniently can't locate it now;), I created a fieldname containing two words which could be used as a header, e.g. "First Name" as opposed to simply "First". Perhaps this is selective memory or wishful thinking on my part, but it sure would be convenient now. Have you ever done anything like this?
>
>Sylvia
Sylvia,
Concatanation there was an example why you would need a number in Order by. It could be any expression (ie: sum((field1+field2)/field3)) and I don't know another way of ordering on that expression. Secondly if there were no expression at all (fields as usual) still I use numbering a lot because sometimes I wish to change just the selected fields and still sort on them the way they appear in select. ie : They could select the fields from a popup and as they select fields are added in the order they should be "ordered by" (order by 1,2,3). And of course at almost all places where SQL would generate error as "Invalid order by" I use numbering.
I couldn't succeed As clause contain spaces yet (but fortunately it supports underscore and more than 10 chars).
Cetin
