ORA-01722: недопустимое число вычитаемых дат

ProgramBox

ORA-01722: недопустимое число вычитаемых дат

Post author:admin
Запись опубликована:22 августа, 2022
Post category:Вопросы по программированию

#sql #oracle

#sql #Oracle

Вопрос:

У меня есть запрос, который я пытаюсь написать / запустить… Я могу заставить его возвращать результаты, если я добавлю фильтры WHERE и добавлю «число строк <100». Я могу продолжать увеличивать это, и это расширит результаты.. Однако, если я удалю его и позволю ему работать полностью, он выдает ошибку oracle sql:

Ошибка базы данных: 511 — ORA-01722: недопустимое число

Любая помощь приветствуется!

 SELECT inventory_view.prtnum, inventory_view.lotnum, inventory_view.expire_dte,     inventory_view.mandte, to_number(inventory_view.expire_dte - inventory_view.mandte) As "DELTA", SUBSTR(inventory_view.age_pflnam, 1, 3) As "Age Profile", to_number((inventory_view.expire_dte - inventory_view.mandte)) - to_number(SUBSTR(inventory_view.age_pflnam, 1, 3)) AS "DELTA 2", lst_arecod, prt_client_id
FROM inventory_view
WHERE inventory_view.prtnum = inventory_view.prtnum
AND lst_arecod <> 'SHIP'
AND prt_client_id = 'TEST'

1. Когда вы вычитаете даты, разница уже является числом, поэтому в to_number нет необходимости. По-видимому, ваша проблема зависит от данных и появляется после первых 99 строк. Изменяя rownum < ограничение, вы должны быть в состоянии найти строку, которая вызывает проблему. Я предполагаю, что некоторые значения inventory_view.age_pflnam не являются числовыми.

2. Спасибо за это… Посмотрел на это поле, чтобы понять, что я делал SUBSTR, чтобы получить первые 3 символа. Ну, есть значения, которые имеют только 1 или 2 значения, а третье — тире. Этот комментарий привел меня прямо к решению, спасибо… Теперь я использую регулярные выражения, чтобы просто захватить все до первого появления дефиса / тире.

Ответ №1:

Вы получаете ошибку, потому что одно из преобразований to_number завершается ошибкой для определенной строки и столбца. Пока вы не нажмете на строку, вы не получите ошибку, которая объясняет, почему and rownum < ... выполняется успешно.

Чтобы определить ошибочные значения, вы можете попробовать следующий запрос, который проверяет значения преобразуемых столбцов на наличие нечисловых значений.

 SELECT 
    inventory_view.prtnum, 
    inventory_view.lotnum, 
    inventory_view.expire_dte,     
    inventory_view.mandte, 
    to_number(inventory_view.expire_dte - inventory_view.mandte) As "DELTA", 
    SUBSTR(inventory_view.age_pflnam, 1, 3) As "Age Profile", 
    to_number((inventory_view.expire_dte - inventory_view.mandte)) - to_number(SUBSTR(inventory_view.age_pflnam, 1, 3)) AS "DELTA 2", 
    lst_arecod, 
    prt_client_id
SELECT inventory_view.*
FROM inventory_view
WHERE lst_arecod <> 'SHIP'
AND prt_client_id = 'TEST'
AND 
(LOWER(inventory_view.expire_dte) <> UPPER(inventory_view.expire_dte)
OR LOWER(inventory_view.mandte) <> UPPER(inventory_view.mandte)
OR LOWER(SUBSTR(inventory_view.age_pflnam, 1, 3)) <> UPPER(SUBSTR(inventory_view.age_pflnam, 1, 3))
);

Кроме того, вы можете исключить эти нечисловые значения в исходном запросе, как показано ниже:

 SELECT 
    inventory_view.prtnum, 
    inventory_view.lotnum, 
    inventory_view.expire_dte,     
    inventory_view.mandte, 
    to_number(inventory_view.expire_dte - inventory_view.mandte) As "DELTA", 
    SUBSTR(inventory_view.age_pflnam, 1, 3) As "Age Profile", 
    to_number((inventory_view.expire_dte - inventory_view.mandte)) - to_number(SUBSTR(inventory_view.age_pflnam, 1, 3)) AS "DELTA 2", 
    lst_arecod, 
    prt_client_id
SELECT inventory_view.*
FROM inventory_view
WHERE lst_arecod <> 'SHIP'
AND prt_client_id = 'TEST'
AND LOWER(inventory_view.expire_dte) = UPPER(inventory_view.expire_dte)
AND LOWER(inventory_view.mandte) = UPPER(inventory_view.mandte)
AND LOWER(SUBSTR(inventory_view.age_pflnam, 1, 3)) = UPPER(SUBSTR(inventory_view.age_pflnam, 1, 3));

Пожалуйста, обратите внимание, что inventory_view.prtnum = inventory_view.prtnum условие было удалено, поскольку оно всегда будет ИСТИННЫМ.

Ответ №2:

Извините, это не прямой ответ на ваш вопрос, но решение моей аналогичной проблемы может дать вам некоторые идеи, если вам все еще нужна помощь. Здесь я вычитаю две даты для отображения прошедших часов. ,to_char(PEH.HOSP_DISCH_TIME-PEH.HOSP_ADMSN_TIME, ‘99.99’) * 24 как LOS_HR Одна учетная запись вызывала ora-01722. Число было слишком большим для «99,99». Изменение на «999,99» сделало свое дело. Однако я все еще озадачен, потому что результат превысил 1000.

Метки: ORA-01722: недопустимое число вычитаемых дат