dashes instead of zero values in a query

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

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
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.

PortletPaulfreelancerCommented:
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
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Please show me how you put it together if possible.

Thanks,

Mike
0
chaauCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
chaau,

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

Mike
0
chaauCommented:
I do not think it will add too much load. You can run the execution plan and check what SQL Server "thinks"
0
Mike EghtebasDatabase and Application DeveloperAuthor 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.
0
Mike EghtebasDatabase and Application DeveloperAuthor 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.
0
chaauCommented:
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

0

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
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
This works. I will try the other solution later because it is better one.

Thanks again,

Mike
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.