Database query error - invalid number

Asked

Viewed 1,123 times

1

Good afternoon. I’m having trouble with a database query. When performing it, returns this error:

ORA-01722: número inválido
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

My code:

SELECT T2.ID_EMPRESA, T2.ID_EQUIPAMENTO, T1.ID_OBRA, T1.ID_ATIVIDADE, SUM(T1.KM_FINAL - T1.KM_INICIAL) AS DIFERENCA_KMS
FROM CE_REGISTRO_LEVES T1
INNER JOIN CE_BOLETIM T2 ON T2.ID = T1.ID_BOLETIM AND T2.DATA BETWEEN TO_DATE('01/06/2018', 'DD/MM/YYYY') AND TO_DATE('30/06/2018', 'DD/MM/YYYY')
WHERE CASE WHEN T1.ID_OBRA = '82' AND T1.ID_ATIVIDADE = '12' THEN 0 ELSE 1 END = 1
GROUP BY T2.ID_EMPRESA, T2.ID_EQUIPAMENTO, T1.ID_OBRA, T1.ID_ATIVIDADE
ORDER BY T2.ID_EQUIPAMENTO

Can anyone assist me? Thank you.

sql oracle
  • 2

    Are you sure that KM_FINAL and KM_INICIAL are numeric fields? I think that’s the problem.

  • It would be easier to answer your question more accurately if you inform how tables are modeled CE_REGISTRO_LEVES and CE_BOLETIM.

1 answer

1


Do JOIN with BETWEEN not a good idea. It’s better to move the BETWEEN to the WHERE. Your CASE also seems to be something more complicated than it should be and can be simplified. I suggest doing this:

SELECT
    T2.ID_EMPRESA,
    T2.ID_EQUIPAMENTO,
    T1.ID_OBRA,
    T1.ID_ATIVIDADE,
    SUM(T1.KM_FINAL - T1.KM_INICIAL) AS DIFERENCA_KMS
FROM
    CE_REGISTRO_LEVES T1
INNER JOIN CE_BOLETIM T2 ON T2.ID = T1.ID_BOLETIM
WHERE T2.DATA BETWEEN TO_DATE('01/06/2018', 'DD/MM/YYYY')
    AND TO_DATE('30/06/2018', 'DD/MM/YYYY')
AND (T1.ID_OBRA <> '82' OR T1.ID_ATIVIDADE <> '12')
GROUP BY T2.ID_EMPRESA, T2.ID_EQUIPAMENTO, T1.ID_OBRA, T1.ID_ATIVIDADE
ORDER BY T2.ID_EQUIPAMENTO

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.