Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 10th, 2003, 10:27 PM
Friend of Wrox
Points: 422, Level: 7
Points: 422, Level: 7 Points: 422, Level: 7 Points: 422, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: La Jolla, CA, USA.
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...


Reply With Quote
  #2 (permalink)  
Old October 11th, 2003, 07:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
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
Reply With Quote
  #3 (permalink)  
Old October 11th, 2003, 12:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
  #4 (permalink)  
Old October 11th, 2003, 12:43 PM
Friend of Wrox
Points: 422, Level: 7
Points: 422, Level: 7 Points: 422, Level: 7 Points: 422, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: La Jolla, CA, USA.
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.


Reply With Quote
  #5 (permalink)  
Old October 11th, 2003, 12:49 PM
Friend of Wrox
Points: 422, Level: 7
Points: 422, Level: 7 Points: 422, Level: 7 Points: 422, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: La Jolla, CA, USA.
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

Reply With Quote
  #6 (permalink)  
Old October 11th, 2003, 12:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
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
Reply With Quote
  #7 (permalink)  
Old October 11th, 2003, 12:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

I do agree with you Jeff.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 08:32 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.