[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

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

0
Mike Eghtebas
Asked:
Mike Eghtebas
  • 5
  • 3
2 Solutions
 
PortletPaulCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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

Thanks again,

Mike
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now