Fields that appear to be Null, but are not

[ljevans]

I am not getting the results that I expect from the following code. There are several records where the sub_title field appears to be null but show up in the result set for a query that is testing for the sub_title field not null. Any idea why this is happening?

<cfquery datasource=&quot;FSI-Library&quot; name=&quot;keyword&quot;>
	SELECT sub_title
	FROM documentation_list
	WHERE doc_pkey BETWEEN 25000 and 30001 
</cfquery>

<CFIF #keyword.sub_title# IS &quot;&quot;>
	<cfquery datasource=&quot;FSI-Library&quot; name=&quot;no_sub_title&quot;>
		SELECT (sim_name + ' - ' + title) As doc_title, document_number, sub_title, doc_pkey
		FROM documentation_list
		WHERE doc_pkey BETWEEN 25000 and 30001 
	</cfquery>
	
	<cfoutput query=&quot;no_sub_title&quot;>
		#doc_title# - #document_number# - #doc_pkey#<br>
	</cfoutput>
<cfelse>
	<cfquery datasource=&quot;FSI-Library&quot; name=&quot;test&quot;>
		SELECT (sim_name + ' - ' + title + ' - ' + sub_title) As doc_title, document_number, sub_title, doc_pkey
		FROM documentation_list
		WHERE doc_pkey BETWEEN 25000 and 30001 
	</cfquery>
	
	<cfoutput query=&quot;test&quot;>
		#doc_title# - #document_number# - #doc_pkey#<br>
	</cfoutput>
</cfif>

 

[sagn]

I'm sorry but I do not see where you are testing
for the sub_title field as not null.

I only see tests for the doc_pkey.

ds

 

[ljevans]

I'm sorry I meant that I was testing for sub_title as being empty.

<CFIF #keyword.sub_title# IS &quot;&quot;>

 

[foxdev]

<CFIF #keyword.sub_title# IS &quot;&quot;>[/blue]

I'm not a ColdFusion developer, so I may be all wet in regards to how CF works, but in SQL Server (this forum) &quot;&quot; is not the same thing as NULL.

Consider the following T-SQL script:

[tt]declare @test char(3)
select @test = ''
if @test is null
print 'null'
else print 'not null'[/tt]

The result will be not null. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com