Skip to main content
S
shearak
Inspiring
February 19, 2009
Answered

Outputting a blank field

  • February 19, 2009
  • 2 replies
  • 1289 views
How do you output a table that has blanks as a charter (None, No etc..)? I have table that I am outputting for cfchart, but the blank fields show up blank with no title on the pie graph. I want to somehow substitute the blank with something like (None).

<cfquery name="GetDistrictSum"
datasource="#request.app.DSN#" >
SELECT COUNT(district_name) AS district_sum, district_name
FROM #request.app.DB#.dbo.v_fr_Districts
GROUP BY district_name
</cfquery>

<cfquery name="GetDistrict">
SELECT district_name
FROM #request.app.DB#.dbo.v_fr_Districts
</cfquery>


<cfchart
format="flash"
scalefrom="0"
scaleto="1200000"
font="Times"
fontsize="16"
title="Applications Per District"
pieslicestyle="solid"
chartheight="600"
chartwidth="600"
show3d="yes"
labelformat="number"
backgroundcolor="##EEEEEE">
<cfchartseries
type="pie" query="GetDistrictSum" itemcolumn="district_name" valuecolumn="district_sum"
serieslabel="Districts"
seriescolor="blue">
</cfchartseries>
</cfchart><body bgcolor="#EEEEEE">
This topic has been closed for replies.
Correct answer paross1
Still getting noting for fields with blanks using your method. Everything else is grouped, including the blanks in cfchart, but I get no name for the blank fields as there are none in the database.
OK, here is one way to do it the "hard way"

SELECT COUNT(district_name) AS district_sum,
district_name = CASE
WHEN LEN(LTRIM(district_name))=0 THEN 'None'
ELSE district_name
END
FROM #request.app.DB#.dbo.v_fr_Districts
GROUP BY district_name

or

SELECT COUNT(district_name) AS district_sum,
CASE
WHEN LEN(LTRIM(district_name))=0 THEN 'None'
ELSE district_name
END AS district_name
FROM #request.app.DB#.dbo.v_fr_Districts
GROUP BY district_name


Phil

2 replies

N
Newsgroup_User
Inspiring
February 20, 2009
shearak wrote:
> How do you output a table that has blanks as a charter (None, No etc..)? I have
> table that I am outputting for cfchart, but the blank fields show up blank
> with no title on the pie graph. I want to somehow substitute the blank with
> something like (None).

what db? for sql server you could probably do something like:

SELECT COUNT(district_name) AS district_sum, ISNULL(district_name,'None') AS
district_name
FROM #request.app.DB#.dbo.v_fr_Districts
GROUP BY district_name

S
shearakAuthor
Inspiring
February 20, 2009
I am using SQL server 2005. This would work, but if I had actual Nulls in the field. The field has blanks not NULLS and I wanted to figure a way to say if there is noting in the field say NONE.

SELECT COUNT(district_name) AS district_sum, ISNULL(district_name,'None') AS
district_name
FROM #request.app.DB#.dbo.v_fr_Districts
GROUP BY district_name
P
paross1
Participating Frequently
February 20, 2009
You could use LTRIM and RTRIM to "trim" the blanks from your selected result, so if it is nothing but blanks the result is NULL, and ISNULL would then cause 'None' to be displayed.

SELECT COUNT(district_name) AS district_sum,
ISNULL(LTRIM(RTRIM(district_name)),'None') AS district_name
FROM #request.app.DB#.dbo.v_fr_Districts
GROUP BY district_name

Edit: this works in Oracle (using NVL) but doesn't seem to be doing what I expected with SQL Server.

Phil
D
Dan_Bracuk
Inspiring
February 19, 2009
If your db has a ifnull or coalesce function, use it to handle the nulls.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices