With command-line interface ( 3.23.37, UNIX Socket ) all is well with
column aliasing. However, column aliases disappear in Excel, over
ODBC, when there are multiple (joined) tables in the query.
I run the following query with aliased columns with CLI:
select table1.c1 as 'pet category', table1.c2 as 'item', table1.c3 as
'quantity', table2.c2 as 'attendant' from table1 left outer join
table2 on (table1.c1=table2.c1);
+--------------+--------+----------+------------+
| pet category | item | quantity | attendant |
+--------------+--------+----------+------------+
| dog | leash | 3 | Dan Miller |
| cat | collar | 5 | Kim Sloan |
| dog | collar | 4 | Dan Miller |
+--------------+--------+----------+------------+
3 rows in set (0.00 sec)
and all is well. The table1.c1 column is labeled as 'pet category',
table1.c2 as 'item', etc., etc.
But executing this same query in Excel ODBC my labels revert to the
original column names, and the aliases "peth category", "item",
....etc, are ignored:
c1 c2 c3 c2
Interestingly, though, Excel/MyODBC _retains_ column aliases for
single-table queries.
Is there some setting that can correct this? My
Edit->Options->"Qualify table names in SQL statement" box is grayed
out. So is the "Prodedures" button in the File->Execute SQL dialogue.
This phenomenon occurs in many versions of Excel, including 2000 and
97.
Thanks!
andrew 3 6009
After posting my message (quoted here) I discovered a case where Excel
drops the column alias with one table in the query. My query is
select schools.*, if( sid between 5000 and 6000, 1, 0) as 'do'
from schools
order by do, dist, name
Please note the "do" alias and the IF statement column it represents.
In this column a row is qualified (with either 1 or 0), based on the
IF condition, then the value is used in one of the sort levels (the
first one). The "order by" syntax lets you use the alias you've set
in the select clause...
I was happy to discover that aliases can be more than cosmetic - that
they can be as functional as allowing a sort otherwise impossible
(impossible without the alias).
And now I am suffering from this Excel alias omission problem where,
for a query such as above, the aliases get erased, and Excel tells me
"unknown column 'do' in order clause", after having wiped that which
it complains it can't find, from the select clause. Frustrating!
Any ideas?
thanks in advance se*****@scssi.org (Andrew) wrote in message news:<a8**************************@posting.google. com>... With command-line interface ( 3.23.37, UNIX Socket ) all is well with column aliasing. However, column aliases disappear in Excel, over ODBC, when there are multiple (joined) tables in the query.
I run the following query with aliased columns with CLI:
select table1.c1 as 'pet category', table1.c2 as 'item', table1.c3 as 'quantity', table2.c2 as 'attendant' from table1 left outer join table2 on (table1.c1=table2.c1); +--------------+--------+----------+------------+ | pet category | item | quantity | attendant | +--------------+--------+----------+------------+ | dog | leash | 3 | Dan Miller | | cat | collar | 5 | Kim Sloan | | dog | collar | 4 | Dan Miller | +--------------+--------+----------+------------+ 3 rows in set (0.00 sec)
and all is well. The table1.c1 column is labeled as 'pet category', table1.c2 as 'item', etc., etc.
But executing this same query in Excel ODBC my labels revert to the original column names, and the aliases "peth category", "item", ...etc, are ignored: c1 c2 c3 c2
Interestingly, though, Excel/MyODBC _retains_ column aliases for single-table queries.
Is there some setting that can correct this? My Edit->Options->"Qualify table names in SQL statement" box is grayed out. So is the "Prodedures" button in the File->Execute SQL dialogue.
This phenomenon occurs in many versions of Excel, including 2000 and 97.
Thanks!
andrew
After posting my message (quoted here) I discovered a case where Excel
drops the column alias with one table in the query. My query is
select schools.*, if( sid between 5000 and 6000, 1, 0) as 'do'
from schools
order by do, dist, name
Please note the "do" alias and the IF statement column it represents.
In this column a row is qualified (with either 1 or 0), based on the
IF condition, then the value is used in one of the sort levels (the
first one). The "order by" syntax lets you use the alias you've set
in the select clause...
I was happy to discover that aliases can be more than cosmetic - that
they can be as functional as allowing a sort otherwise impossible
(impossible without the alias).
And now I am suffering from this Excel alias omission problem where,
for a query such as above, the aliases get erased, and Excel tells me
"unknown column 'do' in order clause", after having wiped that which
it complains it can't find, from the select clause. Frustrating!
Any ideas?
thanks in advance se*****@scssi.org (Andrew) wrote in message news:<a8**************************@posting.google. com>... With command-line interface ( 3.23.37, UNIX Socket ) all is well with column aliasing. However, column aliases disappear in Excel, over ODBC, when there are multiple (joined) tables in the query.
I run the following query with aliased columns with CLI:
select table1.c1 as 'pet category', table1.c2 as 'item', table1.c3 as 'quantity', table2.c2 as 'attendant' from table1 left outer join table2 on (table1.c1=table2.c1); +--------------+--------+----------+------------+ | pet category | item | quantity | attendant | +--------------+--------+----------+------------+ | dog | leash | 3 | Dan Miller | | cat | collar | 5 | Kim Sloan | | dog | collar | 4 | Dan Miller | +--------------+--------+----------+------------+ 3 rows in set (0.00 sec)
and all is well. The table1.c1 column is labeled as 'pet category', table1.c2 as 'item', etc., etc.
But executing this same query in Excel ODBC my labels revert to the original column names, and the aliases "peth category", "item", ...etc, are ignored: c1 c2 c3 c2
Interestingly, though, Excel/MyODBC _retains_ column aliases for single-table queries.
Is there some setting that can correct this? My Edit->Options->"Qualify table names in SQL statement" box is grayed out. So is the "Prodedures" button in the File->Execute SQL dialogue.
This phenomenon occurs in many versions of Excel, including 2000 and 97.
Thanks!
andrew
After posting my message (quoted here) I discovered a case where Excel
drops the column alias with one table in the query. My query is
select schools.*, if( sid between 5000 and 6000, 1, 0) as 'do'
from schools
order by do, dist, name
Please note the "do" alias and the IF statement column it represents.
In this column a row is qualified (with either 1 or 0), based on the
IF condition, then the value is used in one of the sort levels (the
first one). The "order by" syntax lets you use the alias you've set
in the select clause...
I was happy to discover that aliases can be more than cosmetic - that
they can be as functional as allowing a sort otherwise impossible
(impossible without the alias).
And now I am suffering from this Excel alias omission problem where,
for a query such as above, the aliases get erased, and Excel tells me
"unknown column 'do' in order clause", after having wiped that which
it complains it can't find, from the select clause. Frustrating!
Any ideas?
thanks in advance se*****@scssi.org (Andrew) wrote in message news:<a8**************************@posting.google. com>... With command-line interface ( 3.23.37, UNIX Socket ) all is well with column aliasing. However, column aliases disappear in Excel, over ODBC, when there are multiple (joined) tables in the query.
I run the following query with aliased columns with CLI:
select table1.c1 as 'pet category', table1.c2 as 'item', table1.c3 as 'quantity', table2.c2 as 'attendant' from table1 left outer join table2 on (table1.c1=table2.c1); +--------------+--------+----------+------------+ | pet category | item | quantity | attendant | +--------------+--------+----------+------------+ | dog | leash | 3 | Dan Miller | | cat | collar | 5 | Kim Sloan | | dog | collar | 4 | Dan Miller | +--------------+--------+----------+------------+ 3 rows in set (0.00 sec)
and all is well. The table1.c1 column is labeled as 'pet category', table1.c2 as 'item', etc., etc.
But executing this same query in Excel ODBC my labels revert to the original column names, and the aliases "peth category", "item", ...etc, are ignored: c1 c2 c3 c2
Interestingly, though, Excel/MyODBC _retains_ column aliases for single-table queries.
Is there some setting that can correct this? My Edit->Options->"Qualify table names in SQL statement" box is grayed out. So is the "Prodedures" button in the File->Execute SQL dialogue.
This phenomenon occurs in many versions of Excel, including 2000 and 97.
Thanks!
andrew This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Andrew |
last post by:
With command-line interface ( 3.23.37, UNIX Socket ) all is well with
column aliasing. However, column aliases disappear in Excel, over
ODBC, when there are multiple (joined) tables in the query.
...
|
by: bawar |
last post by:
Hey, I can say I belong to the newbies in this subject. I own my own
website with mySQL-databases, but now I have to show one table of
those mySQL-databases in an excel worksheet. It is the...
|
by: Paul |
last post by:
Hi all
Arggghhh...........
The problem.....I want the user to be able to create an excel document and
name particular cells in the document where they want the data to be placed
and then save...
|
by: Geir Baardsen |
last post by:
Hi!
From one of my manufacturers I get new prices in an excel spreadsheet.
Now I want to import this data to my access db.
However, there is a difference:
The items in MyAccess.db...
|
by: acharyaks |
last post by:
Hi life saver,
I am using excel component for the development.
The purpose is to connect to excel through the odbc
connection string. Then through the connection extract
data into a dataset and...
|
by: Peter |
last post by:
I am having a problem reading an Excel file that is XML based. The directory
I am reading contains Excel files that can be of two types. Either generic Microsoft
based or XML based. I am reading...
|
by: Bernie Yaeger |
last post by:
I use the following writexml method to convert a dataset/datatable to .xml:
ds.WriteXml(mstrpath, XmlWriteMode.WriteSchema)
ds.WriteXmlSchema(mstrpathxsd)
The path strings are simple full...
|
by: Eric E |
last post by:
Hi,
I'm trying to write a recordset-returning function that returns a
values from a base table, and one column from a joined table, where the
joined table varies according to a field of the base...
|
by: smaczylo |
last post by:
Hello, I've recently been asked to work with Microsoft Access, and
while I feel quite comfortable with Excel, I'm at a complete loss with
databases. If someone could help me with this issue I'm...
|
by: TG |
last post by:
hi!
I am trying to create a sql server table from an excel sheet.
Here is the code I have:
'This procedure the xlsx file and dumps it to a table in SQL Server
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |