A
andrew strebkov
Hello, All:
The following problem exists only in queries with multiple
tables, and in Excel version 2000 standard addition (there
could be other versions with similar behavious, outside of
my experience/observation):
_____________________________________________________
Some time ago I implemented ODBC queries in Excel with
column aliases, with no problem, regardless of whether the
query had one table or multiple tables. (I.e., select [my
complicated and garbled column value derivation]
AS 'MY_ALIAS').
I recently moved to a different Win 2000 computer with
Excel 2000 (9.0.6926 SP-3) on it and tried to re-implement
my previously developed (and working!) queries. To my
chagrin I discovered that those multiple-table queries
that had the aliases (and which had worked perfectly in
earlier version of Excel), are now failing to stay in
tact. Column aliases are being (silently) stripped from
these queries, and the queries are being executed without
them.
Previously, not only was I able to _create_ a column
alias, for better legibility of the results - I was even
able to REUSE THE ALIAS USED IN THE SELECT CLAUSE IN THE
ORDER CLAUSE. This capability obviously raises the value
of aliasing beyond esthetic benefit, to a more functional
one. (When implementing the alias in the ORDER clause in
Excel 2000 I get an "unknown column" type error message
and the query fails)
Excel 2000 seems to be depriving me of both the esthetic
and functional benefit of column aliasing.
Examples:
1) Use of column aliasing for strictly esthetic/legibility
benefit:
select real_column1, max(date) as 'Latest' from mydb1 left
outer join mydb2 on (....) group by real_column1
(note the alias "as 'Latest'")
the results should come back in two columns, the first
labeled 'real_column1', and the second labeled 'Latest'
(rather than 'max(date)').
2) Use of column aliasing extended to functionality
select max(date) as 'Latest' .... order by 'Latest'
(note the added order clause, where the contrived alias
name 'Latest' is reused for ordering)
My backend has been MySQL, with which previous versions of
Excel did not strip my column aliases.
Also, note that, in this Excel 2000 version at issue, in
single-table queries the alias ARE NOT DELETED, and work
fine.
What am I missing? Is there a setting somewhere that
prevents this behaviour ? I have searched the menus and t
he help file to no avail. Could this a bug? If so, is
there a fix?
thanks in advance
andrew
The following problem exists only in queries with multiple
tables, and in Excel version 2000 standard addition (there
could be other versions with similar behavious, outside of
my experience/observation):
_____________________________________________________
Some time ago I implemented ODBC queries in Excel with
column aliases, with no problem, regardless of whether the
query had one table or multiple tables. (I.e., select [my
complicated and garbled column value derivation]
AS 'MY_ALIAS').
I recently moved to a different Win 2000 computer with
Excel 2000 (9.0.6926 SP-3) on it and tried to re-implement
my previously developed (and working!) queries. To my
chagrin I discovered that those multiple-table queries
that had the aliases (and which had worked perfectly in
earlier version of Excel), are now failing to stay in
tact. Column aliases are being (silently) stripped from
these queries, and the queries are being executed without
them.
Previously, not only was I able to _create_ a column
alias, for better legibility of the results - I was even
able to REUSE THE ALIAS USED IN THE SELECT CLAUSE IN THE
ORDER CLAUSE. This capability obviously raises the value
of aliasing beyond esthetic benefit, to a more functional
one. (When implementing the alias in the ORDER clause in
Excel 2000 I get an "unknown column" type error message
and the query fails)
Excel 2000 seems to be depriving me of both the esthetic
and functional benefit of column aliasing.
Examples:
1) Use of column aliasing for strictly esthetic/legibility
benefit:
select real_column1, max(date) as 'Latest' from mydb1 left
outer join mydb2 on (....) group by real_column1
(note the alias "as 'Latest'")
the results should come back in two columns, the first
labeled 'real_column1', and the second labeled 'Latest'
(rather than 'max(date)').
2) Use of column aliasing extended to functionality
select max(date) as 'Latest' .... order by 'Latest'
(note the added order clause, where the contrived alias
name 'Latest' is reused for ordering)
My backend has been MySQL, with which previous versions of
Excel did not strip my column aliases.
Also, note that, in this Excel 2000 version at issue, in
single-table queries the alias ARE NOT DELETED, and work
fine.
What am I missing? Is there a setting somewhere that
prevents this behaviour ? I have searched the menus and t
he help file to no avail. Could this a bug? If so, is
there a fix?
thanks in advance
andrew