Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 10th, 2003, 10:27 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL SELECT & aliases

I have a query:

SELECT ClientID AS ID, Name AS [User Name]
FROM tblClients

Is it possible to use both in ADO? (my explanation of why I need to do this is too long):

rs.fields("ClientID")
  ...or...
rs.fields("ID")

I get an error saying that field is not a member of the collection...


 
Old October 11th, 2003, 07:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

It's not possible in ADO or anything else.

The alias in the SELECT clause names the column in the SQL resultset, before something like ADO even sees it. A column only has one name, which by default is the name of the original column.

You could duplicate the column in the resultset, i.e.:
Code:
SELECT ClientID, ClientID AS ID, ...
(I can't imagine why anyone would want to do this to themselves. :) I'd be interested in hearing the explanation, even if it is long...)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old October 11th, 2003, 12:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi,

I remember that I used a lot of times something similar to this and had no problems as U.N.C.L.E ;) had faced.

Here is what I have done recently.

Query=SELECT MAX(ProductId)+1 AS NewProductId FROM Products

And have accessed it using

rs("NewProductId")

Which worked... and was able to use its value for inserting a new record into Products.

Also used COALESC function in many occasions and accessed its values as Aliases.

Just wondering!!!...

Jeff,
This is somthing that we could face manytimes going for aliases, in case there is a necessity to access a userdefined column but not the original column from the table itself.

E.g. - May be as I mentioned above one may need to get the incremneted value from the table or sometimes would try to calculate something from the table and try to get it back as aliases in the frontend.

Hope this is what U.N.C.L.E would have tried to do.

Still eagar to hear his explanations, as I dont see any calculations done in his case.

-Vijay G
 
Old October 11th, 2003, 12:43 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you for your response.

The explanation is that I wanted to reuse some existing code that relies on the original field/column names. I planned to create several queries for the purpose of exporting information out of the database, but each client has different column name and record criteria requirements...

The capability in question would allow me to have one query for each client with the column names as per the client requirements but still be able to reuse the existing code to manipulate the recordset after export.

Thanks again anyway for your response.


 
Old October 11th, 2003, 12:49 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I forgot to mention that what I did finally is to have two queries. Both with the same select criteria, but one with the original column names and one designed for export with client required column names.

Hope that makes some sense. If anyone has a better suggestion, I would be interested in hearing it, because as it is now, I now have to manage the criteria in two different places. Not too troublesome, but annoying never-the-less. :D

 
Old October 11th, 2003, 12:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by happygv
 Here is what I have done recently.

Query=SELECT MAX(ProductId)+1 AS NewProductId FROM Products

And have accessed it using

rs("NewProductId")

Which worked... and was able to use its value for inserting a new record into Products.
Yes, this would work, as the name of the column in the resultset (which gets turned into an ADO recordset by the OLEDB provider working with ADO) is in fact 'NewProductId'. That column only has that name, and you cannot refer to it by any other name, which is what I read the OP wanted to do.
Quote:
quote:
Also used COALESC[sic] function in many occasions and accessed its values as Aliases.
Again, once you give a column an alias, no matter how the column was derived, be it a column from a table or an expression, you must refer to it by that name when you manipulate the resultset (recordset).
Quote:
quote:
Jeff,
This is somthing that we could face manytimes going for aliases, in case there is a necessity to access a userdefined column but not the original column from the table itself.

E.g. - May be as I mentioned above one may need to get the incremneted value from the table or sometimes would try to calculate something from the table and try to get it back as aliases in the frontend.

Still eagar to hear his explanations, as I dont see any calculations done in his case.
Referring to a column in a resultset which is the result of evaluating an expression is a good reason for a column alias, but not the only one...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old October 11th, 2003, 12:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

I do agree with you Jeff.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Crystal Report & VB6 & SQL Server wlho VB How-To 2 June 5th, 2013 01:58 AM
Problems with Select & Where statement sharon5469 ASP.NET 1.0 and 1.1 Basics 3 June 11th, 2007 07:09 PM
Good way to select & process only one node traxwx XSLT 2 May 21st, 2007 05:08 PM
INSERT & SELECT FROM....? No idea Seb_soum Classic ASP Databases 3 May 14th, 2004 07:06 AM
sql & join tables & find a field in multiple table trangd Beginning PHP 2 January 29th, 2004 07:18 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.