change complex query from IIf statements

[qwertyjjj]

I had been using some SQL in Access with many IIf statements. I understand the equivalent in SQL is CASE, WHEN, END.
However, I'm really stuck with the following:
oh..ISNULL used to be Nz as well

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

Do I have to put in some seriously heavyily nested CASE statements or are there some other ways round this?

 

[boblarson]

It would drive me completely crazy to try to decipher your current code, but I can give you a hint for something to look at -

Check out the Switch function.

 

[qwertyjjj]

I know, it's a complicated calculation but I'm not sure I can do much about it.
The problem with SWITCH is that it only evaluates an expression to TRUE and returns a value whereas IF (or IIf) will return a value if TRUE, otherwise it will run the FALSE part of the statement and continue.

The calculation is split into 4 parts working out debtor days for the current month DD, previous month, DD-1, etc.
So, for one month, the first calculation up to the + sign is:
IF InvoicesRaised = 0 then 0 ELSE do the calculation.
Then it checks whether the total debt is not equal to 0 and whether the subsequent calculation is not equal to 0.
If so, it works through the calculation.


IIf
(ISNULL(ZI.InvoicesRaised,0)=0, 0,
IIf
(IIf
(ISNULL (ZA.TotalDebt,0)<>0,
IIf
(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)>0,30,(ISNULL(ZA.TotalDebt,0)*30)/ISNULL(ZI.InvoicesRaised,0)),0)>0,
IIf
(ISNULL(ZA.To talDebt,0)<>0,
IIf
(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)>0,30,(ISNULL(ZA.TotalDebt,0)*30)/ISNULL(ZI.InvoicesRaised,0)),0
),0
)
)

 

[Moniker]

OMG, separate the pieces out into individual variables. That's perhaps the most unmaintainable piece of code I've seen.

I realize that you're trying to calculate on the fly, which is good, but you don't do it all in one step. What you want is a series of calculations where one builds off the other, but not all at the same time. That is a maintenance (and readability) nightmare.

 

[qwertyjjj]

In SQL server ? Fair enough.

I dudn't think you could use variables in Access SQL though ?

 

[Moniker]

Sure you can. Access SQL supports function calls, variables, etc. This would work:

SELECT number1, number2, (number1+number2) AS SumNumbers FROM TableName;

 

[qwertyjjj]

Sure you can. Access SQL supports function calls, variables, etc. This would work:

SELECT number1, number2, (number1+number2) AS SumNumbers FROM TableName;

...but where in the SQL do you state what the variable is?
e.g. in SQL server you do
DECLARE @num1 int
SET @num1 = 10+45

SELECT @num1

Not sure how to do this in Access SQL?

 

[Moniker]

In my example:

SELECT number1, number2, (number1+number2) AS SumNumbers FROM TableName;

The piece in bold is setting the variable "SumNumbers" to be "number1+number2".

In the QBE grid in Access, you just type it in to the grid as a new field, like this:

SumNumbers: [numbers1]+[numbers2]

Access will translate that into the SQL like what I provided as an example. Note that if you don't provide a field name, Access will add it's own name. So, if in a new field in the QBE, you just type in this:

=[numbers1]+[numbers2]

Access will translate that to:

Expr1: [numbers1]+[numbers2]

And the resulting SQL would change to:

... (number1+number2) AS Expr1 ...

 

[qwertyjjj]

I'm still lost on this part.
I see how you're using variables in your example but your're still typing in the number/calculation before it number1, number 2. You are using an alias rather than a variable aren't you?

My calculation is this:
IIf
(ISNULL(ZI.InvoicesRaised,0)=0, 0,
IIf
(IIf
(ISNULL (ZA.TotalDebt,0)<>0,
IIf
(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)>0,30,(ISNULL(ZA.TotalD ebt,0)*30)/ISNULL(ZI.InvoicesRaised,0)),0)>0,
IIf
(ISNULL(ZA.To talDebt,0)<>0,
IIf
(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)>0,30,(ISNULL(ZA.TotalD ebt,0)*30)/ISNULL(ZI.InvoicesRaised,0)),0
),0
)
)

I would like to get the result of that into a variable but I'm going to have to write out the code somewhere along the line so I don't see how I can make it more maintainable without using SQL server variables?

ANy ideas? Or even getting that calculation into a function would be good but it relies on all the data I pull back from the tables on the fly...