access statement convert to Sql

Question

how can I convert to T-Sql this one?

IIf([ESSValue]<>0,Int([ESSValue]*100),"")

If/else logic in sql is normally done with the case when keywords. — Dan Bracuk
– Dan Bracuk, Commented Nov 1, 2017 at 14:07
This is TSQL. If you are doing operation on integers, you can't return string value. Try null instead. — Kamil Gosciminski
– Kamil Gosciminski, Commented Nov 1, 2017 at 14:07
What type is [ESSValue] ?? Why you convert it to integer?? Why default is "" ... In Sql you cant return two different types for the same field — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 1, 2017 at 14:07

Gordon Linoff · Accepted Answer · 2017-11-01 14:14:28Z

1

I think the following pretty much does what you want:

select coalesce(cast(EssValue * 100 as int), 0)

Here is the thinking. The comparison to zero is unimportant, because 0 times any value is going to be zero. The iif() returns an integer (I think) because the "then" argument is an integer; the empty string gets converted to zero.

I'm not 100% certain about the last statements with regard to MS Access, but that is how iif() works in SQL Server.

I should add. Although I don't approve of iif() for conditional expressions (because case is the standard and more powerful), SQL Server does support it. So you could write:

IIf([ESSValue]<>0, cast([ESSValue]*100 as int), '')

Note: As I mentioned earlier, the '' will be converted to 0.

answered Nov 1, 2017 at 14:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Juan Carlos Oropeza · Accepted Answer · 2017-11-01 14:11:39Z

0

CASE WHEN ESSValue <> 0 
     THEN CAST(ESSValue * 100 AS INT)
     ELSE NULL
END as fieldname

For case expression the default is NULL if doesn't meet any condition, so you dont really need the ELSE condition

edited Nov 1, 2017 at 14:11

answered Nov 1, 2017 at 14:08

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

1 Comment

Gordon Linoff Over a year ago

The original statement never returns NULL.

Collectives™ on Stack Overflow

access statement convert to Sql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related