Nullif - using in Coldfusion

Question

Looking for a little help with this code. Without posting the entire file which is way to big I just need a little help with using Nullif in Coldfusion.

I could I guess use it in my SQL statment, but for the sake of learning I am wondering if it can be used when setting variables as follows :-

The code doesn't throw any errors but I'd like to know where I would place the 0 after the Nullif.

<cfif AE_C NEQ 0>
    <cfset AE_P=AE_T/AE_C>
    <cfset AE_A=AE/AE_C*100>
    <cfset AE_B = AE-AE_C/H8*H9>
    <cfset AE_D=AE/H9*H8> 
<cfelse>
    <cfset AE_P=ISNULL(AE_T/NULLIF(AE_C))>
    <cfset AE_A=ISNULL(AE/NULLIF(AE_C*100))>
    <cfset AE_B=ISNULL(AE-AE_C/NULLIF(H8*H9))>
    <cfset AE_D=ISNULL(AE/NULLIF(H9*H8))>
</cfif>

Hoping it can be done this way.

You are going to get an error if you are set `0` for `null`, because division by 0 will be an error. — rrk, Aug 03 '18 at 11:31
If you can provide an outline of the output you are looking for, that will be great. — rrk, Aug 03 '18 at 11:37
The code above is just one set of 20 odd. We need the calculations to show 0.00 when we are dividing say zero by 4 without the exception being thrown. At the moment I am receiving a calculation of 1 for each of those values that are obviously being counted as 1. I found a piece of code written by Ben Nadel regarding dealing with Nullif() which when used in the SQL statement sets all values correctly so that you don't receive the error. I just can't work out how to implement it. I'll post it below. Maybe it is the answer ? — CarolaV, Aug 04 '18 at 02:13
`0/1` is valid math. So is `42/null`. It's when you try to make the bottom number a 0 (`1/0`) that will throw the error. I think the issue you are running into is with implicit type casting. `9/2` will return `5`, but `9.0/2` will return `4.5`. You have to make the first number in each operation a decimal to get the rest to also coerce to a decimal. — Shawn, Aug 07 '18 at 14:05

rrk · Answer 1 · 2018-08-03T12:06:17.663

1

IMPORTANT: Your code is not showing any error because ISNULL is masking the error.

Also NULLIF is not a valid ColdFusion function. I believe the reason why there is no error in your page because, ColdFusion ISNULL() function seems to be a very versatile one and showing some undocumented characteristics. ISNULL() does not return an error even if the expression inside it is defined or not if the expression is syntactically valid.

eg.

ISNULL(AE_T/NULLIF(AE_C)) // No error because AE_T/NULLIF(AE_C) is a valid statement.

What you could do as an alternative is the following.

The following is a bit hacky, but you can check out the function val(). It will return 0 for any string that is not a number (check the doc for more details).

NULLIF(AE_C) becomes val(AE_C).

<cfset AE_P=ISNULL(AE_T/val(AE_C))>

Still if the val() return 0, then the output of ISNULL() will be YES, because division by 0 throws error.

edited Aug 03 '18 at 12:06

answered Aug 03 '18 at 11:56

rrk

15,677
4
29
45

1

I never realized that `ISNULL()` would actually mask an error. `writeOutput(ISNULL(1/0))` writes `YES`. :-/ I'm lucky I didn't just tear a hole in the fabric of the universe by "successfully completing" a `div0` operation. – Shawn Aug 03 '18 at 15:44
1

@Shawn Indeed. ColdFusion works in mysterious ways. LOL – rrk Aug 03 '18 at 18:07
Considering that CF 2018 JUST added real `NULL` support, I'm not really sure why they had an `ISNULL()` anyway. :-) – Shawn Aug 03 '18 at 18:20

score 1 · Answer 2 · answered Aug 04 '18 at 02:15

1

This is some code that was written by Ben Nadel that I've found to resolve the error, perhaps someone can assist me in how I would implement it as I just can't get my head around it.

<!---
Do SQL division with divide-by-zero protection. But this,
time, let's provide a default value if the division is
not valid.
--->
<cfquery name="qDivision" datasource="#REQUEST.DSN.Source#">
SELECT
    (
        ISNULL(
            (45 / NULLIF( 0, 0 )),
            0
        )
    ) AS value
  ;
</cfquery>

<!--- Output resulting value. --->
[ #qDivision.value# ]

answered Aug 04 '18 at 02:15

CarolaV

41
3

Well, in that case CF alternative for `NULLIF` would be to use something like this. `Compare(a, b) EQ 0 ? '' : a` – rrk Aug 04 '18 at 04:50
What Ben's code here is essentially doing is saying that if `0` (or your input) equals `0` then return a `NULL`. Which results in `45/NULL`. Which is `NULL` (since you can't do math on a `NULL` value). Then the `ISNULL(45/NULL)` will return the second value, which is `0`. Again, though, I don't think you need to go through all of your second operations if you already know that your divisor is going to be `0`. – Shawn Aug 07 '18 at 14:10

Shawn · Answer 3 · 2018-08-03T15:25:22.500

This is more a comment than an answer. See RRK's answer about NULLIF not being a valid ColdFusion function and ISNULL() hiding an error.

My comment is more about the logic of your operation. You don't need to do some of the steps in your <CFELSE>

Original:

<cfif AE_C NEQ 0>
    <cfset AE_P=AE_T/AE_C>
    <cfset AE_A=AE/AE_C*100>
    <cfset AE_B = AE-AE_C/H8*H9>
    <cfset AE_D=AE/H9*H8> 
<cfelse>
    <cfset AE_P=ISNULL(AE_T/NULLIF(AE_C))>
    <cfset AE_A=ISNULL(AE/NULLIF(AE_C*100))>
    <cfset AE_B=ISNULL(AE-AE_C/NULLIF(H8*H9))>
    <cfset AE_D=ISNULL(AE/NULLIF(H9*H8))>
</cfif>

I'm assuming you're using the NULLIF() to prevent divide by zero error.

First, in the languages I'm familiar with, NULLIF() requires two arguments: NULLIF(a,b) so that if a is not equal to b, it will return a, otherwise it will return NULL.

That said, your cfif says that if AE_C is not 0 then do x, else do y. I'm assuming it should be <cfset AE_P=ISNULL(AE_T/NULLIF(AE_C,0))>. If you extrapolate that out, you'd get AE_T/NULLIF(0,0) == AE_T/NULL == NULL. So you can just short-circuit that block and just set AE_P=NULL. But you also have the ISNULL() function around that, so that will always be true also. And ISNULL() (except in ColdFusion) also takes 2 arguments. In CF, it returns TRUE/FALSE (or YES/NO to be pedantic). So now you've essentially done <cfset AE_P = YES/TRUE>. I'm guessing that wasn't your original intent. So ISNULL() probably isn't appropriate here. In SQL, ISNULL(a,b) would mean that if a is NULL, return b. So (in SQL) you could essentially do AE_P = ISNULL(x,0) and that would mean that if AE_C was 0, rather than getting the divide by zero error, you could just set AE_P = 0.

TLDR; There isn't a NULLIF() function in ColdFusion, so if you're trying to prevent the div0 errors, you'll have to either do it in your SQL or modify your ColdFusion sets:

<cfif AE_C NEQ 0>
    <cfset AE_P=AE_T/AE_C>
    <cfset AE_A=AE/AE_C*100>
<cfelse>
    <cfset AE_P=0>
    <cfset AE_A=0>
</cfif>

I don't know the flow of your code, so I don't know how H8 and H9 are affected by AE_C, so you may have to check those for 0 also.

But, I'd still go back to my usual belief that if these calculations are used in SQL and not really in code, then they should be done in SQL rather than being passed out to the app server and then back to the SQL. Again, though, I don't know what your code is doing, so it may be more appropriate to have those calcs in code.

https://trycf.com/gist/10c7c2acf73aa65b8509461702459c99/acf?theme=monokai That shows how you end up with `YES/NO` as some of your end values. — Shawn, Aug 03 '18 at 15:29
Initially I tried the simple cfset AE_P=0 approach but this just automatically sets everything to zero. For instance I have a value in the database of 0.00 and then another value of 5.48. If I subtract 0.00 from 5.48 then my result is 5.48 which is what I need to be shown, but by setting it this way it simply shows 0.00 — CarolaV, Aug 04 '18 at 02:27
What is math that you're doing to get `5.48`? Also, what do you mean that "`AE_P=0` ... sets everything to zero"? — Shawn, Aug 07 '18 at 14:15

Nullif - using in Coldfusion

3 Answers3