Link to home
Start Free TrialLog in
Avatar of MBoy2
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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

For starters, what is the data type of [Length of Esc(ft)]?
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.
Avatar of MBoy2
MBoy2

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_GetAllV1NestData]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Mote_GetAllV1NestData];
GO
CREATE PROCEDURE [dbo].[Mote_GetAllV1NestData]
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].[ObserverID] FROM [Mote_Observer] WHERE [Mote_Observer].[ObserverName] = [V1Nest].[Observer]) AS ObserverID,
      (Select [Mote_Observer].[ObserverID] FROM [Mote_Observer] WHERE [Mote_Observer].[ObserverName] = 'Unknown Observer') AS Observer2ID,
      (Select [Mote_Observer].[ObserverID] FROM [Mote_Observer] WHERE [Mote_Observer].[ObserverName] = 'Unknown Observer') AS Observer3ID,
      (Select [Mote_Observer].[ObserverID] FROM [Mote_Observer] WHERE [Mote_Observer].[ObserverName] = '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].[DnrMonumentNumberID] FROM [Mote_DnrMonumentNumber] WHERE [Mote_DnrMonumentNumber].[DnrMonumentNumber] = [V1Nest].[DNR Monument #]) AS DnrMonumentNumberID,
      [Address],
      Select [Mote_County].[CountyID] FROM [Mote_County] WHERE [Mote_County].[CountyName] = 'Unknown County') AS ObserverID,
      (Select [Mote_CondoHotelPark].[CondoHotelParkID] FROM [Mote_CondoHotelPark] WHERE [Mote_CondoHotelPark].[CondoHotelParkName] = [V1Nest].[Condo/Resort/Park  Name]) AS CondoHotelParkID,
      (Select [Mote_Species].[SpeciesID] FROM [Mote_Species] WHERE [Mote_Species].[SpeciesName] = [V1Nest].[Species]) AS SpeciesID,
      [Latitude] As 'NestLatitude',
      [Longitude] As 'NestLongitude',
      (Select [Mote_BeachCategory].[BeachCategoryID] FROM [Mote_BeachCategory] WHERE [Mote_BeachCategory].[BeachCategoryName] = [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].[EscarpmentTypeID] FROM [Mote_EscarpmentType] WHERE [Mote_EscarpmentType].[EscarpmentTypeName] = [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
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
Avatar of MBoy2

ASKER

Now I get...

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.
Avatar of MBoy2

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
Avatar of MBoy2

ASKER

Any help would be appreciated.
Avatar of MBoy2

ASKER

Is there a way to change this line so we test for NULL and return a 1

(Select [Mote_BeachCategory].[BeachCategoryID] FROM [Mote_BeachCategory] WHERE [Mote_BeachCategory].[BeachCategoryName] = [V1Nest].[Beach Category]) As 'BeachCategoryID',
ASKER CERTIFIED SOLUTION
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MBoy2

ASKER

Thanks