We help IT Professionals succeed at work.

Check out this week's podcast, "Dairy Farms to Databases: Community's Hand in Technology"Listen Now

x

dashes instead of zero values in a query

197 Views
Last Modified: 2014-08-27
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

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mike EghtebasDatabase and Application Developer

Author

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

Thanks,

Mike
CERTIFIED EXPERT
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
Mike EghtebasDatabase and Application Developer

Author

Commented:
chaau,

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

Mike
CERTIFIED EXPERT
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.
CERTIFIED EXPERT
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mike EghtebasDatabase and Application Developer

Author

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

Thanks again,

Mike
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.