Solved

Error in view when using CAST as INT

Posted on 2011-09-25
18
232 Views
Last Modified: 2012-05-12
Hi,

I have an issue that has justjust started to happen. This view converts four columns from VARCHAR to INT when the view is run. Prior to a few days ago the view was fine and worked perfectly. I'm not a DB man myself but I do know that you can't accomplish this unless the values in the column you want to convert are digits in the first place. I have been through the table and simply cannot see anything that is anything other than 0 or 1. We don't have any empty data in these fields, they don't contain anything other than 0 or 1's, I have completed select statements against each of the four data point but to no avail.

All I get is:

Msg 8114, Level 16, State 5, Line 2
Error converting data type nvarchar to bigint.

At the bottom of the screen it says it brought back or at least got to row 603 before this error appeared. Not sure if that means it went through the original table up to row 603 then crashed. I have been in to the table this view get's it data from and there is nothing wrong with this row.

Any ideas on how I could see what's really happening would be much appreciated!!

Best Regards,

Ken


SELECT DISTINCT 
                      CAST(nslDcs_1.AON_Y AS INT) AS AON_Y, CAST(nslDcs_1.AON_N AS INT) AS AON_N, CAST(nslDcs_1.AWH_Y AS INT) AS AWH_Y, CAST(nslDcs_1.AWH_N AS INT) 
                      AS AWH_N, nslDcs_1.date, nslDcs_1.Tref, nslDcs_1.vendorCode, dbo.country.countryName, dbo.city.cityName, dbo.products.ItemName, dbo.products.[Bin Location], 
                      dbo.city.regionCode, dbo.products.Category, dbo.store.storeName, dbo.store.storeType, dbo.storedirectory.accountCode, nslDcs_1.countryCode, nslDcs_1.cityCode, 
                      nslDcs_1.storeCode, dbo.products.[UPC Code]
FROM         dbo.nslDCS AS nslDcs_1 LEFT OUTER JOIN
                      dbo.store ON nslDcs_1.countryCode = dbo.store.countryCode AND nslDcs_1.cityCode = dbo.store.cityCode AND nslDcs_1.storeType = dbo.store.storeType AND 
                      nslDcs_1.storeCode = dbo.store.storeCode AND nslDcs_1.storeType = dbo.store.storeType AND nslDcs_1.vendorCode = dbo.store.vendorCode LEFT OUTER JOIN
                      dbo.city ON dbo.store.countryCode = dbo.city.countryCode AND dbo.store.cityCode = dbo.city.cityCode LEFT OUTER JOIN
                      dbo.country ON dbo.city.countryCode = dbo.country.countryCode LEFT OUTER JOIN
                      dbo.products ON nslDcs_1.Tref = dbo.products.TREF LEFT OUTER JOIN
                      dbo.storedirectory ON dbo.store.countryCode = dbo.storedirectory.countryCode AND dbo.store.storeCode = dbo.storedirectory.storeCode AND 
                      dbo.store.cityCode = dbo.storedirectory.cityCode AND dbo.store.storeType = dbo.storedirectory.storeType

Open in new window

0
Comment
Question by:kenuk110
  • 10
  • 7
18 Comments
 
LVL 15

Expert Comment

by:Eyal
ID: 36594538
run

SELECT *
FROM nslDcs_1 
WHERE ISNUMERIC(nslDcs_1.AON_Y)<> 1 OR
ISNUMERIC(nslDcs_1.AON_N)<> 1 OR
ISNUMERIC(nslDcs_1.AWH_Y)<> 1 OR
ISNUMERIC(nslDcs_1.AWH_N)<> 1

Open in new window


what do you get?
0
 
LVL 2

Expert Comment

by:Lord_Death
ID: 36594542
if you want to trace the data try using  a paged query for example use between statement and trace down where the problem is,

or u can use a try catch in your query to log the problem.

and finally I prefer to trim the VARCHAR field before casting.
0
 

Author Comment

by:kenuk110
ID: 36594563
Hi Eval,

I tried that and no results were returned. I'm not sure if this is good or bad! ;-)

This is what I ran, I just had to put spaces though inbetwen ORISNUMERIC

SELECT * FROM nslDcs WHERE ISNUMERIC(nslDcs.AON_Y)<> 1 OR ISNUMERIC(nslDcs.AON_N)<> 1 OR ISNUMERIC(nslDcs.AWH_Y)<> 1 OR ISNUMERIC(nslDcs.AWH_N)<> 1
0
 

Author Comment

by:kenuk110
ID: 36594566
Hi Lord_Death,

I'm really not sure how to go about this, I'm not a DB guy, the one we had left the company so because I know how to use a mobile phone I'm now in charge of database......go figure!

How would TRIM work as in the syntax with the view querie I have?

Regards,

Ken
 
0
 
LVL 15

Expert Comment

by:Eyal
ID: 36594572
no records is good :) it means that all records have numeric value

SELECT DISTINCT 
                      CAST(rtrim(ltrim(nslDcs_1.AON_Y)) AS INT) AS AON_Y, CAST(rtrim(ltrim(nslDcs_1.AON_N)) AS INT) AS AON_N, CAST(rtrim(ltrim(nslDcs_1.AWH_Y)) AS INT) AS AWH_Y, CAST(rtrim(ltrim(nslDcs_1.AWH_N)) AS INT) 
                      AS AWH_N, nslDcs_1.date, nslDcs_1.Tref, nslDcs_1.vendorCode, dbo.country.countryName, dbo.city.cityName, dbo.products.ItemName, dbo.products.[Bin Location], 
                      dbo.city.regionCode, dbo.products.Category, dbo.store.storeName, dbo.store.storeType, dbo.storedirectory.accountCode, nslDcs_1.countryCode, nslDcs_1.cityCode, 
                      nslDcs_1.storeCode, dbo.products.[UPC Code]
FROM         dbo.nslDCS AS nslDcs_1 LEFT OUTER JOIN
                      dbo.store ON nslDcs_1.countryCode = dbo.store.countryCode AND nslDcs_1.cityCode = dbo.store.cityCode AND nslDcs_1.storeType = dbo.store.storeType AND 
                      nslDcs_1.storeCode = dbo.store.storeCode AND nslDcs_1.storeType = dbo.store.storeType AND nslDcs_1.vendorCode = dbo.store.vendorCode LEFT OUTER JOIN
                      dbo.city ON dbo.store.countryCode = dbo.city.countryCode AND dbo.store.cityCode = dbo.city.cityCode LEFT OUTER JOIN
                      dbo.country ON dbo.city.countryCode = dbo.country.countryCode LEFT OUTER JOIN
                      dbo.products ON nslDcs_1.Tref = dbo.products.TREF LEFT OUTER JOIN
                      dbo.storedirectory ON dbo.store.countryCode = dbo.storedirectory.countryCode AND dbo.store.storeCode = dbo.storedirectory.storeCode AND 
                      dbo.store.cityCode = dbo.storedirectory.cityCode AND dbo.store.storeType = dbo.storedirectory.storeType

Open in new window

0
 

Author Comment

by:kenuk110
ID: 36594579
Hi Eyal, sorry, I got your name wrong last time....

When I run that I get:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to bigint.

Any ideas? Thanks for the help!
0
 

Author Comment

by:kenuk110
ID: 36594583
Just incase anything I say helps, I'm not sure why it uses describes the original table which is 'nslDCS' as 'nslDCS_1'

Just thought I'd say this, maybe stupid but then I don't know what I'm really doing!
0
 
LVL 15

Expert Comment

by:Eyal
ID: 36594584
I assume it's related to the joins
can you script the structure of the tables participating in the query and post it here?
0
 
LVL 15

Expert Comment

by:Eyal
ID: 36594588
from your query:
dbo.nslDCS AS nslDcs_1

you give name to dbo.nslDCS table as  nslDcs_1
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:kenuk110
ID: 36594708
I hope I have done this properly.

I have scripted each table to a query screen then copied and pasted them in to a code box.


NSLDCS TABLE
----------------------------------------

USE [T1]
GO

/****** Object:  Table [dbo].[nslDCS]    Script Date: 09/25/2011 11:07:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[nslDCS](
	[nslDCS1_ROW_INDEX] [int] NULL,
	[Tref] [nchar](50) NULL,
	[AON_Y] [nvarchar](1) NULL,
	[AON_N] [nvarchar](1) NULL,
	[AWH_Y] [nvarchar](1) NULL,
	[AWH_N] [nvarchar](1) NULL,
	[countryCode] [nchar](50) NULL,
	[cityCode] [nchar](50) NULL,
	[storeCode] [nchar](50) NULL,
	[storeType] [nchar](50) NULL,
	[date] [smalldatetime] NULL,
	[vendorCode] [nchar](50) NULL
) ON [PRIMARY]

GO


PRODUCTS TABLE
-------------------------------------------
USE [T1]
GO

/****** Object:  Table [dbo].[products]    Script Date: 09/25/2011 11:09:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[products](
	[Fops_Pack] [nvarchar](255) NULL,
	[UPC Code] [nvarchar](30) NOT NULL,
	[TREF] [bigint] IDENTITY(1000,1) NOT NULL,
	[ItemNo] [nvarchar](255) NULL,
	[SKU Code] [nvarchar](255) NULL,
	[Ven Part#] [nvarchar](255) NULL,
	[Vendor Name] [nvarchar](255) NULL,
	[ItemName] [nvarchar](255) NULL,
	[ITEM TYPE] [nvarchar](255) NULL,
	[Category] [nvarchar](255) NULL,
	[Desc] [nvarchar](255) NULL,
	[WIDTH] [nvarchar](255) NULL,
	[HEIGHT] [nvarchar](255) NULL,
	[DEPTH] [nvarchar](255) NULL,
	[WEIGHT] [nvarchar](255) NULL,
	[CTN PACK] [nvarchar](255) NULL,
	[PEGHOLE LEFT] [nvarchar](255) NULL,
	[PEGHOLE TOP] [nvarchar](255) NULL,
	[SUGG RETAIL] [nvarchar](255) NULL,
	[COST] [nvarchar](255) NULL,
	[LAST CHG] [nvarchar](255) NULL,
	[OUTLINE COLOR] [nvarchar](255) NULL,
	[FILL COLOR] [nvarchar](255) NULL,
	[PATTERN] [nvarchar](255) NULL,
	[IMAGE NAME] [nvarchar](255) NULL,
	[TRANSPARENT COLOR] [nvarchar](255) NULL,
	[NEST AMOUNT] [nvarchar](255) NULL,
	[Bin Location] [nvarchar](255) NULL,
	[USER2] [nvarchar](255) NULL,
	[USER3] [nvarchar](255) NULL,
	[METRIC] [nvarchar](255) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[RC] [nvarchar](255) NULL,
	[FopsCat] [nvarchar](255) NULL,
	[Priority] [nvarchar](255) NULL,
 CONSTRAINT [PK_products_1] PRIMARY KEY CLUSTERED 
(
	[TREF] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UPCCODE] UNIQUE NONCLUSTERED 
(
	[UPC Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


STORE TABLE
----------------------------------------------
USE [T1]
GO

/****** Object:  Table [dbo].[store]    Script Date: 09/25/2011 11:10:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[store](
	[pk] [bigint] IDENTITY(1,1) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[storeName] [nvarchar](250) NOT NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[classCode] [nvarchar](12) NOT NULL,
	[idr] [bit] NULL,
	[specialInstructions] [nvarchar](max) NULL,
	[storeInfo] [nvarchar](max) NULL,
 CONSTRAINT [PK_store_1] PRIMARY KEY CLUSTERED 
(
	[pk] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_store] UNIQUE NONCLUSTERED 
(
	[countryCode] ASC,
	[cityCode] ASC,
	[storeCode] ASC,
	[storeType] ASC,
	[vendorCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[store]  WITH CHECK ADD  CONSTRAINT [FK_store_city] FOREIGN KEY([countryCode], [cityCode])
REFERENCES [dbo].[city] ([countryCode], [cityCode])
GO

ALTER TABLE [dbo].[store] CHECK CONSTRAINT [FK_store_city]
GO

ALTER TABLE [dbo].[store]  WITH CHECK ADD  CONSTRAINT [FK_store_storeType] FOREIGN KEY([storeType])
REFERENCES [dbo].[storeType] ([storeType])
GO

ALTER TABLE [dbo].[store] CHECK CONSTRAINT [FK_store_storeType]
GO

ALTER TABLE [dbo].[store]  WITH CHECK ADD  CONSTRAINT [FK_store_vendor] FOREIGN KEY([vendorCode])
REFERENCES [dbo].[vendor] ([vendorCode])
GO

ALTER TABLE [dbo].[store] CHECK CONSTRAINT [FK_store_vendor]
GO

ALTER TABLE [dbo].[store] ADD  CONSTRAINT [DF_store_idr]  DEFAULT ('') FOR [idr]
GO

ALTER TABLE [dbo].[store] ADD  CONSTRAINT [DF_store_specialInstructions]  DEFAULT ('') FOR [specialInstructions]
GO


USE [T1]
GO

/****** Object:  Table [dbo].[store]    Script Date: 09/25/2011 11:10:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[store](
	[pk] [bigint] IDENTITY(1,1) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[storeName] [nvarchar](250) NOT NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[classCode] [nvarchar](12) NOT NULL,
	[idr] [bit] NULL,
	[specialInstructions] [nvarchar](max) NULL,
	[storeInfo] [nvarchar](max) NULL,
 CONSTRAINT [PK_store_1] PRIMARY KEY CLUSTERED 
(
	[pk] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_store] UNIQUE NONCLUSTERED 
(
	[countryCode] ASC,
	[cityCode] ASC,
	[storeCode] ASC,
	[storeType] ASC,
	[vendorCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[store]  WITH CHECK ADD  CONSTRAINT [FK_store_city] FOREIGN KEY([countryCode], [cityCode])
REFERENCES [dbo].[city] ([countryCode], [cityCode])
GO

ALTER TABLE [dbo].[store] CHECK CONSTRAINT [FK_store_city]
GO

ALTER TABLE [dbo].[store]  WITH CHECK ADD  CONSTRAINT [FK_store_storeType] FOREIGN KEY([storeType])
REFERENCES [dbo].[storeType] ([storeType])
GO

ALTER TABLE [dbo].[store] CHECK CONSTRAINT [FK_store_storeType]
GO

ALTER TABLE [dbo].[store]  WITH CHECK ADD  CONSTRAINT [FK_store_vendor] FOREIGN KEY([vendorCode])
REFERENCES [dbo].[vendor] ([vendorCode])
GO

ALTER TABLE [dbo].[store] CHECK CONSTRAINT [FK_store_vendor]
GO

ALTER TABLE [dbo].[store] ADD  CONSTRAINT [DF_store_idr]  DEFAULT ('') FOR [idr]
GO

ALTER TABLE [dbo].[store] ADD  CONSTRAINT [DF_store_specialInstructions]  DEFAULT ('') FOR [specialInstructions]
GO


COUNTRY TABLE
-----------------------------------------------
USE [T1]
GO

/****** Object:  Table [dbo].[country]    Script Date: 09/25/2011 11:12:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[country](
	[countryCode] [nvarchar](3) NOT NULL,
	[countryName] [nvarchar](150) NOT NULL,
	[pk] [bigint] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_country_1] PRIMARY KEY CLUSTERED 
(
	[pk] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_country] UNIQUE NONCLUSTERED 
(
	[countryCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CITY TABLE
-------------------------------------------------------
USE [T1]
GO

/****** Object:  Table [dbo].[city]    Script Date: 09/25/2011 11:13:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[city](
	[countryCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	[cityName] [nvarchar](50) NOT NULL,
	[pk] [bigint] IDENTITY(1,1) NOT NULL,
	[regionCode] [nvarchar](20) NULL,
 CONSTRAINT [PK_city_1] PRIMARY KEY CLUSTERED 
(
	[pk] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_city] UNIQUE NONCLUSTERED 
(
	[countryCode] ASC,
	[cityCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[city]  WITH CHECK ADD  CONSTRAINT [FK_city_country] FOREIGN KEY([countryCode])
REFERENCES [dbo].[country] ([countryCode])
GO

ALTER TABLE [dbo].[city] CHECK CONSTRAINT [FK_city_country]
GO

VENDOR TABLE
-------------------------------------------------
USE [T1]
GO

/****** Object:  Table [dbo].[vendor]    Script Date: 09/25/2011 11:16:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[vendor](
	[pk] [bigint] IDENTITY(1,1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[vendorName] [nvarchar](150) NOT NULL,
	[contactFirstName] [nvarchar](50) NULL,
	[contactLastName] [nvarchar](50) NULL,
	[contactEmail] [nvarchar](50) NULL,
	[contactMobile] [nvarchar](50) NULL,
	[vendorWebSite] [nvarchar](50) NULL,
 CONSTRAINT [PK_vendor] PRIMARY KEY CLUSTERED 
(
	[pk] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_vendor] UNIQUE NONCLUSTERED 
(
	[vendorCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

STOREDIRECTORY TABLE
-----------------------------------

USE [T1]
GO

/****** Object:  Table [dbo].[storedirectory]    Script Date: 09/25/2011 11:18:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[storedirectory](
	[pk] [bigint] IDENTITY(1,1) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[storeName] [nvarchar](250) NOT NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[classCode] [nvarchar](1) NOT NULL,
	[dmsActive] [bit] NULL,
	[storeTelephone] [nvarchar](50) NULL,
	[storeSize] [nvarchar](50) NULL,
	[storeAddress1] [nvarchar](50) NULL,
	[storeAddress2] [nvarchar](50) NULL,
	[zipCode] [nvarchar](50) NULL,
	[postCode] [nvarchar](50) NULL,
	[accountCode] [nvarchar](50) NULL,
	[storeInformation] [nvarchar](max) NULL,
 CONSTRAINT [PK_storedirectory] PRIMARY KEY CLUSTERED 
(
	[pk] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_storedirectory_1] UNIQUE NONCLUSTERED 
(
	[countryCode] ASC,
	[cityCode] ASC,
	[storeType] ASC,
	[storeCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[storedirectory] ADD  CONSTRAINT [DF_storedirectory_dmsActive]  DEFAULT ('') FOR [dmsActive]
GO

Open in new window

0
 
LVL 15

Accepted Solution

by:
Eyal earned 500 total points
ID: 36594819
you did it right.

as you can see for example
dbo.products ON nslDcs_1.Tref = dbo.products.TREF
dbo.products.TREF is [bigint]
and  nslDcs_1.Tref is [nchar](50)

you must also convert in the joins to equal types
try this:
SELECT DISTINCT 
                      CAST(nslDcs_1.AON_Y AS INT) AS AON_Y, CAST(nslDcs_1.AON_N AS INT) AS AON_N, CAST(nslDcs_1.AWH_Y AS INT) AS AWH_Y, CAST(nslDcs_1.AWH_N AS INT) 
                      AS AWH_N, nslDcs_1.date, nslDcs_1.Tref, nslDcs_1.vendorCode, dbo.country.countryName, dbo.city.cityName, dbo.products.ItemName, dbo.products.[Bin Location], 
                      dbo.city.regionCode, dbo.products.Category, dbo.store.storeName, dbo.store.storeType, dbo.storedirectory.accountCode, nslDcs_1.countryCode, nslDcs_1.cityCode, 
                      nslDcs_1.storeCode, dbo.products.[UPC Code]
FROM         dbo.nslDCS AS nslDcs_1 LEFT OUTER JOIN
                      dbo.store ON nslDcs_1.countryCode = dbo.store.countryCode AND nslDcs_1.cityCode = dbo.store.cityCode AND nslDcs_1.storeType = dbo.store.storeType AND 
                      nslDcs_1.storeCode = dbo.store.storeCode AND nslDcs_1.storeType = dbo.store.storeType AND nslDcs_1.vendorCode = dbo.store.vendorCode LEFT OUTER JOIN
                      dbo.city ON dbo.store.countryCode = dbo.city.countryCode AND dbo.store.cityCode = dbo.city.cityCode LEFT OUTER JOIN
                      dbo.country ON dbo.city.countryCode = dbo.country.countryCode LEFT OUTER JOIN
                      dbo.products ON CAST(nslDcs_1.Tref as bigint) = dbo.products.TREF LEFT OUTER JOIN
                      dbo.storedirectory ON dbo.store.countryCode = dbo.storedirectory.countryCode AND dbo.store.storeCode = dbo.storedirectory.storeCode AND 
                      dbo.store.cityCode = dbo.storedirectory.cityCode AND dbo.store.storeType = dbo.storedirectory.storeType

Open in new window

0
 

Author Comment

by:kenuk110
ID: 36594835
Hi,

I tried what you posted, I also understand what you are saying but I get this:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to bigint.
0
 

Author Comment

by:kenuk110
ID: 36594839
I ran the querie again changing the bigint to int on the products TREF section and I get this:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '6'46                                              ' to data type int.

Is there a way to see all tref entries in nslDCS that are not all numerics? Maybe thats the problem?

I really appreciate your help on this!
0
 

Author Comment

by:kenuk110
ID: 36594840
All the entries in the tref colum should be 4 digits only - fyi
0
 
LVL 15

Expert Comment

by:Eyal
ID: 36594842
SURE,

SELECT *
FROM nslDcs
WHERE ISNUMERIC(TREF)<> 1
0
 

Author Comment

by:kenuk110
ID: 36594862
FANTASTIC!!!! IT WORKS!!!!!

Thank you so much for your help on this, I really really appreciate it!

0
 
LVL 15

Expert Comment

by:Eyal
ID: 36594874
FYI, it's not a good practice to build constrains between tables like  this.
I saw each table has field names pk so from other tables you should refer to this field with the same field type.
0
 

Author Comment

by:kenuk110
ID: 36594880
Hi,

I know you are right it's just I really don't have the knowledge to review how this lot has been setup, we should be getting a new DB guy within a month or two so I'm hoping he will resolve some of these issues. I will print this suggestion and leave it as advice for him.

Thanks again.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now