IS Null in CF

[emerickson]

I can't figure out how to do a CFIF ... Is Null. From what I read on other threads, CF does not do Is Null? I also tried CFIF ... EQ "", Trim(Len(...)), Len(Trim(...)), but nothing works.

Here is part of my query:
SELECT <CFIF qrySumAbsent.Jan IS NULL><CFSET PercJan = &quot;0&quot;><CFELSE>(qrySumAbsent.Jan/qrySumAll.JanA)*100 As PercJan</CFIF>
FROM qrySumAll, qrySumAbsent

qrySumAbsent.Jan can, under certain circumstances, return a null value, so my calculation qrySumAbsent.Jan/qrySumAll.JanA would not work. Any ideas?

Thanks in advance!
Eva Erickson

 

[fusionaire]

Have you tried using <cfif Not IsDefined(&quot;&quot>?

 

[csteinhilber]

I'm a little confused about what you're doing.
Are you trying to affect the query itself? Or was that just pseudo code? Or do you have other queries that you want to affect this one?

As far as I know, you can't use:

SELECT <CFIF qrySumAbsent.Jan IS NULL><CFSET PercJan = &quot;0&quot;><CFELSE>(qrySumAbsent.Jan/qrySumAll.JanA)*100 As PercJan</CFIF>
FROM qrySumAll, qrySumAbsent

in an actual CFQUERY... because ColdFusion simply sends the contents between the query tags as a string to the database SQL engine... meaning, at the moment that the SELECT is processed, qrySumAbsent.Jan doesn't exist (in fact, in ColdFusion's mind, qrySumAbsent.Jan is never going to exist because it really doesn't know anything about the individual tables you're running the query on).

So what you actually need is for SQL itself to test the value of the fields. The exact syntax you use will depend greatly on the type of database you have. SQL Server, for example, I believe supports CASE statements... so you could use:

SELECT
    CASE
      WHEN qrySumAbsent.Jan = NULL THEN '0' AS PercJan
      ELSE (qrySumAbsent.Jan/qrySumAll.JanA)*100 As PercJan    
     END
        :

or something like that.
Oracle SQL would look completely different.

But regardless of the database you use, it sounds like you want to perform the expression statement in the SQL, rather than in CFML.



-Carl

 

[r937]

the problem with this --

[tt]SELECT
<CFIF qrySumAbsent.Jan IS NULL>
<CFSET PercJan = &quot;0&quot;>
<CFELSE>(qrySumAbsent.Jan/qrySumAll.JanA)
*100 As PercJan
</CFIF>
FROM qrySumAll, qrySumAbsent [/tt]

is that if the CFIF evaluates true, the CFSET runs, but nothing is generated into the query's SELECT list, with the result that the query looks like this --

[tt]SELECT
FROM qrySumAll, qrySumAbsent [/tt]

which will generate an error

but Carl has a good point, the whole thing is confusing (or badly taken out of context)

if you indeed have the results of one query affecting the running of another, you should look into combining the queries into one, so that there's only one call to the database


rudy

 

[emerickson]

Thanks guys for trying to help! fusionaire: your idea didn't work, I got the &quot;Can't find symbol: -1.#IND00&quot; error again. Rudy, your answer makes sense, but how can I fix it? How can I make it generate the results for the SELECT list? And Carl, sorry I didn't make sense. So let me try again. I have 2 queries (qrySumAbsent and qrySumAll), and I can't combine them into just 1 query. That's why I have that third query (qryData), which is what I showed you in my original post. qryData is based on the first 2. My CFOUTPUT then is based on qryData. The problem is that qrySumAbsent might return no results, and in that case I'm getting the &quot;Can't find symbol: -1.#IND00&quot; error. If both qrySumAbsent and qrySumAll return results, qryData is running fine. Does that make any more sense?
Thanks!
Eva Erickson

 

[csteinhilber]

Ahhhh... okay.

So it sounds like you're on the right track, you just want the CFIF block to resolve into a SQL statement rather than a CFSET:

<CFQUERY name=&quot;qryData&quot; ...>
  SELECT
    <CFIF qrySumAbsent.recordCount GT 0 AND qrySumAll.recordCount GT 0>
      (qrySumAbsent.Jan/qrySumAll.JanA)*100 As PercJan
    <CFELSE>
      0 as PercJan
    </CFIF>
  FROM qrySumAll, qrySumAbsent
</CFQUERY>

-Carl