MBoy2
asked on
SQL - SP This line allowing Non Int to get through
This fails because data 3.5 won't convert to Int. Why is 3.5 making it through this case?
'LengthOfEscarpment_FT' = CASE WHEN Cast([Length of Esc(ft)] as INT) = 1
THEN [Length of Esc(ft)]
ELSE '0'
END
'LengthOfEscarpment_FT' = CASE WHEN Cast([Length of Esc(ft)] as INT) = 1
THEN [Length of Esc(ft)]
ELSE '0'
END
ASKER
I'm just looking for a quick answer if available.. here's the complete SP
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[Mote_Ge tAllV1Nest Data]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Mote_GetAllV1NestDa ta];
GO
CREATE PROCEDURE [dbo].[Mote_GetAllV1NestDa ta]
AS
BEGIN
SELECT
[ID] As 'OriginalNestID',
'IsNest' = CASE [Nest]
WHEN 0 THEN '0'
WHEN 1 THEN '1'
Else '0'
END,
'UnmarkedNest' = CASE [Verified]
WHEN 0 THEN '0'
WHEN 1 THEN '1'
Else '0'
END,
[Ft# So#] As 'FTNumberSONumber',
'NestLocationDate' = CASE WHEN ISDATE(Cast([Date] as datetime)) = 1
THEN CONVERT(VARCHAR(10), [Date], 101)
ELSE '01/01/1900'
END,
(Select [Mote_Observer].[ObserverI D] FROM [Mote_Observer] WHERE [Mote_Observer].[ObserverN ame] = [V1Nest].[Observer]) AS ObserverID,
(Select [Mote_Observer].[ObserverI D] FROM [Mote_Observer] WHERE [Mote_Observer].[ObserverN ame] = 'Unknown Observer') AS Observer2ID,
(Select [Mote_Observer].[ObserverI D] FROM [Mote_Observer] WHERE [Mote_Observer].[ObserverN ame] = 'Unknown Observer') AS Observer3ID,
(Select [Mote_Observer].[ObserverI D] FROM [Mote_Observer] WHERE [Mote_Observer].[ObserverN ame] = 'Unknown Observer') AS Observer4ID,
[Nest/FC No#] As 'NestFCNumber',
(Select [Mote_Key].[KeyID] FROM [Mote_Key] WHERE [Mote_Key].[KeyName] = [V1Nest].[Key]) AS KeyID,
(Select [Mote_Zone].[ZoneID] FROM [Mote_Zone] WHERE [Mote_Zone].[ZoneName] = [V1Nest].[Zone]) AS ZoneID,
(Select [Mote_DnrMonumentNumber].[ DnrMonumen tNumberID] FROM [Mote_DnrMonumentNumber] WHERE [Mote_DnrMonumentNumber].[ DnrMonumen tNumber] = [V1Nest].[DNR Monument #]) AS DnrMonumentNumberID,
[Address],
Select [Mote_County].[CountyID] FROM [Mote_County] WHERE [Mote_County].[CountyName] = 'Unknown County') AS ObserverID,
(Select [Mote_CondoHotelPark].[Con doHotelPar kID] FROM [Mote_CondoHotelPark] WHERE [Mote_CondoHotelPark].[Con doHotelPar kName] = [V1Nest].[Condo/Resort/Par k Name]) AS CondoHotelParkID,
(Select [Mote_Species].[SpeciesID] FROM [Mote_Species] WHERE [Mote_Species].[SpeciesNam e] = [V1Nest].[Species]) AS SpeciesID,
[Latitude] As 'NestLatitude',
[Longitude] As 'NestLongitude',
(Select [Mote_BeachCategory].[Beac hCategoryI D] FROM [Mote_BeachCategory] WHERE [Mote_BeachCategory].[Beac hCategoryN ame] = [V1Nest].[Beach Category]) AS BeachCategoryID,
[Initial Surface2Eggs(cm)] As 'InitialSurface2Eggs',
[NNE - # Body Pits] As 'NneNumberBodyPits',
[NNE - # Abandon Cavity] As 'NneNumberAbandonCavity',
[Obstructions] As 'Obstructions',
(Select [Mote_EscarpmentType].[Esc arpmentTyp eID] FROM [Mote_EscarpmentType] WHERE [Mote_EscarpmentType].[Esc arpmentTyp eName] = [V1Nest].[Type Esc(Sloped/Vertical)]) AS EscarpmentTypeID,
'DistMHW_FT' = CASE WHEN Cast([Dist from MHW(ft)] as INT) = 1
THEN [Dist from MHW(ft)]
ELSE '0'
END,
'DistBarrier_FT' = CASE WHEN Cast([Dist from Barrier(ft)] as INT) = 1
THEN [Dist from Barrier(ft)]
ELSE '0'
END,
'HeightOfEscarpment_FT' = CASE WHEN Cast([Height of Esc(ft)] as INT) = 1
THEN [Height of Esc(ft)]
ELSE '0'
END,
'LengthOfEscarpment_FT' = CASE WHEN Cast([Length of Esc(ft)] as INT) = 1
THEN [Length of Esc(ft)]
ELSE '0'
END,
[Overall Comments] As 'NestLocationComments',
[ActiveRecord] As 'Active'
FROM [V1Nest]
END
GO
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[Mote_Ge
DROP PROCEDURE [dbo].[Mote_GetAllV1NestDa
GO
CREATE PROCEDURE [dbo].[Mote_GetAllV1NestDa
AS
BEGIN
SELECT
[ID] As 'OriginalNestID',
'IsNest' = CASE [Nest]
WHEN 0 THEN '0'
WHEN 1 THEN '1'
Else '0'
END,
'UnmarkedNest' = CASE [Verified]
WHEN 0 THEN '0'
WHEN 1 THEN '1'
Else '0'
END,
[Ft# So#] As 'FTNumberSONumber',
'NestLocationDate' = CASE WHEN ISDATE(Cast([Date] as datetime)) = 1
THEN CONVERT(VARCHAR(10), [Date], 101)
ELSE '01/01/1900'
END,
(Select [Mote_Observer].[ObserverI
(Select [Mote_Observer].[ObserverI
(Select [Mote_Observer].[ObserverI
(Select [Mote_Observer].[ObserverI
[Nest/FC No#] As 'NestFCNumber',
(Select [Mote_Key].[KeyID] FROM [Mote_Key] WHERE [Mote_Key].[KeyName] = [V1Nest].[Key]) AS KeyID,
(Select [Mote_Zone].[ZoneID] FROM [Mote_Zone] WHERE [Mote_Zone].[ZoneName] = [V1Nest].[Zone]) AS ZoneID,
(Select [Mote_DnrMonumentNumber].[
[Address],
Select [Mote_County].[CountyID] FROM [Mote_County] WHERE [Mote_County].[CountyName]
(Select [Mote_CondoHotelPark].[Con
(Select [Mote_Species].[SpeciesID]
[Latitude] As 'NestLatitude',
[Longitude] As 'NestLongitude',
(Select [Mote_BeachCategory].[Beac
[Initial Surface2Eggs(cm)] As 'InitialSurface2Eggs',
[NNE - # Body Pits] As 'NneNumberBodyPits',
[NNE - # Abandon Cavity] As 'NneNumberAbandonCavity',
[Obstructions] As 'Obstructions',
(Select [Mote_EscarpmentType].[Esc
'DistMHW_FT' = CASE WHEN Cast([Dist from MHW(ft)] as INT) = 1
THEN [Dist from MHW(ft)]
ELSE '0'
END,
'DistBarrier_FT' = CASE WHEN Cast([Dist from Barrier(ft)] as INT) = 1
THEN [Dist from Barrier(ft)]
ELSE '0'
END,
'HeightOfEscarpment_FT' = CASE WHEN Cast([Height of Esc(ft)] as INT) = 1
THEN [Height of Esc(ft)]
ELSE '0'
END,
'LengthOfEscarpment_FT' = CASE WHEN Cast([Length of Esc(ft)] as INT) = 1
THEN [Length of Esc(ft)]
ELSE '0'
END,
[Overall Comments] As 'NestLocationComments',
[ActiveRecord] As 'Active'
FROM [V1Nest]
END
GO
Quick? Replace the offending block with this...
'LengthOfEscarpment_FT' = CASE WHEN Cast([Length of Esc(ft)] as numeric(19,4)) = 1
THEN [Length of Esc(ft)]
ELSE '0'
END
'LengthOfEscarpment_FT' = CASE WHEN Cast([Length of Esc(ft)] as numeric(19,4)) = 1
THEN [Length of Esc(ft)]
ELSE '0'
END
ASKER
Now I get...
Error converting data type nvarchar to numeric.
Error converting data type nvarchar to numeric.
I suspect that your original problem is not exactly what you think it is. The problem with converting your column to an integer is occuring in the CAST function and not in the assignment of the value to the more properly named column.
Using the [Length of Esc(ft)] column name is bad enough but to also have a numeric value stored in an nvarchar column is just compounding your sins. This problem is just one of the reasons why you shouldn't do that.
Using the [Length of Esc(ft)] column name is bad enough but to also have a numeric value stored in an nvarchar column is just compounding your sins. This problem is just one of the reasons why you shouldn't do that.
ASKER
If you look at the flow of the SP you can see that I am converting a database designed by an idiot into a database designed by a professional. I'm using a mix of C# code and stored procedures to move 51K records into a normalized design. I'm also donating my time on this one.
OLD...
[Emergence No#] [nvarchar](12) NULL,
[Key] [nvarchar](4) NULL,
[Date] [date] NULL,
[Observer] [nvarchar](100) NULL,
[Zone] [nvarchar](6) NULL,
[Species] [nvarchar](4) NULL,
[Nest] [nvarchar](2) NULL,
[Verified] [nvarchar](1) NULL,
[Unmarked Nest] [bit] NOT NULL,
[Initial Surface2Eggs(cm)] [int] NULL,
[Nest/FC No#] [nvarchar](25) NULL,
[Dist from MHW(ft)] [int] NULL,
[Dist from Barrier(ft)] [int] NULL,
[NNE - # Body Pits] [int] NULL,
[NNE - # Abandon Cavity] [int] NULL,
[Address] [nvarchar](52) NULL,
[Condo/Resort/Park Name] [nvarchar](50) NULL,
[DNR Monument #] [int] NULL,
[Ft# So#] [int] NULL,
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[Beach Category] [nvarchar](13) NULL,
[Obstructions] [nvarchar](250) NULL,
[Type Esc(Sloped/Vertical)] [nvarchar](15) NULL,
[Crawled Over Esc] [nvarchar](50) NULL,
[Height of Esc(ft)] [nvarchar](5) NULL,
[Length of Esc(ft)] [nvarchar](5) NULL,
[PIT Tag #] [nvarchar](50) NULL,
[LFF Tag #] [nvarchar](50) NULL,
[RFF Tag #] [nvarchar](50) NULL,
[Turtle Number] [int] NULL,
[Comments] [nvarchar](225) NULL,
[Caged/Screened] [nvarchar](3) NULL,
[Date Caged/Screen] [date] NULL,
[Type Cage/Screen] [nvarchar](3) NULL,
[Relocated] [nvarchar](3) NULL,
[Date Relocated] [date] NULL,
[Relocator] [nvarchar](100) NULL,
[Why Moved] [nvarchar](32) NULL,
[# Eggs Moved] [float] NULL,
[# Eggs Relocator Broke] [float] NULL,
[New Location] [nvarchar](33) NULL,
[Relocated Latitude] [float] NULL,
[Relocated Longitude] [float] NULL,
[Initial Treatment] [nvarchar](1) NULL,
[Final Treatment] [nvarchar](1) NULL,
[Predated] [nvarchar](1) NULL,
[Date(s) Predated] [nvarchar](250) NULL,
[Predator(s)] [nvarchar](250) NULL,
[Root Damage (0 if none)] [nvarchar](20) NULL,
[# Eggs Damaged by Pred] [float] NULL,
[Vandalized] [nvarchar](3) NULL,
[Date(s) Vandalized] [nvarchar](25) NULL,
[Type of Vandalism] [nvarchar](25) NULL,
[Poached] [nvarchar](3) NULL,
[Date(s) Poached] [nvarchar](25) NULL,
[Were Eggs Removed] [nvarchar](3) NULL,
[Nest Washed Over] [nvarchar](3) NULL,
[Date(s) Washed Over] [nvarchar](255) NULL,
[Storm Name Washed Over] [nvarchar](25) NULL,
[Nest Washed Out] [nvarchar](3) NULL,
[Date(s) Washed Out] [nvarchar](25) NULL,
[Storm Name Washed Out] [nvarchar](25) NULL,
[Drop Date] [date] NULL,
[Date Hatched] [date] NULL,
[Add_l Hatch Date(s)] [nvarchar](10) NULL,
[Disorientation Date(s)] [nvarchar](15) NULL,
[Date Excavated] [date] NULL,
[Excavated By] [nvarchar](100) NULL,
[Live in Nest] [float] NULL,
[Dead in Nest] [float] NULL,
[Final Surface to Eggs(cm)] [float] NULL,
[Depth to Bottom(cm)] [float] NULL,
[Cavity Width(cm)] [float] NULL,
[# Hatched] [float] NULL,
[# Live pipped] [float] NULL,
[# Dead pipped] [float] NULL,
[# Eggs Destroyed] [float] NULL,
[# Unhatched] [float] NULL,
[Total Eggs] [float] NULL,
[Overall Comments] [nvarchar](255) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[FWC Diso #] [nvarchar](50) NULL,
NEW...
[NestLocationID] [int] IDENTITY(1,1) Primary Key NOT NULL,
[NestLocationName] [nvarchar](50) NULL, --calculated field
[OriginalNestID] [int] NOT NULL,
[IsNest] [Bit] NOT NULL,
[UnmarkedNest] [Bit] Default(0) NOT NULL,
[FTNumberSONumber] [int] NULL,
[NestLocationDate] DATETIME NOT NULL,
[ObserverID] [int] NOT NULL,
[Observer2ID] [int] NOT NULL,
[Observer3ID] [int] NOT NULL,
[Observer4ID] [int] NOT NULL,
[NestFCNumber] [nvarchar](50) NULL,
[KeyID] [int] NOT NULL,
[ZoneID] [int] NOT NULL,
[DnrMonumentNumberID] [int] NULL,
[Address] [nvarchar](50) NULL,
[CountyID] [int] NOT NULL,
[CondoHotelParkID] [int] Default(0) NOT NULL,
[SpeciesID] [int] NOT NULL,
[NestLatitude] [FLOAT] NULL,
[NestLongitude] [FLOAT] NULL,
[BeachCategoryID] [int] NULL,
[InitialSurface2Eggs] [nvarchar](50) NULL,
[NneNumberBodyPits] [nvarchar](50) NULL,
[NneNumberAbandonCavity] [nvarchar](50) NULL,
[Obstructions] [nvarchar](50) NULL,
[EscarpmentTypeID] [int] NULL,
[DistMHW_FT] [int] NULL,
[DistBarrier_FT] [int] NULL,
[HeightOfEscarpment_FT] [int] NULL,
[LengthOfEscarpment_FT] [int] NULL,
[NestLocationComments] [nvarchar](500) NULL,
[Active] [bit] DEFAULT(1) NOT NULL
OLD...
[Emergence No#] [nvarchar](12) NULL,
[Key] [nvarchar](4) NULL,
[Date] [date] NULL,
[Observer] [nvarchar](100) NULL,
[Zone] [nvarchar](6) NULL,
[Species] [nvarchar](4) NULL,
[Nest] [nvarchar](2) NULL,
[Verified] [nvarchar](1) NULL,
[Unmarked Nest] [bit] NOT NULL,
[Initial Surface2Eggs(cm)] [int] NULL,
[Nest/FC No#] [nvarchar](25) NULL,
[Dist from MHW(ft)] [int] NULL,
[Dist from Barrier(ft)] [int] NULL,
[NNE - # Body Pits] [int] NULL,
[NNE - # Abandon Cavity] [int] NULL,
[Address] [nvarchar](52) NULL,
[Condo/Resort/Park Name] [nvarchar](50) NULL,
[DNR Monument #] [int] NULL,
[Ft# So#] [int] NULL,
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[Beach Category] [nvarchar](13) NULL,
[Obstructions] [nvarchar](250) NULL,
[Type Esc(Sloped/Vertical)] [nvarchar](15) NULL,
[Crawled Over Esc] [nvarchar](50) NULL,
[Height of Esc(ft)] [nvarchar](5) NULL,
[Length of Esc(ft)] [nvarchar](5) NULL,
[PIT Tag #] [nvarchar](50) NULL,
[LFF Tag #] [nvarchar](50) NULL,
[RFF Tag #] [nvarchar](50) NULL,
[Turtle Number] [int] NULL,
[Comments] [nvarchar](225) NULL,
[Caged/Screened] [nvarchar](3) NULL,
[Date Caged/Screen] [date] NULL,
[Type Cage/Screen] [nvarchar](3) NULL,
[Relocated] [nvarchar](3) NULL,
[Date Relocated] [date] NULL,
[Relocator] [nvarchar](100) NULL,
[Why Moved] [nvarchar](32) NULL,
[# Eggs Moved] [float] NULL,
[# Eggs Relocator Broke] [float] NULL,
[New Location] [nvarchar](33) NULL,
[Relocated Latitude] [float] NULL,
[Relocated Longitude] [float] NULL,
[Initial Treatment] [nvarchar](1) NULL,
[Final Treatment] [nvarchar](1) NULL,
[Predated] [nvarchar](1) NULL,
[Date(s) Predated] [nvarchar](250) NULL,
[Predator(s)] [nvarchar](250) NULL,
[Root Damage (0 if none)] [nvarchar](20) NULL,
[# Eggs Damaged by Pred] [float] NULL,
[Vandalized] [nvarchar](3) NULL,
[Date(s) Vandalized] [nvarchar](25) NULL,
[Type of Vandalism] [nvarchar](25) NULL,
[Poached] [nvarchar](3) NULL,
[Date(s) Poached] [nvarchar](25) NULL,
[Were Eggs Removed] [nvarchar](3) NULL,
[Nest Washed Over] [nvarchar](3) NULL,
[Date(s) Washed Over] [nvarchar](255) NULL,
[Storm Name Washed Over] [nvarchar](25) NULL,
[Nest Washed Out] [nvarchar](3) NULL,
[Date(s) Washed Out] [nvarchar](25) NULL,
[Storm Name Washed Out] [nvarchar](25) NULL,
[Drop Date] [date] NULL,
[Date Hatched] [date] NULL,
[Add_l Hatch Date(s)] [nvarchar](10) NULL,
[Disorientation Date(s)] [nvarchar](15) NULL,
[Date Excavated] [date] NULL,
[Excavated By] [nvarchar](100) NULL,
[Live in Nest] [float] NULL,
[Dead in Nest] [float] NULL,
[Final Surface to Eggs(cm)] [float] NULL,
[Depth to Bottom(cm)] [float] NULL,
[Cavity Width(cm)] [float] NULL,
[# Hatched] [float] NULL,
[# Live pipped] [float] NULL,
[# Dead pipped] [float] NULL,
[# Eggs Destroyed] [float] NULL,
[# Unhatched] [float] NULL,
[Total Eggs] [float] NULL,
[Overall Comments] [nvarchar](255) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[FWC Diso #] [nvarchar](50) NULL,
NEW...
[NestLocationID] [int] IDENTITY(1,1) Primary Key NOT NULL,
[NestLocationName] [nvarchar](50) NULL, --calculated field
[OriginalNestID] [int] NOT NULL,
[IsNest] [Bit] NOT NULL,
[UnmarkedNest] [Bit] Default(0) NOT NULL,
[FTNumberSONumber] [int] NULL,
[NestLocationDate] DATETIME NOT NULL,
[ObserverID] [int] NOT NULL,
[Observer2ID] [int] NOT NULL,
[Observer3ID] [int] NOT NULL,
[Observer4ID] [int] NOT NULL,
[NestFCNumber] [nvarchar](50) NULL,
[KeyID] [int] NOT NULL,
[ZoneID] [int] NOT NULL,
[DnrMonumentNumberID] [int] NULL,
[Address] [nvarchar](50) NULL,
[CountyID] [int] NOT NULL,
[CondoHotelParkID] [int] Default(0) NOT NULL,
[SpeciesID] [int] NOT NULL,
[NestLatitude] [FLOAT] NULL,
[NestLongitude] [FLOAT] NULL,
[BeachCategoryID] [int] NULL,
[InitialSurface2Eggs] [nvarchar](50) NULL,
[NneNumberBodyPits] [nvarchar](50) NULL,
[NneNumberAbandonCavity] [nvarchar](50) NULL,
[Obstructions] [nvarchar](50) NULL,
[EscarpmentTypeID] [int] NULL,
[DistMHW_FT] [int] NULL,
[DistBarrier_FT] [int] NULL,
[HeightOfEscarpment_FT] [int] NULL,
[LengthOfEscarpment_FT] [int] NULL,
[NestLocationComments] [nvarchar](500) NULL,
[Active] [bit] DEFAULT(1) NOT NULL
ASKER
Any help would be appreciated.
ASKER
Is there a way to change this line so we test for NULL and return a 1
(Select [Mote_BeachCategory].[Beac hCategoryI D] FROM [Mote_BeachCategory] WHERE [Mote_BeachCategory].[Beac hCategoryN ame] = [V1Nest].[Beach Category]) As 'BeachCategoryID',
(Select [Mote_BeachCategory].[Beac
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
If it's some kind of text, use CAST('3.5' as numeric(19,4))
Int = Integer = Number with zero decimal places.
3.5 = one decimal place. fyi.
btw Length of Esc(ft) is not a good name for a column, as the parentheses marks forces you to use square brackets [ ] to refer to it in T-SQL.