Board index » off-topic » Sort a query

Sort a query


2004-12-08 10:56:49 PM
off-topic17
Hello,
I have a question..
I use a TQuery object to query a database and I want to sort the results of
my "SELECT...".
If I add "ORDER BY..." in my query, can I declare a param for the name of
the field I want to sort by ?
For example, the propriety SQL of my TQuery object could be :
"SELECT * FROM Table ORDER BY :FieldParam"
If it's possible, which "DataType" must I choice for the param ?
And else, what's the best solution to change easily the sort of my query ? I
want to show the result in a TDBGrid, and I want to let the user of my
application change the field to sort by dynamically.
Thanks for your help.
Ben
 
 

Re:Sort a query

No. Parameters can only be used for values in the WHERE clause.
Parameters can never be used for database object names. If you do not
identify all of the database objects it is impossible for the DBMS to
optimize the query.
--
Bill (TeamB)
TeamB cannot answer questions received via email
 

Re:Sort a query

Hi Ben,
As Bill said it is not possible. However, I never use Parameters as you
understand them but in fact I use text substitution.
So to take your example SELECT * FROM Table ORDER BY :FieldParam
My function call would be something like
MyExecuteQuery(QueryString, Parameters, NoParameters)
where Query string would be something like
LPSTR Query = SELECT * FROM #0 ORDER BY #1
LPSTR Parameters[];
Parameters[0] = "TABLE1"
Parameters[1] = "A_FIELD_NAME"
and in the case above NoParameters = 2
All the ExecuteQuery() has to do now is to replace *all* occurrences of #0
and #1 with the values in the Parameters array (you might use them a number
of times). This method works for both SQL and QBE scripts and probably for
others I do not know about. I have used this for years know without fail.
Leslie.
"Ben" < XXXX@XXXXX.COM >wrote in message
Quote
Hello,

I have a question..
I use a TQuery object to query a database and I want to sort the results
of
my "SELECT...".
If I add "ORDER BY..." in my query, can I declare a param for the name of
the field I want to sort by ?

For example, the propriety SQL of my TQuery object could be :
"SELECT * FROM Table ORDER BY :FieldParam"

If it's possible, which "DataType" must I choice for the param ?
And else, what's the best solution to change easily the sort of my query ?
I
want to show the result in a TDBGrid, and I want to let the user of my
application change the field to sort by dynamically.

Thanks for your help.
Ben



 

{smallsort}

Re:Sort a query

Thanks Leslie and Bill.
I will use your solution Leslie.
In fact, I'm lazy and I search the more simple solution... lol
But, I'm all the same surprised that there is no "Sort" proriety for TQuery
object.....
So.. I have no other choice... let's go and work... ;o)
See you soon
Ben
--
"Leslie Milburn" < XXXX@XXXXX.COM >a écrit dans le message de
Quote
Hi Ben,

As Bill said it is not possible. However, I never use Parameters as you
understand them but in fact I use text substitution.

So to take your example SELECT * FROM Table ORDER BY :FieldParam

My function call would be something like

MyExecuteQuery(QueryString, Parameters, NoParameters)

where Query string would be something like
LPSTR Query = SELECT * FROM #0 ORDER BY #1

LPSTR Parameters[];

Parameters[0] = "TABLE1"
Parameters[1] = "A_FIELD_NAME"

and in the case above NoParameters = 2

All the ExecuteQuery() has to do now is to replace *all* occurrences of #0
and #1 with the values in the Parameters array (you might use them a
number
of times). This method works for both SQL and QBE scripts and probably for
others I do not know about. I have used this for years know without fail.

Leslie.


"Ben" < XXXX@XXXXX.COM >wrote in message
news:41b71630$ XXXX@XXXXX.COM ...
>Hello,
>
>I have a question..
>I use a TQuery object to query a database and I want to sort the results
of
>my "SELECT...".
>If I add "ORDER BY..." in my query, can I declare a param for the name
of
>the field I want to sort by ?
>
>For example, the propriety SQL of my TQuery object could be :
>"SELECT * FROM Table ORDER BY :FieldParam"
>
>If it's possible, which "DataType" must I choice for the param ?
>And else, what's the best solution to change easily the sort of my query
?
I
>want to show the result in a TDBGrid, and I want to let the user of my
>application change the field to sort by dynamically.
>
>Thanks for your help.
>Ben
 

Re:Sort a query

What do you mean by "proriety"?
--
Bill (TeamB)
TeamB cannot answer questions received via email
Ben wrote:
Quote
But, I'm all the same surprised that there is no "Sort" proriety for
TQuery
 

Re:Sort a query

I'll mean "Property" (sorry for my english...lol) , like in
"TCustomADODataSet " object, there is a propriety "Sort" to specify how to
sort the query's result.
--------------------------------------------------------------------
TCustomADODataSet
__property WideString Sort = {read=GetSort, write=SetSort};
Example :
ADOQuery1->Sort = "LastName ASC, DateDue DESC"
--------------------------------------------------------------------
"Bill Todd" < XXXX@XXXXX.COM >a écrit dans le message de
Quote
What do you mean by "proriety"?

--
Bill (TeamB)
TeamB cannot answer questions received via email

Ben wrote:

>But, I'm all the same surprised that there is no "Sort" proriety for
>TQuery
 

Re:Sort a query

If you want to sort the result set from a query either change the ORDER
BY clause and rerun the query or use a TClientDataSet.
--
Bill Todd (TeamB)
TeamB cannot answer questions received via email