if null cfset

Simple question....

I have some nulls returning from a query and I'd like to set the values equal to something.  I've tried it a few different ways.

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

...with this I get an error


<cfif Not IsDefined(#something#)>
  <cfset something = "NA">
</cfif>

...with this I get an error
sl1ngerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sl1ngerAuthor Commented:
Value NULL is undefined

or

String Index out of range 0
pinaldaveCommented:
Hi sl1nger,
 
 <cfif len(trim(something)) eq 0>
   <cfset something = "NA">
 </cfif>


Regards,
---Pinal
trailblazzyr55Commented:
it's looking at null as a variable because you don't have quotes around it...

try this...

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

sl1ngerAuthor Commented:
Doesn't work for this example... Should I set it in the query?
pinaldaveCommented:
Hi sl1nger,
does my example works or it fails too...?
Regards,
---Pinal
trailblazzyr55Commented:
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
sl1ngerAuthor Commented:
pinaldave -

It doesn't return an error.. but when I call #something# later in the output it returns nothing, instead of the text NA.
pinaldaveCommented:
Hi sl1nger,
we need more info about your code.
Regards,
---Pinal
sl1ngerAuthor Commented:
<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>
sl1ngerAuthor Commented:
Also, cfoutput is wrapped in the cfdocument.  I don't think that would effect anything.
pinaldaveCommented:
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
sl1ngerAuthor Commented:
I'm trying the exact syntax and getting this...

The value of this field is

When expecting..

The value of this field is NA
pinaldaveCommented:
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
sl1ngerAuthor Commented:
still blank
trailblazzyr55Commented:
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>
trailblazzyr55Commented:
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
sl1ngerAuthor Commented:
SELECT * FROM tbl_Stuff ORDER BY ID, FirstName, LastName

The query actually has an order by in it...  Would the grouping have an effect.
trailblazzyr55Commented:
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
trailblazzyr55Commented:
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
Tacobell777Commented:
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')
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.