dashes instead of zero values in a query

Mike Eghtebas
Mike Eghtebas used Ask the Experts™
on
Question: How can I have dashes instead of zero values in the following query?

This query works fine but at times has too many zeros.

Thank you.
	SELECT @nsql = 'Select left(m.MedCenter,13) As [Med Ctr], round(p.LastYearCount,0) as [' + @LY + ']
	, round(p.[01],0)as ''Jan'' , round(p.[02],0)as ''Feb'' , round(p.[03],0)as ''Mar'', round(p.[04],0)as ''Apt'', round(p.[05],0)as ''May'', 
		round(p.[06],0)as ''Jun'', round(p.[07],0)as ''Jul'', round(p.[08],0)as ''Aug'', round(p.[09],0)as ''Sep'', round(p.[10],0)as ''Oct'', round(p.[11],0)as ''Nov'', round(p.[12],0)as ''Dec''  
	FROM t08MedCenter AS m INNER JOIN t01YearlyPercent AS p 
	ON m.MedCtrID = p.MedCtr_ID' 
	   
	EXEC (@nsql)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
in SQL:
a dash is not (by itself) a numeric value, you would have to cast all numeric columns to |n|varchar and replace zero values with dash.

In a "report writer" or "presentation layer":
this may be a (relatively) simple option
Mike EghtebasDatabase and Application Developer

Author

Commented:
Please show me how you put it together if possible.

Thanks,

Mike
Top Expert 2013

Commented:
You will need to convert the number to varchar(). I will show the example using 'Jan' figure. You can apply it to all:
COALESCE(STR(NULLIF(round(p.[01],0)), 10, 0), ''-'') as ''Jan''

Open in new window

I prefer STR function than a normal CAST AS Varchar, as the former makes the numbers right justified that looks much better on screen
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mike EghtebasDatabase and Application Developer

Author

Commented:
chaau,

Thanks for the sample. Does this add too much load SQl Server?

Mike
Top Expert 2013

Commented:
I do not think it will add too much load. You can run the execution plan and check what SQL Server "thinks"
Mike EghtebasDatabase and Application Developer

Author

Commented:
My first attempt failed. I am going to try it again. Meanwhile please check WHERE clause of this query and how the bold portion is exposed to T-SQL environment:

'Select ...  WHERE (((p.Metric_ID)=' + CONVERT(VARCHAR(100),@Metric_ID) + ') AND ...'

I think, it maybe necessary for me to isolate some portion of your code so T-SQL can touch it.

brb.
Mike EghtebasDatabase and Application Developer

Author

Commented:
For your consideration, because it not ever be null, my air-code below could be translated also to do the job.

if(p.[01]= 0, '-', STR(round(p.[01],0)))

I will try your code again.
Top Expert 2013
Commented:
There is no if syntax in SQL Server. You will need to use case:
CASE p.[01] WHEN 0 THEN '-' ELSE STR(round(p.[01],0))) END

Open in new window

Mike EghtebasDatabase and Application Developer

Author

Commented:
This works. I will try the other solution later because it is better one.

Thanks again,

Mike

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial