join int and varchar column iff values are numerical
using sql server 2005
all the orders.orderid are int
only want to join the numerical values, do not join if values are not numerical
select * from trackingnumbers t
left join orders o on t.orderid=o.orderid
The conversion of the varchar value '190365396412HRT' overflowed an int column. Maximum integer value exceeded.
USE [ow1222]
GO
/****** Object: Table [dbo].[trackingnumbers] Script Date: 03/14/2013 12:01:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[trackingnumbers](
[trackingnumberid] [int] IDENTITY(1,1) NOT NULL,
[date] [datetime] NOT NULL CONSTRAINT [DF_trackingnumbers_date] DEFAULT (getdate()),
[orderid] [varchar](50) NOT NULL,
[trackingnumber] [varchar](50) NULL,
[shipper] [varchar](250) NULL,
[cost] [decimal](10, 2) NULL,
[void] [varchar](20) NULL,
[name] [varchar](100) NULL,
[address] [varchar](250) NULL,
CONSTRAINT [PK__trackingnumbers__2B3F
(
[trackingnumberid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
all the orders.orderid are int
only want to join the numerical values, do not join if values are not numerical
select * from trackingnumbers t
left join orders o on t.orderid=o.orderid
The conversion of the varchar value '190365396412HRT' overflowed an int column. Maximum integer value exceeded.
USE [ow1222]
GO
/****** Object: Table [dbo].[trackingnumbers] Script Date: 03/14/2013 12:01:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[trackingnumbers](
[trackingnumberid] [int] IDENTITY(1,1) NOT NULL,
[date] [datetime] NOT NULL CONSTRAINT [DF_trackingnumbers_date] DEFAULT (getdate()),
[orderid] [varchar](50) NOT NULL,
[trackingnumber] [varchar](50) NULL,
[shipper] [varchar](250) NULL,
[cost] [decimal](10, 2) NULL,
[void] [varchar](20) NULL,
[name] [varchar](100) NULL,
[address] [varchar](250) NULL,
CONSTRAINT [PK__trackingnumbers__2B3F
(
[trackingnumberid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
all 3 queries return results
aneeshattingal query edited ordeID to orderID
ScottPletcher takes long time to run and displays alot of data
thanks
aneeshattingal query edited ordeID to orderID
ScottPletcher takes long time to run and displays alot of data
thanks
1 -- joining strings and numerics.
2 -- numeric value larger than an integer.
You can certainly join strings and numerics, but you add a lot of overhead to the query as each string will need to be converted to an integer every time the query runs. This also nullifies any index that may be on the column.
You may wish to add another column to the table and convert the varchar column into the new column. Then the join is trivial.
As far as the large values, make the data type BIGINT.
Good Luck,
Kent