SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Select All Fields Except...
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

mellamokb
Starting Member

39 Posts

Posted - 03/30/2006 :  17:50:07  Show Profile
Hi,

Is there a way to select all fields from a table except for one, and then select that field as a special value? For instance, let's say I have a table [tblBook] with fields [bookID, pageCount, origPrice, retailPrice, numLeft, author, copyright]. I want to retrieve all fields with SQL code, but I want the retailPrice to be conditional [using a CASE]:


SELECT bookID, pageCount, origPrice, numLeft,
author, copyright,

CASE WHEN numLeft=1 THEN retailPrice*1.2 ELSE retailPrice END AS retailPrice

FROM tblBook


Basically, when there is only one book left, I am multiplying the price by 1.2. However, let's suppose I don't know what fields need to be added to tblBook; thus, I would use tblBook.*. How can I specifiy tblBook.* and still provide the condition for the value of tblBook.retailPrice?

Would I use:

SELECT tblBook.*,

CASE WHEN numLeft=1 THEN retailPrice*1.2 ELSE retailPrice END AS retailPrice

FROM tblBook


or would this give me two fields with the same name [retailPrice]?

Thanks,

mellamokb

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 03/30/2006 :  18:59:31  Show Profile
mellamokb,
U have came up to that, and asking whether it works
Don't u have Query Analyzer installed in the machine you submitted this?

Just Copy & Paste, Execute it.
If u do not get the desired results, post some sample data and the expected results.

Srinika
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 03/30/2006 :  20:42:14  Show Profile
"Is there a way to select all fields from a table except for one"
No. Unless you do it dynamically using Dynamic SQL.



KH

Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 03/30/2006 :  21:04:35  Show Profile
Also refer to here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63016



KH

Choice is an illusion, created between those with power, and those without.
Go to Top of Page

mellamokb
Starting Member

39 Posts

Posted - 03/31/2006 :  09:55:57  Show Profile
I ran the query I submitted and it does work; what I don't understand is how the ASP page determines which of the two fields [retailPrice] to retrieve when I ask for the value by field name. It seems to me like it grabs the last field with the name provided; however, with such ambiguity, how can I be certain that I'll never get the one undoctored field [i.e., the retailPrice not multiplied by 1.2 like it should be]? I know this is more of an ASP question now, but is it possible to tell specifically that the second instance of this field is desired?


Set dbrs = qry_getBookInformation.Execute

Response.Write dbrs("retailPrice")


It seems to me like I couldn't be 100% certain that the above code will give me the changed price instead of the original price as selected by [tblBook.*] in the query. Should I worry or just call it good for now?

thanks,

mellamokb
Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 03/31/2006 :  10:41:00  Show Profile
>> how can I be certain that Ill never get the one undoctored field
T-SQL works in the same way which ever u write ur logic. There is no uncertainity in its selections. If it finds something ambigous, it will tell that

Do u want to know whether the retailPrice is multiplied by 1.2 or not ?
In that case, u can check it in the client side by
-- numLeft
-- returning retailPrice as retailPrice_Original
-- Returning the factor of Multiplication, with an additional Case Statement

>> Should I worry or just call it good for now?
If u REALLY want to worry, u may do so a little per day

Srinika
Go to Top of Page

mellamokb
Starting Member

39 Posts

Posted - 03/31/2006 :  14:45:46  Show Profile
Alright...that makes sense. So if I'm getting the right value in one record, I should get the right value in every record.

Thanks so much for your help, Srinika and khtan.

mellamokb
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000