Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mellamokb
Starting Member
39 Posts |
 Posted - 2006-03-30 : 17:50:07
|
| 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]:[CODE]SELECT bookID, pageCount, origPrice, numLeft,author, copyright,CASE WHEN numLeft=1 THEN retailPrice*1.2 ELSE retailPrice END AS retailPriceFROM tblBook[/CODE]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:[CODE]SELECT tblBook.*,CASE WHEN numLeft=1 THEN retailPrice*1.2 ELSE retailPrice END AS retailPriceFROM tblBook[/CODE]or would this give me two fields with the same name [retailPrice]?Thanks,mellamokb |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-30 : 18:59:31
|
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 |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-30 : 20:42:14
|
"Is there a way to select all fields from a table except for one"No. Unless you do it dynamically using Dynamic SQL.
KHChoice 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 |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
mellamokb
Starting Member
39 Posts |
Posted - 2006-03-31 : 09:55:57
|
| 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?[CODE]Set dbrs = qry_getBookInformation.ExecuteResponse.Write dbrs("retailPrice")[/CODE]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 |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-31 : 10:41:00
|
>> how can I be certain that Ill never get the one undoctored fieldT-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 thatDo 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 |
|
|
|
mellamokb
Starting Member
39 Posts |
Posted - 2006-03-31 : 14:45:46
|
| 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 |
|
|
|
|
|
|
|
|
Select All Fields Except...