"IIF TO COMPLEX" PROBLEM 1

[fsweb2002]

Hi, I have an SQL query with what appears to be a very long IIF formula (see below).
I have tried to take half the conditions out and it works fine, so I assume is because it´s too long.

Is there a way of achieving the same thing, doing it some other way, or am I stuck with deleting some of the conditions?

Your help is appreciated.

sqlline = sqlline & "IIf([Pick Up Costs].Kms>200,((0.86*2)*[Pick Up Costs].Kms),"
sqlline = sqlline & &quot;IIf([PU Costs].Kms<11,'114,49',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<21,'122,91',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<31,'134,33',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<41,'143,64',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<51,'153,56',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<61,'163,48',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<71,'173,39',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<81,'183,31',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<91,'192,62',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<101,'202,08',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<121,'182,35',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<141,'202,80',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<161,'227,19',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<181,'254,55',&quot;
sqlline = sqlline & &quot;IIf([PU costs].Kms<201,'281,90',''))))))))))))))) as txtrate,&quot;
sqlline = sqlline & &quot; [Pick Up Costs].PostalCode, [Pick Up Costs].ProvCode&quot;
sqlline = sqlline & &quot; FROM [Pick Up Costs] &quot;

 

[ClydeDoggie]

You could split it into two querys, using a Where clause to limit one to <= 101 and the other to >101, then use a Union query to return your results. Another choice would be to create a module that uses a select case construct to put the records in a temp table, then using the temp table as your results. That is the method I would use.

 

[Steve101]

Create a user defined function in a Global module, and then use this in your sql:

For example:

Function DeterminePUCosts(PUCostsKms)
Select Case PUCostsKms
Case Is < 11
CaseVal = &quot;114,49&quot;
Case Is < 21
CaseVal = &quot;122,91&quot;
Case Is < 31
CaseVal = &quot;134,33&quot;
Case Is < 41
CaseVal = &quot;143,64&quot;
'
'...add other statements here
'
Case Is < 121
CaseVal = &quot;202.08&quot;
Case Else
CaseVal = &quot;???&quot;
End Select
DeterminPUCosts = CaseVal
End Function


Your current sql lines which look like this:

sqlline = sqlline & &quot;IIf([PU Costs].Kms<11,'114,49',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<21,'122,91',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<31,'134,33',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<41,'143,64',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<51,'153,56',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<61,'163,48',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<71,'173,39',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<81,'183,31',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<91,'192,62',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<101,'202,08',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<121,'182,35',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<141,'202,80',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<161,'227,19',&quot;
sqlline = sqlline & &quot;IIf([PU Costs].Kms<181,'254,55',&quot;
sqlline = sqlline & &quot;IIf([PU costs].Kms<201,'281,90',''))))))))))))))) as

Could then be replaced by the single line:

sqlline = sqlline & &quot;DeterminePUCosts([PU Costs].Kms) AS &quot;

I may have some of the detail slightly wrong, but the ideas is to use a global function, which then allows you the full disposal of the program language.

Hope you get the drift,

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[fsweb2002]

Thanks all for your replies.

Steve101, what a GREAT idea !!!! Never thought you could that !!!!

Thank you so much! You not only solved this problem, but many others I had problems with within SQL !!!!

Thank you
Thank you
Thank you
Thank you

 

[Steve101]

Thanks for the thanks. Glad to have helped,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au