SQL 2017 : Expression type float is invalid for COLLATE clause

Question

Hi everyone - I am not sure what this error is.

The column [S_ID] in Stage.dw.CY_FCO_S_STBL is navarchar(384) null.

How do solve this, please

Answer 1

Hi @kkran ，

COLLATE is applicable only for columns of the char, varchar, nchar, and nvarchar data types.

It seems that the type of column S_ID from CY_FCO_S table in your Oracle is float.

If two decimal places is accepted for all data in S_ID, then you could just remove COLLATE statement.

Otherwise you have to use convert/cast to change the float type to varchar type with defined format.

Please refer a simple example:

declare @t table  
(a nvarchar(384) )  
  
declare @r table   
(b float )  
  
insert into @r values  
(1234.1234)  
  
insert into @t   
select b from @r  
  
insert into @t   
select CONVERT(varchar(50), b) from @r  
  
insert into @t   
select CONVERT(varchar(50), b,1) from @r  
  
select * from @t  

Output:

a  
1234.12  
1234.12  
1.2341234e+003  

CONVERT(varchar(50), b,1)

As mentioned in this doc, you could also use another number(0,2,3,or 128) instead of 1 to change the format per your requirement as below:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Remove the COLLATE statement - it isn't needed for a float data type. If you want the value to be converted to a string - then use CONVERT/CAST - or use the equivalent function on the Oracle part of the query to convert to an appropriate data type.