case statement problem - SQL Server Forums

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

SQL Server Development (2000)

case statement problem

Author

Topic

s2ymon
Starting Member

2 Posts

Posted - 2010-06-07 : 12:27:27

Hi

got a case statement and asically for each value of a field i want to retrieve a different value from different field. In first table i've got all stock of my products with useby dates, in second table i've got all products information, some products have 4 days life on it some have 5 days life on it so what i want to retrieve is the min useby date for each product that can be still used so when product has 4 days life on it set up i want to retrieve the useby date of getdate()+4 but it doesn't like that after then statement, fields and syntax is correct cos im using it withouth case statment but seems like case can only accept text as a results. is there a way for me to retrieve what i need. Thanks in advance

case when mm.usr_minlife='4' then mio.usr_LatestUse >= getdate()+4
when mm.usr_minlife='5' then mio.usr_LatestUse >= getdate()+5
else
''
end as [minlife]

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-07 : 23:30:24

The syntax of adding +<No> days is supported in oracle but not in MS SQL Server.
For adding four days to Current date you need to use GetDate function.

Here is an example (AdventureWorks DB) on how to use case and add dates..

Select employeeId, HireDate, Case EmployeeId%3 When 0 then Dateadd(DD,-300,Getdate())
When 1 then Dateadd(DD,-500,Getdate())
When 2 then Dateadd(DD,-1500,Getdate())
End AS DummyDate
from HumanResources.Employee

Correct me if my understanding about your requirement is wrong.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

s2ymon
Starting Member

2 Posts

Posted - 2010-06-08 : 02:34:25

the problem i've got is that mm.usr_minlife is a number type cell with only 1 digit in it, and mio.usr_latestuse is a datetime cell and i have problem assigning results to a variable. when i replace:
when mm.usr_minlife='4' then mio.usr_LatestUse >= getdate()+4
with
when mm.usr_minlife='4' then getdate()+4
then it tries to put results into mm.usr_minlife and can't convert datetime into number. the original line however throws an error on >= mark
when mm.usr_minlife='4' then mio.usr_LatestUse >= getdate()+4
saying Incorrect syntax near '>' it looks like a can't assign results to a variable using case. maybe i could replace that with IF or something similiar?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-06-08 : 03:00:52

[code]case when convert(varchar,mm.usr_minlife='4') then convert(varchar,getdate()+4)
when convert(varchar,mm.usr_minlife)='5' then convert(varchar,getdate()+5 )
else
''
end as [minlife][/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 05:07:44

quote:

Originally posted by sakets_2000

case when convert(varchar,mm.usr_minlife='4') then convert(varchar,getdate()+4)
when convert(varchar,mm.usr_minlife)='5' then convert(varchar,getdate()+5 )
else
''
end as [minlife]

1 convert(varchar,mm.usr_minlife='4') should be convert(varchar(100),mm.usr_minlife)='4')
2 When converting to character datatypes, always specify the length
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources