Erro ao converter dados: Error converting data type varchar to numeric.

Question

Estou com a consulta abaixo e para alguns registros ocorre o erro :  Error converting data type varchar to numeric.

SELECT DI.NOME AS DISCIPLINA,
	ISNULL(MAX(ANO1.NOTA), '...') AS NOTA1, ISNULL(CAST(CAST(MAX(ANO1.CH) AS DECIMAL(15,0)) AS VARCHAR), '...') AS CH1,
	ISNULL(MAX(ANO2.NOTA), '...') AS NOTA2, ISNULL(CAST(CAST(MAX(ANO2.CH) AS DECIMAL(15,0)) AS VARCHAR), '...') AS CH2,
	ISNULL(MAX(ANO3.NOTA), '...') AS NOTA3, ISNULL(CAST(CAST(MAX(ANO3.CH) AS DECIMAL(15,0)) AS VARCHAR), '...') AS CH3,
	ISNULL(MAX(ANO4.NOTA), '...') AS NOTA4, ISNULL(CAST(CAST(MAX(ANO4.CH) AS DECIMAL(15,0)) AS VARCHAR), '...') AS CH4,
	ISNULL(MAX(ANO5.NOTA), '...') AS NOTA5, ISNULL(CAST(CAST(MAX(ANO5.CH) AS DECIMAL(15,0)) AS VARCHAR), '...') AS CH5,
	ISNULL(MAX(ANO6.NOTA), '...') AS NOTA6, ISNULL(CAST(CAST(MAX(ANO6.CH) AS DECIMAL(15,0)) AS VARCHAR), '...') AS CH6,
	ISNULL(MAX(ANO7.NOTA), '...') AS NOTA7, ISNULL(CAST(CAST(MAX(ANO7.CH) AS DECIMAL(15,0)) AS VARCHAR), '...') AS CH7,
	ISNULL(MAX(ANO8.NOTA), '...') AS NOTA8, ISNULL(CAST(CAST(MAX(ANO8.CH) AS DECIMAL(15,0)) AS VARCHAR), '...') AS CH8
FROM SHISTDISCCOL SD (NOLOCK)
INNER JOIN SDISCIPLINAHIST DI (NOLOCK) ON SD.CODCOLIGADA = DI.CODCOLIGADA AND SD.CODDISCHIST = DI.CODDISCHIST
LEFT JOIN (
	SELECT HC.CODCOLIGADA, HC.CODCURSOHIST, HC.CODDISCHIST, HC.NOTA, HC.RA, HC.CARGAHORARIA AS CH
	FROM SHISTDISCCOL HC (NOLOCK)
	WHERE HC.CODSERIEHIST = 1
) AS ANO1 ON ANO1.CODCOLIGADA = SD.CODCOLIGADA AND ANO1.CODCURSOHIST = SD.CODCURSOHIST AND ANO1.CODDISCHIST = SD.CODDISCHIST AND ANO1.RA = SD.RA
LEFT JOIN (
	SELECT HC.CODCOLIGADA, HC.CODCURSOHIST, HC.CODDISCHIST, HC.NOTA, HC.RA, HC.CARGAHORARIA AS CH
	FROM SHISTDISCCOL HC (NOLOCK)
	WHERE HC.CODSERIEHIST = 2
) AS ANO2 ON ANO2.CODCOLIGADA = SD.CODCOLIGADA AND ANO2.CODCURSOHIST = SD.CODCURSOHIST AND ANO2.CODDISCHIST = SD.CODDISCHIST AND ANO2.RA = SD.RA
LEFT JOIN (
	SELECT HC.CODCOLIGADA, HC.CODCURSOHIST, HC.CODDISCHIST, HC.NOTA, HC.RA, HC.CARGAHORARIA AS CH
	FROM SHISTDISCCOL HC (NOLOCK)
	WHERE HC.CODSERIEHIST = 3
) AS ANO3 ON ANO3.CODCOLIGADA = SD.CODCOLIGADA AND ANO3.CODCURSOHIST = SD.CODCURSOHIST AND ANO3.CODDISCHIST = SD.CODDISCHIST AND ANO3.RA = SD.RA
LEFT JOIN (
	SELECT HC.CODCOLIGADA, HC.CODCURSOHIST, HC.CODDISCHIST, HC.NOTA, HC.RA, HC.CARGAHORARIA AS CH
	FROM SHISTDISCCOL HC (NOLOCK)
	WHERE HC.CODSERIEHIST = 4
) AS ANO4 ON ANO4.CODCOLIGADA = SD.CODCOLIGADA AND ANO4.CODCURSOHIST = SD.CODCURSOHIST AND ANO4.CODDISCHIST = SD.CODDISCHIST AND ANO4.RA = SD.RA
LEFT JOIN (
	SELECT HC.CODCOLIGADA, HC.CODCURSOHIST, HC.CODDISCHIST, HC.NOTA, HC.RA, HC.CARGAHORARIA AS CH
	FROM SHISTDISCCOL HC (NOLOCK)
	WHERE HC.CODSERIEHIST = 5
) AS ANO5 ON ANO5.CODCOLIGADA = SD.CODCOLIGADA AND ANO5.CODCURSOHIST = SD.CODCURSOHIST AND ANO5.CODDISCHIST = SD.CODDISCHIST AND ANO5.RA = SD.RA
LEFT JOIN (
	SELECT HC.CODCOLIGADA, HC.CODCURSOHIST, HC.CODDISCHIST, HC.NOTA, HC.RA, HC.CARGAHORARIA AS CH
	FROM SHISTDISCCOL HC (NOLOCK)
	WHERE HC.CODSERIEHIST = 6
) AS ANO6 ON ANO6.CODCOLIGADA = SD.CODCOLIGADA AND ANO6.CODCURSOHIST = SD.CODCURSOHIST AND ANO6.CODDISCHIST = SD.CODDISCHIST AND ANO6.RA = SD.RA
LEFT JOIN (
	SELECT HC.CODCOLIGADA, HC.CODCURSOHIST, HC.CODDISCHIST, HC.NOTA, HC.RA, HC.CARGAHORARIA AS CH
	FROM SHISTDISCCOL HC (NOLOCK)
	WHERE HC.CODSERIEHIST = 7
) AS ANO7 ON ANO7.CODCOLIGADA = SD.CODCOLIGADA AND ANO7.CODCURSOHIST = SD.CODCURSOHIST AND ANO7.CODDISCHIST = SD.CODDISCHIST AND ANO7.RA = SD.RA
LEFT JOIN (
	SELECT HC.CODCOLIGADA, HC.CODCURSOHIST, HC.CODDISCHIST, HC.NOTA, HC.RA, HC.CARGAHORARIA AS CH
	FROM SHISTDISCCOL HC (NOLOCK)
	WHERE HC.CODSERIEHIST = 8
) AS ANO8 ON ANO8.CODCOLIGADA = SD.CODCOLIGADA AND ANO8.CODCURSOHIST = SD.CODCURSOHIST AND ANO8.CODDISCHIST = SD.CODDISCHIST AND ANO8.RA = SD.RA
WHERE SD.CODCOLIGADA = :CODCOLIGADA
AND SD.CODCURSOHIST = 10
AND SD.RA = :RA
GROUP BY DI.NOME
ORDER BY DI.NOME

como posso arrumar este erro?

Obrigado.

Answer 1

Chédisson,

Nesses casos de erro de conversão, o ideal é ir comentando cada JOIN/LEFT JOIN( e seus respectivos campos no SELECT), afim de identificar em qual parte da query ele está retornando o erro.

Abraços.

Answer 2

Nesta consulta, com o determinado registro de aluno a ser pesquisado, o mesmo não só possui notas em números, também possui por conceito.

Será que isso pode ocasionar o erro?

Qual seria a solução para que a consulta aceite qualquer tipo de dado?

Obrigado.

Answer 3

Bom dia,

Acho que neste caso você deve remover os Casts nas colunas CH, pois nem sempre será possível fazer a conversão para Decimal:

SELECT DI.NOME AS DISCIPLINA,
	ISNULL(MAX(ANO1.NOTA), '...') AS NOTA1, ISNULL(MAX(ANO1.CH), '...') AS CH1,
	ISNULL(MAX(ANO2.NOTA), '...') AS NOTA2, ISNULL(MAX(ANO2.CH), '...') AS CH2,
	ISNULL(MAX(ANO3.NOTA), '...') AS NOTA3, ISNULL(MAX(ANO3.CH), '...') AS CH3,
	ISNULL(MAX(ANO4.NOTA), '...') AS NOTA4, ISNULL(MAX(ANO4.CH), '...') AS CH4,
	ISNULL(MAX(ANO5.NOTA), '...') AS NOTA5, ISNULL(MAX(ANO5.CH), '...') AS CH5,
	ISNULL(MAX(ANO6.NOTA), '...') AS NOTA6, ISNULL(MAX(ANO6.CH), '...') AS CH6,
	ISNULL(MAX(ANO7.NOTA), '...') AS NOTA7, ISNULL(MAX(ANO7.CH), '...') AS CH7,
	ISNULL(MAX(ANO8.NOTA), '...') AS NOTA8, ISNULL(MAX(ANO8.CH), '...') AS CH8

Espero que ajude.

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 4

Bom dia,

Acho que neste caso você deve remover os Casts nas colunas CH, pois nem sempre será possível fazer a conversão para Decimal:

SELECT DI.NOME AS DISCIPLINA,
	ISNULL(MAX(ANO1.NOTA), '...') AS NOTA1, ISNULL(MAX(ANO1.CH), '...') AS CH1,
	ISNULL(MAX(ANO2.NOTA), '...') AS NOTA2, ISNULL(MAX(ANO2.CH), '...') AS CH2,
	ISNULL(MAX(ANO3.NOTA), '...') AS NOTA3, ISNULL(MAX(ANO3.CH), '...') AS CH3,
	ISNULL(MAX(ANO4.NOTA), '...') AS NOTA4, ISNULL(MAX(ANO4.CH), '...') AS CH4,
	ISNULL(MAX(ANO5.NOTA), '...') AS NOTA5, ISNULL(MAX(ANO5.CH), '...') AS CH5,
	ISNULL(MAX(ANO6.NOTA), '...') AS NOTA6, ISNULL(MAX(ANO6.CH), '...') AS CH6,
	ISNULL(MAX(ANO7.NOTA), '...') AS NOTA7, ISNULL(MAX(ANO7.CH), '...') AS CH7,
	ISNULL(MAX(ANO8.NOTA), '...') AS NOTA8, ISNULL(MAX(ANO8.CH), '...') AS CH8

Espero que ajude.

---

Assinatura: http://www.imoveisemexposicao.com.br

Muito obrigado Gapimex, desta forma a consulta trouxe as informações.

Answer 5

Boa tarde, Gapimex.

Agora estou com uma situação parecida, mas ao tentar remover os CASTs, ocorre erro no sql, teria como dar um auxilio no código abaixo?

STURMADISC.CODDISC,
SDISCIPLINA.NOME AS NOMEDISC,
ISNULL(CAST(CAST(NOTA1.NOTAFALTA AS DECIMAL(15,0))AS VARCHAR), '...') AS N1,
ISNULL(CAST(CAST(FALTA1.NOTAFALTA AS DECIMAL(15,0))AS VARCHAR), '...') AS F1,
ISNULL(CAST(CAST(NOTA2.NOTAFALTA AS DECIMAL(15,0)) AS VARCHAR), '...') AS N2,
ISNULL(CAST(CAST(FALTA2.NOTAFALTA AS DECIMAL(15,0)) AS VARCHAR), '...') AS F2,
ISNULL(CAST(CAST(NOTA3.NOTAFALTA AS DECIMAL(15,0)) AS VARCHAR), '...') AS N3,
ISNULL(CAST(CAST(FALTA3.NOTAFALTA AS DECIMAL(15,0)) AS VARCHAR), '...') AS F3,
ISNULL(CAST(CAST(MA.NOTAFALTA AS DECIMAL(15,0)) AS VARCHAR), '...') AS MA,
ISNULL(CAST(CAST(EC.NOTAFALTA AS DECIMAL(15,0)) AS VARCHAR), '...') AS EC,
ISNULL(CAST(CAST(MF.NOTAFALTA AS DECIMAL(15,0)) AS VARCHAR), '...') AS MF,
ISNULL(CAST(CAST(TAULAS.AULASDADAS AS DECIMAL(15,0)) AS VARCHAR), '...') AS TAULAS,
ISNULL(CAST(CAST(MFF.NOTAFALTA AS DECIMAL(15,0)) AS VARCHAR), '...') AS MFF,
SSTATUS.DESCRICAO AS RESULTADO,
CAST(SDISCGRADE.CH AS DECIMAL(15,0)) AS CH

FROM
SMATRICPL (NOLOCK)

Obrigado.