Access(2000) =IIF to T-SQL(2005) [RESOLVED]

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

Other Forums

MS Access

Access(2000) =IIF to T-SQL(2005) [RESOLVED]

Author

Topic

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2008-11-04 : 05:58:31

Hello Experts,

I am trying to convert the following to t-sql


=IIf([Selling Price]=0,0,([Selling Price]-Nz([Cost]))/[Selling Price])

So far I have;


CASE p.[Selling Price]
      WHEN 0
      THEN (p.[Selling Price] - ISNULL(p.[Cost], 0)) / (p.[Selling Price])
    END

I have converted both selling price and cost to floats but im still getting a divide by zero error. Im pretty sure im not doing this correctly at all. Can someone help me convert that inline if statement to something that I can understand in tsql?

Thanks,
Pace

"Impossible is Nothing"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-04 : 06:07:56

[code]
CASE p.[Selling Price]
WHEN 0
THEN 0
ELSE
(p.[Selling Price] - ISNULL(p.[Cost], 0)) / (p.[Selling Price])
END
[/code]

Madhivanan

Failing to plan is Planning to fail

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2008-11-04 : 06:11:39

Gah! So close.

Many thanks

"Impossible is Nothing"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-04 : 06:16:13

or

p.[Selling Price] - ISNULL(p.[Cost], 0)) / ISNULL(NULLIF((p.[Selling Price]),0),0)

Madhivanan

Failing to plan is Planning to fail

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2008-11-04 : 06:20:44

NULLIF! Nice, new one for me today.

Thanks again :)

"Impossible is Nothing"

genius_palli
Starting Member

42 Posts

Posted - 2008-11-28 : 01:12:48

Hi everyone..
I am having a Access Query where the following piece of code is used.
iif([fieldname],"Hello",[fieldname]) as [somename]

in this iif , there is no condition. How can we write this piece of code in SQL.
if no condition is specified does it mean true or false.
Plz help .. thanks in advance..

Every experience has something to learn. Go get it.
* Thanks and Regards *
genius_palli
Web Developer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-29 : 03:01:30

quote:
Originally posted by genius_palli

Hi everyone..
I am having a Access Query where the following piece of code is used.
iif([fieldname],"Hello",[fieldname]) as [somename]

in this iif , there is no condition. How can we write this piece of code in SQL.
if no condition is specified does it mean true or false.
Plz help .. thanks in advance..

Every experience has something to learn. Go get it.
* Thanks and Regards *
genius_palli
Web Developer

You cant use IIF if there is no condition

Madhivanan

Failing to plan is Planning to fail

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-29 : 12:13:51

quote:
You cant use IIF if there is no condition

I think Access works like VB - it will evaluate a numeric value of zero as False and all other numerics as True and for string values blank is False and anything else is True, so you can have expressions without conditions.
However, you should change it to be an explicit condition even when the language doesn't require it, so for example, if the value is numeric rather code the expression as x <> 0 instead of just x.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources