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 |
|
ppbedz
Starting Member
2 Posts |
 Posted - 2011-11-04 : 10:17:04
|
| Is it possible to code an sql statement that combines select * with derived columns (see below)? I need to pull in every column from the specified tables. However, I also need to derive a column based on the values of one of the columns. Can I do this without listing every column from all of the tables (there are alot)???? select *, case when acbrand is NULL then crbrand else acbrand end as actbrand from pftrpvtstf left outer join crvalus on afshipto = crshipto and afresc = crresc left outer join crvalustf on afresc = acresc and char(afyear) || char(afmonth) between char(acsyear) || char(acsmonth) and char(aceyear) || char(acemonth) |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-11-04 : 10:43:55
|
yes
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-11-04 : 10:43:59
|
Yes. Did you simply try it?Regarding the column list you should avoid using "*". If you're too lazy to type all the column names (like me) use information_schema views to do the work for you:select ' ,' + column_name from information_schema.columns where table_name = 'pftrpvtstf' order by ordinal_position Be One with the OptimizerTG |
|
|
|
ppbedz
Starting Member
2 Posts |
Posted - 2011-11-04 : 10:54:38
|
| Yes, I did try it. The syntax is invalid. It does not like the "*," followed by the definition of my derived field. I am attempting this on an Iseries as well as using it to pull data into Excel. Neither Excel nor the Iseries likes the syntax of the statement. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-11-04 : 10:56:41
|
quote:
Originally posted by ppbedz via direct email
Yes, I did try it before posting. I am attemping to use this sql statement to pull data into a spreadsheet. I also tried it on our Iseries. Both Excel and the Iseries tell me the syntax is invalid. It does not like the "*", followed by the definition of my derived field.
Don't send direct email to the contributors of this forum. Just post followup questions in the thread - for everyone's benefit.Are you using sql server 2000? Sql server doesn't take the "||" OR operator. Use something like this:on (<condition1> and <condition2>)OR (<condition3> and <condition4>)Be One with the OptimizerTG |
|
|
|
|
|
|
Sql SELECT * + derived folumns