Select order, (if then?),date from orders ? ? ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select order, (if then?),date from orders ? ? ?

Author

Topic

mentalee
Starting Member

6 Posts

Posted - 2005-05-17 : 20:30:17

trying to figure out how to select variable data on the fly. for instance if I am selecting data out of an "orders" table and want an added flag to simply state (before sale / after sale) within the same select, here's an example that I will use supposing that it were the correct syntax (which it is not) that I would like to use to come up with the needed results:

Query-

declare @saledate char(8)
set @saledate = '20050510

select order,total,date,
case date begin
when date < @saledate then 'Before Sale' else 'After Sale'
End
from orders

Desired Results-

ORD001 23.58 20050501 Before Sale
ORD002 32.01 20050509 Before Sale
ORD003 98.76 20050510 After Sale
ORD004 128.99 20050512 After Sale

... does anyone know the proper syntax for accomplishing this?

Thanks very much in advance~!

Michael

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-17 : 20:40:08

[code]
declare @saledate char(8)
set @saledate = '20050510'

select
[order],
total,
[date],
case
when [date] < @saledate
then 'Before Sale'
else 'After Sale'
End AS newcolumn
from
orders
[/code]

You shouldn't be reserving key words for column names either. (order, date, etc)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mentalee
Starting Member

6 Posts

Posted - 2005-05-17 : 20:51:17

I tried your suggestion with the case syntax... I thought it would work too, but gives me an error before the '<'... I think that you are not able to do comparisons in a case, right? I shortened the actual column names for simplicity sake, they are really Ordernumber, Orderdate, etc.

Any other ideas?

Thanks

mentalee
Starting Member

6 Posts

Posted - 2005-05-17 : 20:54:16

WOW, what a subtle difference but I caught it!!!

Your case statement is "Case when date... "
and mine had "Case date when date...."

I have never had a problem with casing the column name BEFORE the "when" except that I have never been able to run any arithmatic on them. Your example was exactly what I needed to give me the right results.

THANKS!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources