Converting Rrom Access To Sql Syntax

Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..

Here is the part that I need to convert:

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

IIf(Mid([proj_name],1,9)='9900-2787','Sales',

IIf(Mid([proj_name],1,9)='9910-2799','Sales',

IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)

)

)

)

) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

from................

how can you convert it to sql syntax

I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):

select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End

end

Case Statement might be the solution but i could not do it.

Your input will be appreciated

Thank you

you rightly said that CASE Statement is the right choice...

check this....
select ID, FName, LName,

Case

When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')

End as FN,

Case

When SUBSTRING(LastName, 1, 4)= 'Kong' Then Replace(LastNam,'Kong,'KongTest')

End as LN,

Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........

End As Add

From YourTableName Where <Conditions>

Madhu

|||Thank you for you respond. This won't really work for one simple reason.

It will execute each case statement. In other words if you have two people with the last name Kong both of their last names will change to KongTes. Even if you change it to which it will work

Case

When SUBSTRING(FirstName, 1, 4)= 'Mike' Then Replace(FirstName,'Mike','MikeTest')

End as FN,

Case

When SUBSTRING(LastName, 1, 4)= 'Kong' and When SUBSTRING(FirstName, 1, 4)= 'Mike'

Then Replace(LastNam,'Kong,'KongTest')

End as LN,

Case
When SUBSTRING(Address, 1, 4)= '1245') Then ...........

End As Add

From YourTableName Where <Conditions>

But it is not equal to this

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

IIf(Mid([proj_name],1,9)='9900-2787','Sales',

IIf(Mid([proj_name],1,9)='9910-2799','Sales',

IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)

)

)

)

) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

Here is what i am trying to do:

select case

when SUBSTRING(task_name, 1, 3)= 'PTO'

then Replace(task_name,'PTO','PTO_Holiday') and Sum(td_hours) AS SumOfHours (this will give me an error)

when SUBSTRING(task_name, 1, 7)= 'Holiday' and SUBSTRING(task_name, 1, 3)= 'PTO'

then Replace(task_name,'Holiday','PTO_Holiday')and Sum(td_hours) AS SumOfHours (this will give me an error)

ELSE task_name

This does not work i can't have two things happening after "Then"

Do you think i can convertt this to sql Syntax?

thanks