Database query error - invalid number

Question

Database query error - invalid number

Navigation

#1 by (1 votes)

1

Good afternoon. I am having trouble in a database query. Performing this 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 help me? Thank you.

sql oracle

asked by anonymous 05.07.2018 / 18:33

1 answer

I am not able to complete the merge this error always appears: Mobile menu: Click to open and then close

score 1 · Accepted Answer

Making JOIN with BETWEEN is not a good idea. It is best to move BETWEEN to 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