Блог

Главная - Вопросы по программированию - Как выполнить NVL для NUMTODSINTERVAL

Как выполнить NVL для NUMTODSINTERVAL

#sql #oracle

#sql #Oracle

Вопрос:

 SELECT NUMTODSINTERVAL(SUM(EXTRACT(HOUR FROM time1)), 'HOUR')  
   NUMTODSINTERVAL(SUM(EXTRACT(MINUTE FROM time1)), 'MINUTE') AS HOURS
   FROM table1 WHERE id1 = '123' AND value1 = 123

Мне нужно выполнить NVL для NUMTODSINTERVAL и преобразовать его в ‘0 00:00:00’, потому что мне нужно суммировать общее количество часов. Однако, когда значение равно НУЛЮ, все результаты равны НУЛЮ. Как мне это сделать?

Ответ №1:

Замените каждое вхождение time1 на :

 NVL(time1,to_date('01/01/0001 00:00:00','dd/mm/yyyy hh24:mi:ss'))

Комментарии:

1. Поддержано, но я сомневаюсь, что он хочет работать с годом 1.

2. Я думал использовать что-то вроде этого: NVL(time1, TO_DSINTERVAL(‘0 00:00:00’))…. но у меня не сработало.

3. @AlvaroRaygada — можете ли вы быть более конкретным, чем «не сработало»? Что произошло? Вы получили сообщение об ошибке?

4. @AlvaroRaygada — возможно, вы получили ORA-00909: недопустимое количество аргументов? Если это так, вам просто нужна дополнительная закрывающая скобка.

5. Спасибо за редактирование @alexpoole , пропустил эту скобку.

Ответ №2:

Вы не можете сделать то, что предложили:

 NVL(time1, TO_DSINTERVAL('0 00:00:00'))

потому что это разные типы данных; time1 это временная метка, а фиксированное значение — интервал. Вместо этого вы могли бы использовать литерал метки времени:

 NVL(time1, TIMESTAMP '1972-01-01 00:00:00')

… но это, по сути, то, что предложил @sagi — просто с литералом метки времени вместо преобразованной строки. Часть даты не имеет значения, это может быть что угодно, главное, чтобы время было полночь.

Вы также можете использовать небольшой вариант; поскольку вам нужно, чтобы количество часов / минут было равно нулю:

 SELECT NUMTODSINTERVAL(SUM(NVL(EXTRACT(HOUR FROM time1), 0)), 'HOUR')  
   NUMTODSINTERVAL(SUM(NVL(EXTRACT(MINUTE FROM time1), 0)), 'MINUTE') AS HOURS
FROM table1 WHERE id1 = '123' AND value1 = 123

Быстрая демонстрация с CTE для предоставления ненулевого значения:

 WITH table1 (id1, value1, time1) AS (
   SELECT '123', 123, TIMESTAMP '2016-01-01 12:34:56.789'
   FROM dual
)
SELECT NUMTODSINTERVAL(SUM(NVL(EXTRACT(HOUR FROM time1), 0)), 'HOUR')  
   NUMTODSINTERVAL(SUM(NVL(EXTRACT(MINUTE FROM time1), 0)), 'MINUTE') AS HOURS
   FROM table1 WHERE id1 = '123' AND value1 = 123;

HOURS
-----------------------------
 000000000 12:34:00.000000000

И с нулевым значением:

 WITH table1 (id1, value1, time1) AS (
   SELECT '123', 123, CAST(null AS TIMESTAMP)
   FROM dual
)
SELECT NUMTODSINTERVAL(SUM(NVL(EXTRACT(HOUR FROM time1), 0)), 'HOUR')  
   NUMTODSINTERVAL(SUM(NVL(EXTRACT(MINUTE FROM time1), 0)), 'MINUTE') AS HOURS
   FROM table1 WHERE id1 = '123' AND value1 = 123;

HOURS
-----------------------------
 000000000 00:00:00.000000000