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 2000 Forums
 SQL Server Development (2000)
 Arithmetic overflow error when doing outer joins

Author  Topic 

Yukke
Starting Member

5 Posts
Posted - 2006-07-20 : 10:02:40
Hey everyone,

I have two tables, one is a large table (v_userviews) containing a list of all the servers and various information about those servers. The other table (l_printers) contains printer information for those servers. I am working on a view to consolidate the printer information in l_printers with the other server information in v_userviews.

I've been trying to get outer joins to work but I am getting this error:
"Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation."

Here is my select statement:
select u.propid, u.address, 
SUM((CASE u.Tree WHEN 'tree1' then 1 ELSE 0 END)) AS One,
SUM((CASE u.Tree WHEN 'tree2' then 1 ELSE 0 END)) AS Two,
SUM((CASE u.Tree WHEN 'tree3' then 1 ELSE 0 END)) AS Three,
SUM((CASE u.Tree WHEN 'tree4' then 1 ELSE 0 END)) AS Four,
SUM((CASE u.Tree WHEN 'tree5' then 1 ELSE 0 END)) AS Five,
SUM((CASE u.Tree WHEN 'tree6' then 1 ELSE 0 END)) AS Six,
SUM((CASE u.Tree WHEN 'tree7' then 1 ELSE 0 END)) AS Seven,
SUM((CASE u.Tree WHEN 'tree8' then 1 ELSE 0 END)) AS Eight,
SUM((CASE u.Tree WHEN 'tree9' then 1 ELSE 0 END)) AS Nine,
SUM((CASE u.Tree WHEN 'tree10' then 1 ELSE 0 END)) AS Ten,
SUM((CASE u.Tree WHEN 'tree11' then 1 ELSE 0 END)) AS Eleven,
SUM((CASE u.Tree WHEN 'tree12' then 1 ELSE 0 END)) AS Twelve,
SUM((CASE u.Tree WHEN 'tree13' then 1 ELSE 0 END)) AS Thirteen,
SUM((CASE u.Tree WHEN 'tree14' then 1 ELSE 0 END)) AS Fourteen,

count(u.server) as totalservers, 
sum(cast(left(u.totalspace,len(u.totalspace)-2) as int)) as totalspace,
sum(cast(left(u.totalusedspace,len(u.totalusedspace)-2) as int)) as totalusedspace,
count(p.printer) as numprinters

from serverops.dbo.v_userviews u LEFT OUTER JOIN novell_twr.dbo.l_printers p ON u.propid = p.propid
where u.os='netware'and u.state in ('ny', 'nj', 'fl')
group by u.propid, u.address


L_Printers DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[L_Printers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[L_Printers]
GO

CREATE TABLE [dbo].[L_Printers] (
	[Printer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Server] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PropID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO


R_Servers (v_userviews is actually just a distinct top 100 view of r_servers):
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[R_Servers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[R_Servers]
GO

CREATE TABLE [dbo].[R_Servers] (
	[Server] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Type] [varchar] (51) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Classification] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[IPX Internal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Secondary IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DNS_IP1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DNS_IP2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DNS_RIBIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PropID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Branch #] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OSVersion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[InProduction] [bit] NULL ,
	[NOTES] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Tree] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NWContext] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NTDomain] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NTDomainRole] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LASTXEUpdate] [datetime] NULL ,
	[MacAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CommonName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Zip] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TFloors] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Bank_Floors] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Country] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TotalEmp] [float] NULL ,
	[Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Street Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Room] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Floor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[IPResponse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ResponseOK] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TapeDrive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TapeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DriveCapacity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[IP Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RIBIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RIBPW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Remote PW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Console PW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DeviceStatus] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RIBInstalled] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SPack] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DeviceOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ASource] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BackupDetails] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ServerID] [numeric](10, 0) NOT NULL ,
	[Model] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Serial] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[AssetNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Memory] [numeric](18, 0) NULL ,
	[ROM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CPU] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CPUSpeed] [decimal](18, 0) NULL ,
	[TotalCPUs] [int] NULL ,
	[Vendor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PODate] [datetime] NULL ,
	[ReceivedDate] [smalldatetime] NULL ,
	[ActivationDate] [smalldatetime] NULL ,
	[RefreshDate] [smalldatetime] NULL ,
	[WarrantyEndDate] [smalldatetime] NULL ,
	[TowerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TowerID] [int] NULL ,
	[DistrictID] [int] NULL ,
	[Priority] [int] NULL ,
	[Severity] [int] NULL ,
	[SupportComment] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SiteContact] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SupportStaff] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PSRegion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DistrictManager] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DMName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SupportQueue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[District] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Domain] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[inProductionDate] [smalldatetime] NULL ,
	[Region] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TotalSpace] [nvarchar] (257) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TotalUsedSpace] [nvarchar] (257) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RackID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RackPosition] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RackRow] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OSType] [int] NULL ,
	[Project#] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClusterID] [numeric](18, 0) NULL ,
	[RIBLicenseKey] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Prop_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastUpdate] [datetime] NULL ,
	[CheckSum] [int] NULL ,
	[ManagmentServer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastAudit] [datetime] NULL ,
	[RecordAuditor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PrimarySupport] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[KVM] [bit] NULL ,
	[KVMType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[expanse] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LOBOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PrimaryRecordOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreationDate] [datetime] NULL ,
	[Strategic] [bit] NULL ,
	[ServiceBillingCC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HardwareSupport] [bit] NULL ,
	[Updated] [datetime] NOT NULL 
) ON [PRIMARY]
GO


The expected output should be like this:

PropID ONE TWO THREE FOUR ...  totalservers totalspace totalusedspace numprinters



Any help will be appreciated

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2006-07-20 : 10:10:43
What the heck are you trying to do here:

sum(cast(left(u.totalspace,len(u.totalspace)-2) as int)) as totalspace,
sum(cast(left(u.totalusedspace,len(u.totalusedspace)-2) as int)) as totalusedspace,

??

What datatype is totalspace and totalusedspace? Are you trying to round these numbers? chances are it is this expression that is causing your issue.

Your CASE function is fine, there are two ways you can phrase your CASE syntax.

- Jeff
Go to Top of Page

Srinika
Master Smack Fu Yak Hacker

1378 Posts
Posted - 2006-07-20 : 10:13:11
I strongly feel that the error is not in "OUTER JOIN" but could be in
either of the following :

sum(cast(left(u.totalspace,len(u.totalspace)-2) as int)) as totalspace,
sum(cast(left(u.totalusedspace,len(u.totalusedspace)-2) as int)) as totalusedspace,

--

So remove those 2 and run the query, to c the results

Srinika
Go to Top of Page

Yukke
Starting Member

5 Posts
Posted - 2006-07-20 : 10:15:19
the "sum(cast(left(u.totalspace,len(u.totalspace)-2) as int)) as totalspace" statement is for adding up the total disk space available and total disk space used for the server.
originally totalspace and totalusedspace is of type nvarchar in this format, ex: "46667MB" which is why I took out the "MB" part and cast it as an integer before summing it up.

this expression works in another view that I created before. I only get this error when I add:
count(p.printer) as numprinters
and
from serverops.dbo.v_userviews u LEFT OUTER JOIN novell_twr.dbo.l_printers p ON u.propid = p.propid
Go to Top of Page

Yukke
Starting Member

5 Posts
Posted - 2006-07-20 : 10:18:27
I took out the two sum(cast... expressions like you guys said. Now, I dont get the error message but the totalservers and numprinters column values are exactly the same when they should be different and the values are much higher than expected.
Go to Top of Page

Srinika
Master Smack Fu Yak Hacker

1378 Posts
Posted - 2006-07-20 : 10:25:33
Yukke,

-- v suggested u to remove that & c, to drill down to the point which is causing the error.
-- as Jeff says, u may need to have the Cast ... in the correct way
-- totalservers and numprinters are same, not because of u removed the above 2, that may associate with something else

Srinika
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site