NULL showing when they shouldn't be

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

SQL Server 2005 Forums

Transact-SQL (2005)

NULL showing when they shouldn't be

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-01-06 : 13:22:33

I have a table holding training cost data for various organisational departments (e.g. how much was spent on training).
I run a query and place the results into a table variable, which I can then group in different ways as required.
I need to show how much each department has spent on training, for all different currencies that exist within the first set of results, e.g.


siteOrgLevelID   colname               regionCulture   totals
-------------------------------------------------------------
1                Durham, IT            £ GBP           15250
2                Essex, HR             £ GBP           7452
3                Essex, Production     $ USD           3256
4                Essex, Production     $ USD           6982

To force the query to show all currencies for each department, I use a CROSS JOIN, then LEFT OUTER JOIN my original results to SUM the costs. This allows for effective graphing at a later stage. The problem is that when no cost data exists for a department, both the [colname] and [totals] columns show NULL values. The [siteOrgLevelID] column however still shows the correct ID value. I can understand about the costs, so I use COALESCE to produce zeros, but within my initial results table (@tmp) there is ALWAYS a department name for each [siteOrgLevelID], so I don't understand what's happening.


siteOrgLevelID   colname               regionCulture   totals
-------------------------------------------------------------
1                Durham, IT            £ GBP           15250
2                NULL                  £ GBP           NULL <-- [colname] should have a value!
3                Essex, Production     $ USD           3256
4                Essex, Production     $ USD           6982

Can anyone please explain how to force the query to show the department name that corresponds to the ID value?


-- first put original query into @tmp (omitted)
-- then...

DECLARE @Depts TABLE
	(
	siteOrgLevelID int
	)
INSERT INTO @Depts
	(
	siteOrgLevelID
	)
SELECT
	DISTINCT(siteOrgLevelID)
FROM
	@tmp
	

DECLARE @regionCultures TABLE 
	(
	regionCultureID int NOT NULL, 
	regionCultureCode nvarchar(5) not null
	)
INSERT INTO @regionCultures
	(
	regionCultureID,
	regionCultureCode
	)
SELECT
	DISTINCT (regionCultureID),
	regionCultureCode
FROM
	@tmp


SELECT
	d.siteOrgLevelID,
	rc.regionCultureID,
	rc.regionCultureCode,
	t.siteName + ', ' + t.orgLevelName as colname, -- NULLs get shown here when @tmp.costAmount is null
	SUM(COALESCE(t.costAmount, 0)) as totals
FROM
	@Depts d CROSS JOIN 
	@regionCultures rc LEFT OUTER JOIN
	@tmp t ON t.siteOrgLevelID = d.siteOrgLevelID AND rc.regionCultureID = t.regionCultureID
GROUP BY
	rc.regionCultureID, 
	rc.regionCultureCode, 
	d.siteOrglevelID, 
	t.siteName, 
	t.orgLevelName
ORDER BY
	t.siteName + ', ' + t.orgLevelName
	rc.regionCultureCode

I've tried to include a screen grab to illustrate this more clearly. Apologies for my long winded explanation...!

Thank you in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 13:30:04

seems like you need to include depart info in @Depts table also and retrieve from it

shan
Yak Posting Veteran

84 Posts

Posted - 2010-01-06 : 13:40:00

Try running with lesser tables \ joins and see how the data output looks like,

for example

@Depts d CROSS JOIN
@regionCultures rc

then with

@regionCultures rc LEFT OUTER JOIN
@tmp

That way you will have a better idea if there is data related issue.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-06 : 13:51:53

Since your deriving from multiple columns could one of the two be null?

t.siteName + ', ' + t.orgLevelName as colname

Either: t.siteName or t.OrgLevelName

Try something like:

Coalesce(t.siteName,'') + ', ' + Coalesce(t.orgLevelName,'') as colname

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-01-07 : 03:35:57

Hi all

Thanks for your replies. After having a sleep it seems I really was overlooking the obvious! Yes, as visakh16 correctly points out I need to get all the department titles first, then join to the totals, otherwise the department titles won't exist.

Thank you very much for your clarifications, keep up the good work!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 03:38:04

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources