Home End User
New Blog Posts: Merging Reports - Part 1 and Part 2

Query Designer field selection and ordering

edited October 2002 in End User
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

Comments

  • edited October 2002
    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

This discussion has been closed.