At 11:18 AM 3/1/2001 +0000, Ian A. Newby wrote:

> In other SQL databases, the expression
>
>select col1 * 10 as a, col2 + 5 as b, a + b as c
> from mytable
> order by c, b, a
>
>is allowed.
>
>How difficult would it be to allow this type of sql statement to be
>accepted?

Not particularly. The underlying engine would have no trouble
with the query. It would require changes to DSQL, adding new
syntax, teaching it to recognize symbols in new places, convincing
it to generate the appropriate BLR.

>I belive it splits into two problems:
>
>1. Ordering by calculated column names (not ordinal position).

Should be trivial - if the column names are carried around (or
there's something to carry them in). Otherwise, annoying but
not earthshaking.

>2. Allow calculated column names to be used in further calculated
> columns.

Basically the same.

>Just out of interest, what does FB do when the above SQL is written as
>
>select col1 * 10 as a, col2 + 5 as b, ((col1 * 10) + (col2 + 5)) as c
>from mytable order by 3, 2, 1?
>
>Does it read col1 and col2 multiple times from the database (not a
>problem with this example but when they are subqueries it could be).

Yes, it reads the columns for each reference, and yes, that could
be expensive.


Regards,

Ann
www.ibphoenix.com
We have answers.