CASTing error value for FLOAT in SQL Server 2005
I am trying to get a stored procedure to work for a staff member who is sick (and therefore no guidance can be offered).
I have a SQL Server 2005 database with this exact procedure and I am trying to make scripts to convert a test database to match this dev database. My script has multiple lines like:
CAST(RELATIVE_ERROR_RATIO AS FLOAT),
CAST(REPORTING_LIMIT AS FLOAT),
The procedure essentially does "insert into table (all fields) from another table, where field = @input"
When I run the script, I get the error:
CAST or CONVERT: invalid attributes specified for type 'float'
and no procedure is created. But I have compared the original tables in both development and test environments and they match exactly. And the procedure exists exactly the same as the script in the dev environment.
I can't ask my colleague if he had to do any special acrobatics to create this script, so I'm asking you. I did some searches and see that maybe the float should be of the form FLOAT (6,1) (or some such), but that is NOT what it has and I am not comfortable with changing the test environment so that it wonβt really matches dev.
Added
The comment is correct. I was told that the error is related to the following piece:
CAST(TRACER_YIELD AS FLOAT(10,3)),
I can post the whole request, but it's long! So instead, I'll just include the cast margins and the first and last margins. I would like to ask my colleague if this field was a bug and he just needed a straight throw. He'll be back on Monday, so it might take a long time.
CREATE PROCEDURE [dbo].[our_LOAD_INPUT]
@ourNUMBER INT
AS
INSERT INTO our_FILE (our_NUMBER,
DILUTION_FACTOR,
DISTILLATION_VOLUME,
MAXIMUM_CONTROL_LIMIT,
MDA,
MINIMUM_CONTROL_LIMIT,
NUMBER_OF_TICS_FOUND,
PERCENT_MOISTURE,
PERCENT_RECOVERY,
PERCENT_SOLIDS,
RELATIVE_ERROR_RATIO,
REPORTING_LIMIT,
REQUIRED_DETECTION_LIMIT,
RER_MAX,
RESULT,
RETENTION_TIME,
RPD,
RPD_MAXIMUM,
SAMPLE_ALIQUOT_SIZE,
SPIKE_CONCENTRATION,
TOTAL_PROPAGATED_UNCERTAINTY,
TRACER_YIELD,
TWO_SIGMA_COUNTING_ERROR,
VERSION)
SELECT FILE_NUMBER,
CAST(DILUTION_FACTOR AS FLOAT),
CAST(DISTILLATION_VOLUME AS FLOAT),
CAST(MAXIMUM_CONTROL_LIMIT AS FLOAT),
CAST(MDA AS FLOAT),
CAST(MINIMUM_CONTROL_LIMIT AS FLOAT),
CAST(NUMBER_OF_TICS_FOUND AS FLOAT),
CAST(PERCENT_MOISTURE AS FLOAT),
CAST(PERCENT_RECOVERY AS FLOAT),
CAST(PERCENT_SOLIDS AS FLOAT),
CAST(RELATIVE_ERROR_RATIO AS FLOAT),
CAST(REPORTING_LIMIT AS FLOAT),
CAST(REQUIRED_DETECTION_LIMIT AS FLOAT),
CAST(RER_MAX AS FLOAT),
CAST(RESULT AS FLOAT),
CAST(RETENTION_TIME AS FLOAT),
CAST(RPD AS FLOAT),
CAST(RPD_MAXIMUM AS FLOAT),
CAST(SAMPLE_ALIQUOT_SIZE AS FLOAT),
CAST(SPIKE_CONCENTRATION AS FLOAT),
CAST(TOTAL_PROPAGATED_UNCERTAINTY AS FLOAT),
CAST(TRACER_YIELD AS FLOAT(10,3)),
CAST(TWO_SIGMA_COUNTING_ERROR AS FLOAT),
VERSION
FROM our_FILE_CHAR
WHERE our_NUMBER = @ourNUMBER
GO
our_File_CHAR is defined as
CREATE TABLE [dbo].[our_FILE_CHAR]
(
[our_NUMBER] [int] NOT NULL,
[DILUTION_FACTOR] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DISTILLATION_VOLUME] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MAXIMUM_CONTROL_LIMIT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MDA] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MINIMUM_CONTROL_LIMIT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NUMBER_OF_TICS_FOUND] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PERCENT_MOISTURE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PERCENT_RECOVERY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PERCENT_SOLIDS] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RELATIVE_ERROR_RATIO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REPORTING_LIMIT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REQUIRED_DETECTION_LIMIT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RER_MAX] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RESULT] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RETENTION_TIME] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RPD] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RPD_MAXIMUM] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SAMPLE_ALIQUOT_SIZE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SPIKE_CONCENTRATION] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TOTAL_PROPAGATED_UNCERTAINTY] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TRACER_YIELD] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TWO_SIGMA_COUNTING_ERROR] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VERSION] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
our_File is defined as
CREATE TABLE [dbo].[our_FILE]
(
[our_NUMBER] [int] NOT NULL,
[DILUTION_FACTOR] [numeric] (10, 3) NULL,
[DISTILLATION_VOLUME] [numeric] (5, 1) NULL,
[MAXIMUM_CONTROL_LIMIT] [numeric] (10, 3) NULL,
[MDA] [numeric] (10, 3) NULL,
[MINIMUM_CONTROL_LIMIT] [numeric] (10, 3) NULL,
[NUMBER_OF_TICS_FOUND] [numeric] (2, 0) NULL,
[PERCENT_MOISTURE] [numeric] (5, 1) NULL,
[PERCENT_RECOVERY] [numeric] (10, 3) NULL,
[PERCENT_SOLIDS] [numeric] (5, 1) NULL,
[RELATIVE_ERROR_RATIO] [numeric] (10, 3) NULL,
[REPORTING_LIMIT] [numeric] (10, 2) NULL,
[REQUIRED_DETECTION_LIMIT] [numeric] (10, 2) NULL,
[RER_MAX] [numeric] (10, 3) NULL,
[RESULT] [numeric] (13, 3) NULL,
[RETENTION_TIME] [numeric] (6, 2) NULL,
[RPD] [numeric] (10, 3) NULL,
[RPD_MAXIMUM] [numeric] (10, 3) NULL,
[SAMPLE_ALIQUOT_SIZE] [numeric] (10, 3) NULL,
[SPIKE_CONCENTRATION] [numeric] (10, 3) NULL,
[TOTAL_PROPAGATED_UNCERTAINTY] [numeric] (13, 3) NULL,
[TRACER_YIELD] [numeric] (10, 3) NULL,
[TWO_SIGMA_COUNTING_ERROR] [numeric] (10, 3) NULL,
[VERSION] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
source to share
(I can't accept my own answer, but this was the solution that worked for me.)
If I change one line of violation from
CAST(TRACER_YIELD AS FLOAT(10,3)),
to
CAST(TRACER_YIELD AS FLOAT),
it works.
Will it work correctly?
Update: This was a mistake by the original programmer. So the change to not specify decimal places (as shown above) is what he intended and should work as expected.
source to share
Hmmm why bother applying at all, varchar to numeric has an implicit conversion if the data is correct (I assume you are checking the cleanup process to make sure the data is numeric before trying to push it into production, even the cast will fail if someone will put character data in the field).
source to share