Оператор Coalesce для обработки нескольких значений и NULL?

Я пытаюсь понять, как создать SQL-запрос, который будет проверять параметры (:FROM_DATE) и (:TO_DATE), и если NULL указать даты последнего месяца для двух значений, а если нет NULL, чтобы принять любые значения, введенные в параметры.

Например:

  • если пользователь вводит (01-JAN-17) как FROM_DATE и (31-JAN-17) как TO_DATE, я хочу, чтобы запрос не передавал автоматически какие-либо значения для TO_DATE и FROM_DATE.

  • если пользователь не вводит никаких значений для TO_DATE и FROM_DATE или есть переданные значения NULL, я хочу, чтобы запрос автоматически вводил значения за последние месяцы (т. е. если запрос выполняется 1 июля 2017 года, FROM_DATE будет 01-JUN-17, а TO_DATE будет 30-JUN-17).

Мне подсказали использовать оператор объединения для обработки нескольких значений и NULLS (т. е. AND ( (coalesce(null, :P_ORG) is null) or (ORG.ORGANIZATION_ID in :P_ORG)))???

Любая помощь будет принята с благодарностью.


oracle business-intelligence oracle11g bi-publisher
person Fadiddy    schedule 23.06.2017    source источник
comment
Oracle и Postgresql имеют разные функции для обработки даты и времени. Какой из них вы используете?   -  person cachique    schedule 24.06.2017
comment
@cachique Извините, это для Oracle BI Publisher .... так что Oracle SQL.   -  person Fadiddy    schedule 24.06.2017

Ответы (3)


arrow_upward
0
arrow_downward

Что-то типа:

SELECT *
FROM   your_table
WHERE  your_date_column BETWEEN TO_DATE( :from_date, 'DD-MON-YYYY' )
                        AND     TO_DATE( :to_date, 'DD-MON-YYYY' )
OR     (   ( :from_date IS NULL OR :to_date IS NULL )
       AND your_date_column BETWEEN ADD_MONTHS( TRUNC( SYSDATE, 'MM' ), -1 )
                            AND     TRUNC( SYSDATE, 'MM' ) - 1
       );

Если одно (или оба) :from_date или :to_date равно NULL, то даты будут сравниваться с предыдущим месяцем.

Если в вашей таблице есть даты, где компонент времени не всегда установлен на полночь, вам нужно будет использовать:

SELECT *
FROM   your_table
WHERE  your_date_column BETWEEN TO_DATE( :from_date, 'DD-MON-YYYY' )
                        AND     TO_DATE( :to_date, 'DD-MON-YYYY' )
OR     (   ( :from_date IS NULL OR :to_date IS NULL )
       AND your_date_column >= ADD_MONTHS( TRUNC( SYSDATE, 'MM' ), -1 )
       AND your_date_column <  TRUNC( SYSDATE, 'MM' )
       );
person MT0    schedule 23.06.2017
comment
Это сработало отлично! Спасибо, сэр, это было именно то, что я искал. Очень ценится. - person Fadiddy; 28.06.2017

arrow_upward
0
arrow_downward

Доказательство концепции: рассмотрим следующий запрос, в котором у нас есть даты и значения, и мы хотим суммировать значения для дат, которые попадают между :from_date и :to_date. Если любой из них равен null, запрос будет использовать первый день предыдущего месяца для from_date и последний день предыдущего месяца для to_date. Обратите внимание, что это вызовет проблемы, если одной дате присваивается фактическое значение, а другой остается null — вы не объяснили, как вы хотите, чтобы это обрабатывалось. Но это другой вопрос.

Я использую SQL Developer, и в нем я не знаю, как передавать даты; Я показываю передачу строк и преобразование их в даты.

with
     test_data ( dt, val ) as (
       select date '2017-05-29', 200 from dual union all
       select date '2017-06-13', 150 from dual union all
       select date '2017-06-18', 500 from dual
     )
select sum(val) as sum_val
from   test_data
where  dt between coalesce(to_date(:from_date, 'yyyy-mm-dd'), 
                                                   add_months(trunc(sysdate, 'mm'), -1))
              and coalesce(to_date(:to_date  , 'yyyy-mm-dd'), trunc(sysdate, 'mm') - 1)
;
person mathguy    schedule 23.06.2017
comment
У вас есть :to_date в начале диапазона BETWEEN и :from_date в конце, и он не будет использовать предыдущий месяц в качестве диапазона, если только одна из связанных переменных равна NULL - будет использоваться комбинация значения, отличного от NULL, и предыдущий месяц. - person MT0; 24.06.2017
comment
@MT0 - Спасибо, что уловили путаницу в / из, я также указал ее в посте и исправил, но не в коде. Я прямо упомянул проблему только с одним из входных данных, равным NULL; ОП не указал желаемое поведение в этом случае. Я просто сделал другое предположение, отличное от твоего. - person mathguy; 24.06.2017

arrow_upward
0
arrow_downward

Да, вы можете использовать COALESCE (или Oracle NVL). Если параметр имеет значение null, замените его датой по умолчанию.

select *
from mytable
where mydate >= coalesce(:from_date, trunc(sysdate - interval '1' month), 'month')
  and mydate <= coalesce(:to_date, last_day(sysdate - interval '1' month));
person Thorsten Kettner    schedule 24.06.2017