Query Designer field selection and ordering

rbuser

Hello. I am building a query in the Query Designer. Notice I am selecting
two fields ("First" and "Email") and ordering by "LastPhysical" even know it
isn't in the SELECT statement. The Query Designer allows you to select
ordering fields even know they aren't selected on the "Fields" tab.

SELECT Employees.First, Employees.Email
FROM Employees Employees
ORDER BY Employees.LastPhysical

BUT, when I try to run the query using a TDBISAMQuery component, by
retrieving the SQL from the TdaSQL object, it doesn't like the
"LastPhysical" ORDER BY clause. I get an "invalid column name
LastPhysical...." error. I'm guessing because it isn't part of the SELECT
statement???

Why does the Query Designer allow you to order by fields you don't select on
the "Fields" tab? Is there any way to get prevent this? Or, am I missing
something?

Thank you

Brian Kennedy
RB Enterprise 6.03 (this project)
RB Enterprise 7.0

digitalmetaphors

That is the correct behavior. It is advantageous in that you as a developer
can create a dataview and then add search and sort by fields which aren't in
the select fields list. This capability keeps the user from being confused
about why there is a field which should be behind the scenes (as an extreme
example, let's say you a have a cryptic VenDev_PK_ID field name) in the
available pipeline, which is used to search on or order the dataset.
However, the SQL that is generated should be correct. I'm runnign DBISAM
3.17 and RB 7 and it works as designed (haven't tried 6.03 yet, but it
should work the same) . Ctrl click over the dataview, you should see the
Magic SQL that is generated which has the field in the select clause in
order to support this on different databases generically, since it is not
guaranteed that every database can support an Order By on a non grouped
query where the field isn't selected.

Cheers,

Jim Bennett
Digital Metaphors