Help with the Query - 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

Help with the Query

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-05-26 : 13:33:53

Hi

I am trying to select the total numKB for each Jobname , applicationjobid from CRMMETRICS TABLE is equal to jobname from table dw_t_asw_jobdata ,

here is my query

SELECT a.[CLUSTER_NAME]
,a.[SUB_CLUSTER_NAME]
,a.[CLUSTER_CODE]
,a.[SUB_CLUSTER_CODE]
,a.[FINISH_DAY]
,a.[FINISH_TIME]
,a.[FINISH_TIME_GMT]
,B.ID
,b.ApplicationJobID
,B.Type
,b.StartTime
,(select sum(NumKB) from dbo.CRMMetrics
group by ApplicationJobID)
from dbo.DW_T_ASW_JOBDATA a join dbo.CRMMetrics b
on a.JOB_NAME=b.ApplicationJobID
where year(a.FINISH_DAY)=2010 and MONTH(a.finish_day)=04 I am receiving the error :

Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type int.

Warning: Null value is eliminated by an aggregate or other SET operation.

What does this mean ?

I think there some jobnames in dw_t_asw_jobdata table which are not there in crmmetrics table , i want to get the SUM(NUMKB) for only the jobnames there in both the tables , if there is no jobname then just display sum(numkb) as null......

Please Help...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-26 : 14:15:22

Try this

SELECT a.[CLUSTER_NAME]
,a.[SUB_CLUSTER_NAME]
,a.[CLUSTER_CODE]
,a.[SUB_CLUSTER_CODE]
,a.[FINISH_DAY]
,a.[FINISH_TIME]
,a.[FINISH_TIME_GMT]
,B.ID
,b.ApplicationJobID
,B.Type
,b.StartTime
,b.totsum
from dbo.DW_T_ASW_JOBDATA a 
JOIN
(
select sum(NumKB) over (partition by ApplicationJobID) as totsum, * from dbo.CRMMetrics
) b
on a.JOB_NAME=b.ApplicationJobID
where year(a.FINISH_DAY)=2010 and MONTH(a.finish_day)=04

rds207
Posting Yak Master

198 Posts

Posted - 2010-05-26 : 16:17:41

I get the below error :

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

quote:

Originally posted by vijayisonly

Try this

SELECT a.[CLUSTER_NAME]
,a.[SUB_CLUSTER_NAME]
,a.[CLUSTER_CODE]
,a.[SUB_CLUSTER_CODE]
,a.[FINISH_DAY]
,a.[FINISH_TIME]
,a.[FINISH_TIME_GMT]
,B.ID
,b.ApplicationJobID
,B.Type
,b.StartTime
,b.totsum
from dbo.DW_T_ASW_JOBDATA a 
JOIN
(
select sum(NumKB) over (partition by ApplicationJobID) as totsum, * from dbo.CRMMetrics
) b
on a.JOB_NAME=b.ApplicationJobID
where year(a.FINISH_DAY)=2010 and MONTH(a.finish_day)=04

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-26 : 16:40:11

What is the datatype of NumKB? And show us some sample data from that table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources