How to refer to two columns w/ same name

[MrFurious]

I have a pass-through query that returns two columns with the same name and I can't figure out how to refer to the second one. Using 'CustName' returns the value from the first column named CustName in the query. How can I refer to the second one?

Note: I can't alias the field names in the pass-through query. The ODBC driver doesn't support column name aliasing.

Example:
SELECT CustName, CustName, CustName (it will always return the value in the first column named 'CustName')
FROM OpenQuery(LinkedServerData,
'SELECT BC.CustName, SC.CustName
FROM InvoiceTable IT
INNER JOIN Cust BC ON IT.BillTo=BC.CustID
INNER JOIN Cust SC ON IT.ShipTo=SC.CustID')

Anyone have an answer for this? I'm stumped.

 

[JFoushee]

Have you ever used the AS clause?

You could do something like

SELECT BC.CustName AS BC_CustName
     , SC.CustName AS SC_CustName

or some similar variation, then refer to "BC_CustName" (notice underscores) or "SC_CustName" to differentiate.

Hope this helps.

 

[MrFurious]

I can't alias the column name because of the ODBC driver I'm using. So no "As" allowed. It's the only driver that exists for this data source so I'm kind of stuck. Thanks for the response though.

 

[Stamey]

Why wouldn't Table1.CustName, Table2.CustName work? No aliases, just complete table names.

Chris

 

[Stamey]

I have also used:
SELECT Order_Information.User_ID UserID, Users.User_ID
FROM Order_Information, Users;
IN a SQL passthrough query from Access 2000 inot a SQL 7.0 DB. Note the alias, but no "AS".

Chris

 

[fluteplr]

Maybe I missed something here. Since you are posting to a MS SQL forum I assume your back end is MS SQL Server. Is it? If not what is it? If so, where did you find this weird ODBC driver?

 

[MrFurious]

I work for a publishing company that uses a really old order entry program written in Data Bus called CATS. Believe me, the program is wierd. I want to pull invoice information with bill to and ship to information as well. But since I'm linking to the customer table twice the query returns column names twice. It's not the "As" the driver has a problem with. It just won't alias column names. The funny part is. The error message says it doesn't support aliasing. So it knows what I'm trying to do but it won't let me do it. It also doesn't support aggregate functions either. Stupid!

I was hoping someone knew of a way to refer to the fields using an index number or something else I don't even know about. Thanks for your help!

 

[MrFurious]

I forgot to mention that the pass-through query does not return a fully qualified field name like TableName.ColumnName so that's not an option.

 

[Stamey]

How about creating a view on the back end for one of the tables and aliasing the column names as necessary so your front end can reference it correctly? Is that possible?

Chris