Printing '0' If NULL? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Printing '0' If NULL?

Author

Topic

towardabettercountry
Starting Member

26 Posts

Posted - 2012-06-27 : 10:52:30

I'm working on an SSRS report. A few tablix won't show because no results are returned. However, I would like 0 to show if no results are returned. Would someone mind showing me where I could put a CASE statement or how I might create a TEMP table to accomplish this? (And if a better solution exists, feel free to give that!). Thanks so much. Here is my query:

SELECT
[ROMA - Acceptance Fee] AS ROMA_Accept_Fee
,[ROMA - Pre-Registration Fee] AS ROMA_Pre_Reg_Fee

FROM
(
SELECT
cte.[Admissions Fee]
,cte.Count
FROM cte
) pt

PIVOT
(
SUM(pt.Count) FOR pt.[Admissions Fee]
IN ([ROMA - Acceptance Fee], [ROMA - Pre-Registration Fee])
) as pvt

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-06-27 : 10:58:01

SELECT
cte.[Admissions Fee]
,ISNULL(cte.Count,0)
FROM cte

or put it in your CTE.

towardabettercountry
Starting Member

26 Posts

Posted - 2012-06-27 : 11:10:47

Thanks, Rick. I put inserted your suggestion (see below) but still don't get a 0 printed. (I would rather keep it out of the cte table as I think I found a way to not need it). Am I doing something wrong? Thanks again for the help...

SELECT
[ROMA - Acceptance Fee] AS ROMA_Accept_Fee
,[ROMA - Pre-Registration Fee] AS ROMA_Pre_Reg_Fee

FROM
(
SELECT
cte.[Admissions Fee]
,ISNULL(cte.Count,0) AS Count
FROM cte
) pt

PIVOT
(
SUM(pt.Count) FOR pt.[Admissions Fee]
IN ([ROMA - Acceptance Fee], [ROMA - Pre-Registration Fee])
) as pvt

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-27 : 14:38:36

quote:
Originally posted by towardabettercountry

Thanks, Rick. I put inserted your suggestion (see below) but still don't get a 0 printed. (I would rather keep it out of the cte table as I think I found a way to not need it). Am I doing something wrong? Thanks again for the help...

SELECT
[ROMA - Acceptance Fee] AS ROMA_Accept_Fee
,[ROMA - Pre-Registration Fee] AS ROMA_Pre_Reg_Fee

FROM
(
SELECT
cte.[Admissions Fee]
,ISNULL(cte.Count,0) AS Count
FROM cte
) pt

PIVOT
(
SUM(pt.Count) FOR pt.[Admissions Fee]
IN ([ROMA - Acceptance Fee], [ROMA - Pre-Registration Fee])
) as pvt

This may be an artifact of SSRS. Check the formatting expression for the column or text box where you are displaying the data. You can add a custom format string there - for example this would format the numbers showing 0 when the value is null or 0 (and also include thousands separators (because of the commas).

#,##0;-#,##0;0

You can find details on custom formatting strings here: http://msdn.microsoft.com/en-us/library/0c899ak8.aspx

towardabettercountry
Starting Member

26 Posts

Posted - 2012-06-27 : 15:49:53

Thanks for the reply. I tried that but the result is the same (which is no results). I put it in Properties > Textbox > Number > "Format". That's right, correct?

I don't think ISNULL can work in the SELECT statement or FROM subquery. I think that's because PIVOT simply counts the values for the IN clause and returns what it finds. So that means 0 will be printed for NULLs, but won't appear in the PIVOT section because it doesn't know to look for that. There may be a way for ISNULL to help, but I'm too much of a rookie to know :-)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-27 : 17:51:03

Can you post some sample data? And also results of the inner query and the whole query that you get when you run it from SSMS?

towardabettercountry
Starting Member

26 Posts

Posted - 2012-06-28 : 10:25:33

Here is some sample data (but see the section below):

CREATE TABLE TestingPivot
(
PivotID CHAR(10) NOT NULL,
Activity CHAR(25) NOT NULL,
);

INSERT INTO TestingPivot
(
PivotID,
Activity
)

Values
('1', 'ROMA - Pre-Mat Fee'),
('2', 'ROMA - Pre-Mat Fee'),
('3', 'ROMA - Pre-Art Fee')

Here is a query that will return one row of NULL:
SELECT
[ROMA - Acceptance Fee] AS ROMA_Accept_Fee
,[ROMA - Pre-Registration Fee] AS ROMA_Pre_Reg_Fee

FROM
(
SELECT
Activity AS Activity
,COUNT(DISTINCT PivotID) AS COUNT
FROM TestingPivot
GROUP BY Activity
)pt

PIVOT
(
SUM(pt.Count) FOR pt.[Activity]
IN ([ROMA - Acceptance Fee], [ROMA - Pre-Registration Fee])
) as pvt

--------
I think I can just use a cte table and check it with a CASE statement. Here's the final piece I can't figure out: I created the above as an ad hoc table for this example. (The other query is setup as basically the same. However, there are a few joins and the data is complex making it hard to create an example). If the above query is executed in SSMS, it returns one row with NULL. However, my other query doesn't return any rows. It doesn't say "NULL", it just doesn't say anything. I think that's a problem. While a cte table with a case statement will work on the above solution, I'm still not returning any results using the same on my other query.

So my question - Does anyone know what might cause SSMS to return 1 row of NULL in one instance, and not return any rows in another instance? Perhaps something in the data itself?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources