Error converting varchar field to float

0

I am generating a Query and in this Query I am doing a join between a varchar field and a float field.

Since the field SY.YE_MOEDA is varchar and the DA1_A1_MOEDA field is float .

Follow my Query:

SELECT
DA.DA1_CODTAB  AS TABELA,
DA.DA1_CODPRO  AS CODPRODUTO,
SB.B1_DESC     AS DESCRICAO,
SB.B1_PRV1     AS PRECOVENDA,
DA.DA1_MOEDA   AS MOEDA,
SY.YE_MOEDA    AS NOMEMOEDA
FROM DA1010 AS DA
INNER JOIN SB1010 AS SB WITH (NOLOCK) ON SB.B1_COD = DA.DA1_CODPRO
INNER JOIN SYE010 AS SY WITH (NOLOCK) ON CONVERT(FLOAT, ISNULL(SY.YE_MOEDA,0)) = DA.DA1_MOEDA
WHERE DA.D_E_L_E_T_ <> '*' AND DA.DA1_ATIVO = '1'

Follow the error

  

Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar   to float.

    
asked by anonymous 11.11.2017 / 18:07

1 answer

0

In oracle there is a trick to doing this conversion:

select meuCampo + 0.0 as comoFloat from minhaTabela

I suppose it works in sql serves.

Another solution is to switch the convert to decimal, with:

SELECT
DA.DA1_CODTAB  AS TABELA,
DA.DA1_CODPRO  AS CODPRODUTO,
SB.B1_DESC     AS DESCRICAO,
SB.B1_PRV1     AS PRECOVENDA,
DA.DA1_MOEDA   AS MOEDA,
SY.YE_MOEDA    AS NOMEMOEDA
FROM DA1010 AS DA
INNER JOIN SB1010 AS SB WITH (NOLOCK) ON SB.B1_COD = DA.DA1_CODPRO
INNER JOIN SYE010 AS SY WITH (NOLOCK) ON CONVERT(DECIMAL, ISNULL(SY.YE_MOEDA,0)) = DA.DA1_MOEDA
WHERE DA.D_E_L_E_T_ <> '*' AND DA.DA1_ATIVO = '1'
    
13.11.2017 / 11:16