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

  • Pergunta

  • 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.

    quarta-feira, 23 de janeiro de 2013 13:20

Respostas

  • 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


    • Editado gapimex quarta-feira, 23 de janeiro de 2013 14:09
    • Marcado como Resposta Chédisson Soares quarta-feira, 23 de janeiro de 2013 14:51
    quarta-feira, 23 de janeiro de 2013 14:08

Todas as Respostas

  • 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.


    quarta-feira, 23 de janeiro de 2013 13:36
  • 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.

    quarta-feira, 23 de janeiro de 2013 13:41
  • 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


    • Editado gapimex quarta-feira, 23 de janeiro de 2013 14:09
    • Marcado como Resposta Chédisson Soares quarta-feira, 23 de janeiro de 2013 14:51
    quarta-feira, 23 de janeiro de 2013 14:08
  • 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.
    quarta-feira, 23 de janeiro de 2013 14:52
  • 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.

    terça-feira, 5 de fevereiro de 2013 18:05