transform Access iif statement to SQL

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)

transform Access iif statement to SQL

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-11-26 : 10:49:59

I need to change this to SQL server code.
SHould I use a CASE statement? Seems like it mike get complicated.
I can't break it up into variables as it is a live calculation from the retrieved data...

IIf(Nz(ZI.InvoicesRaised,0)<=0,0,IIf(IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)>0,30,(Nz(ZA.TotalDebt,0)*30)/Nz(ZI.InvoicesRaised,0)),0)>0,IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)>0,30,(Nz(ZA.TotalDebt,0)*30)/Nz(ZI.InvoicesRaised,0)),0),0))
+IIf(Nz(ZIMinus1.InvoicesRaised,0)<=0,0,IIf(IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0))*30)/Nz(ZIMinus1.InvoicesRaised,0))),0)>0,IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0))*30)/Nz(ZIMinus1.InvoicesRaised,0))),0),0))
+IIf(Nz(ZIMinus2.InvoicesRaised,0)<=0,0,IIf(IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0))*30)/(Nz(ZIMinus2.InvoicesRaised,0)))),0)>0,IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0))*30)/(Nz(ZIMinus2.InvoicesRaised,0)))),0),0))
+IIf(Nz(ZIMinus3.InvoicesRaised,0)<=0,0,IIf(IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)-Nz(ZIMinus3.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0))*30)/(Nz(ZIMinus3.InvoicesRaised,0)))),0)>0,IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)-Nz(ZIMinus3.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0))*30)/(Nz(ZIMinus3.InvoicesRaised,0)))),0),0))
+IIf(Nz(ZIMinus4.InvoicesRaised,0)<=0,0,IIf(IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)-Nz(ZIMinus3.InvoicesRaised,0)-Nz(ZIMinus4.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)-Nz(ZIMinus3.InvoicesRaised,0))*30)/(Nz(ZIMinus4.InvoicesRaised,0)))),0)>0,IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)-Nz(ZIMinus3.InvoicesRaised,0)-Nz(ZIMinus4.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)-Nz(ZIMinus3.InvoicesRaised,0))*30)/(Nz(ZIMinus4.InvoicesRaised,0)))),0),0)) AS DD,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-26 : 15:50:20

quote:
Originally posted by qwertyjjj

I need to change this to SQL server code.
SHould I use a CASE statement? Seems like it mike get complicated.
I can't break it up into variables as it is a live calculation from the retrieved data...

IIf(Nz(ZI.InvoicesRaised,0)<=0,0,IIf(IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)>0,30,(Nz(ZA.TotalDebt,0)*30)/Nz(ZI.InvoicesRaised,0)),0)>0,IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)>0,30,(Nz(ZA.TotalDebt,0)*30)/Nz(ZI.InvoicesRaised,0)),0),0))
+IIf(Nz(ZIMinus1.InvoicesRaised,0)<=0,0,IIf(IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0))*30)/Nz(ZIMinus1.InvoicesRaised,0))),0)>0,IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0))*30)/Nz(ZIMinus1.InvoicesRaised,0))),0),0))
+IIf(Nz(ZIMinus2.InvoicesRaised,0)<=0,0,IIf(IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0))*30)/(Nz(ZIMinus2.InvoicesRaised,0)))),0)>0,IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0))*30)/(Nz(ZIMinus2.InvoicesRaised,0)))),0),0))
+IIf(Nz(ZIMinus3.InvoicesRaised,0)<=0,0,IIf(IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)-Nz(ZIMinus3.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0))*30)/(Nz(ZIMinus3.InvoicesRaised,0)))),0)>0,IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)-Nz(ZIMinus3.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0))*30)/(Nz(ZIMinus3.InvoicesRaised,0)))),0),0))
+IIf(Nz(ZIMinus4.InvoicesRaised,0)<=0,0,IIf(IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)-Nz(ZIMinus3.InvoicesRaised,0)-Nz(ZIMinus4.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)-Nz(ZIMinus3.InvoicesRaised,0))*30)/(Nz(ZIMinus4.InvoicesRaised,0)))),0)>0,IIf(Nz(ZA.TotalDebt,0)<>0,IIf(Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)-Nz(ZIMinus3.InvoicesRaised,0)-Nz(ZIMinus4.InvoicesRaised,0)>0,30,(((Nz(ZA.TotalDebt,0)-Nz(ZI.InvoicesRaised,0)-Nz(ZIMinus1.InvoicesRaised,0)-Nz(ZIMinus2.InvoicesRaised,0)-Nz(ZIMinus3.InvoicesRaised,0))*30)/(Nz(ZIMinus4.InvoicesRaised,0)))),0),0)) AS DD,

Not at all...A case statement would be just as simple and straight forward as your access code

Be One with the Optimizer
TG

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-11-26 : 16:03:47

won't the statement above have to have nested case statements though? Is that even possible?
If you look at the 1st IIf it has 4 nested IIf statements in it!
Just looks messy...

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-26 : 16:04:55

I can tell you one thing, it won't look near as bad as that IIF statement...

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-11-26 : 16:14:45

ARe nested case statements possible?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-26 : 16:21:52

>>ARe nested case statements possible?
Yes - and a simple test would prove it. Did you try it?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-26 : 16:26:19

Didn't mean to be a..."smarty-pants"...this is what I mean by a simple test:


select	case 
		when 1=2 then 'false'
		else case
			when 2=1 then 'false'
			else 'true'
			end
		end as [nestedCaseTest]

output:
nestedCaseTest 
-------------- 
true

Be One with the Optimizer
TG

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-11-26 : 16:28:03

Can't at the moment as don't have the SQL tools in front of me :)

CASE
WHEN ZI.InvoicesRaised,0)<=0 THEN 0
ELSE WHEN ZA.TotalDebt,0)<>0 THEN
ELSE
END
?

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-11-29 : 08:23:01

This bit of code sometimes comes up as NULL and I cannot see why as everywere has ISNULL round it.
Have I missed an ELSE somewhere?

CASE
WHEN ISNULL(ZI.InvoicesRaised,0) <=0
THEN 0
ELSE CASE
WHEN ISNULL(ZA.TotalDebt,0) <> 0
THEN CASE
WHEN (ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0)) > 0
THEN 30
ELSE ((ISNULL(ZA.TotalDebt,0) * 30) / ISNULL(ZI.InvoicesRaised,0))
END
END
END

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-29 : 08:28:32

Is that your whole statement?

Please post the whole statement and the error.

I would say that there is a possibility you will get a divide by zero error looking at the above.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-29 : 09:28:28

In your snippit above two conditions aren't handled properly:
1 - when za.totaldebt = 0 --has no case handler
2 - when zi.invoicesRaised is null --as RickD says: results in devide by 0 error

btw some formatting helps to ~~analize~~ analyze. use CODE tags (see faq)


CASE 
 WHEN ISNULL(ZI.InvoicesRaised,0) <=0 
 THEN 0
 ELSE 
	CASE
	 WHEN ISNULL(ZA.TotalDebt,0) <> 0 THEN 
		CASE
		 WHEN (ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0)) > 0 THEN 30
		 ELSE ((ISNULL(ZA.TotalDebt,0) * 30) / ISNULL(ZI.InvoicesRaised,0))
		END
	END
END

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources