CODEWITHSUNDEEP
sqlsql-server
Phil001
Phil001

Reputation: 23

select fails when converting data type varchar to numeric

My select is to insert from a table 1 to table 2 and when I run it I get the following message:

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

the columns that I am converting from varchar to numeric are:

If I eliminate these columns from the select it works fine.

This is the actual select:

INSERT INTO SICAVS1_Transacciones_con_ISIN 
(tipo_operacion, fecha, cod_operacion,
 nombre, titulos, cambio, liquido,
 resultado, ISIN )
SELECT DISTINCT st.tipo_operacion
              , st.fecha
              , st.cod_operacion
              , st.nombre
              , cast(st.titulos as DECIMAL(16,2))
              , cast(st.cambio as DECIMAL(16,2))
              , cast(st.liquido as DECIMAL(16,2))
              , cast(st.resultado as DECIMAL(16,2))
              , st.ISIN 
FROM temp_Transacciones st WHERE NOT EXISTS
(SELECT 1 
  FROM SICAVS1_Transacciones t2
  WHERE t2.tipo_operacion = st.tipo_operacion 
    AND t2.fecha = st.fecha
    AND t2.cod_operacion = st.cod_operacion
    AND t2.nombre = st.nombre
    AND t2.ISIN = st.ISIN)

And this is the table scheme of SICAVS1_transacciones_con_ISIN[dbo].[SICAVS1_Transacciones_con_ISIN]

[ID] [int] IDENTITY(1,1) NOT NULL,
[tipo_operacion] [varchar](30) NULL,
[fecha] [varchar](10) NULL,
[cod_operacion] [varchar](6) NULL,
[nombre] [varchar](32) NULL,
[titulos] [decimal](16, 2) NULL,
[cambio] [decimal](16, 2) NULL,
[liquido] [decimal](16, 2) NULL,
[resultado] [decimal](16, 2) NULL,
[ISIN] [varchar](20) NULL,
[fecha_valor] [date] NULL,
[type] [varchar](14) NULL,
[categoria_1] [char](35) NULL,
CONSTRAINT [PK__BNP_SICA__3214EC27DA21ECEF] PRIMARY KEY CLUSTERED 
(
  [ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Upvotes: 0

Views: 876

Answers (2)

Code Different
Code Different

Reputation: 93161

One of these rows aren't numeric, you have to eliminate them. To find them:

SELECT DISTINCT st.tipo_operacion
              , st.fecha
              , st.cod_operacion
              , st.nombre
              , st.titulos
              , st.cambio
              , st.liquido
              , st.resultado
              , st.ISIN 
FROM temp_Transacciones st WHERE NOT EXISTS
(SELECT 1 
  FROM SICAVS1_Transacciones t2
  WHERE t2.tipo_operacion = st.tipo_operacion 
    AND t2.fecha = st.fecha
    AND t2.cod_operacion = st.cod_operacion
    AND t2.nombre = st.nombre
    AND t2.ISIN = st.ISIN)
WHERE ISNUMERIC(st.titulos) = 0
    OR ISNUMERIC(st.cambio) = 0
    OR ISNUMERIC(st.liquido) = 0
    OR ISNUMERIC(st.resultado) = 0

Note that this only find them. It won't help you resolve the issues by itself. There's no way to convert abc123 to a number.

Upvotes: 0

Abhishek Mittal
Abhishek Mittal

Reputation: 366

SQL Server 2012 and Later

Just use Try_Convert instead:

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

Read more about Try_Convert.

Upvotes: 0

Related Questions