Error using CONVERT

Mitch · December 1st, 2003, 12:39 PM

I have the following T-SQL in SQL2000 that is giving me an error in the CONVERT line:

Code:

SELECT [T_SetupSheetHistoryParameterDetail].WOID,
      [T_SetupSheetHistoryParameterDetail].WOIDSub,
      [T_SetupSheetHistoryParameterDetail].QuoteID,
      [T_SetupSheetHistoryParameterDetail].ECN,
      [T_SetupSheetHistoryParameterDetail].Revision,


 CONVERT(float,
    CASE
        WHEN [ParameterValue] LIKE '%[A-Z]%'
            THEN 0
        WHEN  [ParameterValue] IS NULL
            THEN 0
        ELSE
              [ParameterValue]
    END
  ) AS LineSpedInInchesPerMin  

FROM [T_SetupSheetHistoryParameterDetail]
WHERE [T_SetupSheetHistoryParameterDetail].ParameterID=9;

When I run this, the first [ParameterValue] is '34.2' and it generates the following error:

Code:

 
Syntax error converting the nvarchar value '34.2' to a column of data type int.

The [ParameterValue] can contain text as well as numbers or be blank, so it is a nvarchar 50.

defiant · December 2nd, 2003, 01:48 PM

Maybe it will work if you replace 'THEN 0' with 'THEN 0.0'.

defiant

Mitch · December 2nd, 2003, 02:23 PM

We are getting closser I think. :)

When I change THEN 0 to THEN 0.0 I now get:

Arithmetic overflow error converting numeric to data type numeric.

Which does not make any sense to me.

Mitch · December 2nd, 2003, 03:00 PM

Even if I take out the Convert I still get an error!

(
    CASE
        WHEN [ParameterValue] LIKE '%[A-Z]%'
            THEN 0.0
        WHEN [ParameterValue] = NULL
            THEN 0.0
         ELSE
              [ParameterValue]
    END
  ) AS LineSpedInInchesPerMin

I get:

Arithmetic overflow error converting numeric to data type numeric.

Mitch · December 2nd, 2003, 03:29 PM

I got it to work! :D
But I had to change the 0.0 to '0' like this:

(
    CASE
        WHEN [ParameterValue] LIKE '%[A-Z]%'
            THEN '0'
        WHEN [ParameterValue] = NULL
            THEN '0'
         ELSE
              [ParameterValue]
    END
) AS LineSpedInInchesPerMin
I don't understand why the zeros have to be text.

happygv · December 3rd, 2003, 02:00 AM

It is simple, as you have mentioned earlier in your first post. Since you have removed the Convert() from your sql statement now, you can't return int in first 2 cases and nvarchar in the 3rd case. So '0' is a meaningful one.

Quote:

quote:The [ParameterValue] can contain text as well as numbers or be blank, so it is a nvarchar 50.

Cheers,

-Vijay G

happygv · December 3rd, 2003, 02:12 AM

Now you can try this too.

convert(float,
    CASE
        WHEN [ParameterValue] LIKE '%[A-Z]%'
            THEN '0'
        WHEN [ParameterValue] = NULL
            THEN '0'
         ELSE
              [ParameterValue]
    END
) AS LineSpedInInchesPerMin

Cheers,

-Vijay G

Mitch · April 2nd, 2004, 11:46 AM

Sorry about getting back to this one so late, I got side tracked and forgot about it until I actually came back to do it again and thought this problem seemed familiar.

To reply to Vijay:

Putting CONVERT at the begining still caused an error, I thought it would as well.

This is what finally worked:

Code:

SELECT [T_SetupSheetHistoryParameterDetail].WOID,
      [T_SetupSheetHistoryParameterDetail].WOIDSub,
      [T_SetupSheetHistoryParameterDetail].QuoteID,
      [T_SetupSheetHistoryParameterDetail].ECN,
      [T_SetupSheetHistoryParameterDetail].Revision,
(
    CASE
        WHEN [ParameterValue] LIKE '%[A-Z]%'
            THEN '0'
        WHEN  [ParameterValue] = NULL
            THEN '0'
         ELSE
              [ParameterValue] 
    END 
) AS LineSpedInInchesPerMin  


FROM [T_SetupSheetHistoryParameterDetail]
WHERE [T_SetupSheetHistoryParameterDetail].ParameterID=9

As with before, it works. :)

happygv · April 3rd, 2004, 05:18 AM

Yes, the reason behind it is, there weren't similarity in the datatypes used in your THEN statements.

In your first and foremost post,

CONVERT(float,
    CASE
        WHEN [ParameterValue] LIKE '%[A-Z]%'
            THEN 0 -- Int Type
        WHEN [ParameterValue] IS NULL
            THEN 0 -- Int Type
        ELSE
              [ParameterValue] -- NVARCHAR Type
    END
  ) AS LineSpedInInchesPerMin

We need to maintain similarity in return datatypes for all the 3 cases. Then it doesn't matter using convert() or not using Convert() there, as LineSpedInInchesPerMin cannot be an Int type in one row and a nvarchar type in another.

Cheers,

-Vijay G

Mitch · April 5th, 2004, 02:36 PM

Thanks Vijay