if null cfset

Value NULL is undefined

or

String Index out of range 0

Doesn't work for this example... Should I set it in the query?

Hi sl1nger,
does my example works or it fails too...?
Regards,
---Pinal

if PinalDave's doesn't work it may be better to post your full query and code so we have a better idea where to help fix it ;o)

~trail

pinaldave -

It doesn't return an error.. but when I call #something# later in the output it returns nothing, instead of the text NA.

Hi sl1nger,
we need more info about your code.
Regards,
---Pinal

<cfquery name="GetSomeStuff" datasource="safsf">
      SELECT * FROM tbl_Stuff
</cfquery>

<cfoutput query="GetSomeStuff" group="theID">

<cfif #something# eq 'NULL'>
  <cfset something = "NA">
</cfif>

The value of this field is #something#

</cfoutput>

Also, cfoutput is wrapped in the cfdocument.  I don't think that would effect anything.

Hi sl1nger,

my suggestion should work. it will not work in only condtion when something has some value.
 <cfquery name="GetSomeStuff" datasource="safsf">
      SELECT * FROM tbl_Stuff
 </cfquery>
 
 <cfoutput query="GetSomeStuff" group="theID">
 
  <cfif len(trim(something)) eq 0>
   <cfset something = "NA">
 </cfif>
 
 The value of this field is #something#
 
 </cfoutput>

Regards,
---Pinal

I'm trying the exact syntax and getting this...

The value of this field is

When expecting..

The value of this field is NA

Hi sl1nger,
let us try to debug what is the output here...?
 <cfif len(trim(something)) eq 0>
   <cfset something = "NA">
<cfelse>
    <cfset something = 'error'>
 </cfif>

Regards,
---Pinal

still blank

here this works, I just tested it ;o)


<cfquery name="qry" datasource="testDB">
SELECT TestOne FROM NewTest
</cfquery>

<cfoutput query="qry" group="TestOne">
<cfif TestOne EQ "NULL">
<cfset NewValue = "N/A">
The value of this field is #NewValue#<br>
<cfelse>
The value of this field is #TestOne#<br>
</cfif>
</cfoutput>

I think the problem you were having was one, NULL did not have the "" around it so it thought it was a variable and not a value, and second you're getting mistakes because you're trying to assign the column name a value based on a condition. Notice in my example where I name the value something else like "NewValue" to assign "NULL" the Value of "N/A", this worked without any issues ;o)

~trail

SELECT * FROM tbl_Stuff ORDER BY ID, FirstName, LastName

The query actually has an order by in it...  Would the grouping have an effect.

also just as a note I noticed you had the group attribute on the query output, if you have a list of records like this....

...
12 The value of this field is NULL
13 The value of this field is something
14 The value of this field is NULL
15 The value of this field is something else
16 The value of this field is NULL
17 The value of this field is NULL
18 The value of this field is NULL
19 The value of this field is another value

with the group attribute you'll get this......

12 The value of this field is NULL
13 The value of this field is something
14 The value of this field is NULL
15 The value of this field is something else
16 The value of this field is NULL
19 The value of this field is another value

notice the rows 17 and 18 are missing? this is because it's outputed as a group, just something to keep in mind...

regards,
~trail

you asked... "The query actually has an order by in it...  Would the grouping have an effect."

Answer.. No your output will be fine. ;o)

~trail

Best thing to do is handle this in the query

SELECT COALESCE( tableName, 'some value instead of null' ) AS myColumn
FROM yourTable

If you do not have COALESCE use ISNULL(tableName, 'some value instead of null')