Solved

Error in view when using CAST as INT

Posted on 2011-09-25
18
247 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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
 

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 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