Объединение полей дат из отдельных таблиц

У меня есть две почти одинаковые таблицы. Единственное отличие состоит в том, что это архивная таблица (назовем ее B), в которой все записи удалены из другой таблицы (назовем ее A).

Мне нужно получить ВСЕ записи из заданного диапазона данных, поэтому мне нужно соединить две таблицы (и фактически соединить их с третьей, чтобы получить часть информации, которой нет в этих таблицах, но это не влияет на мою проблему ).

Я хочу сгруппировать по часам, из которых поступила запись (т.е. trunc(<date_field>, 'hh')

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

ТАК, что я хочу сделать, это добавить две даты и заставить их работать так же, как в Excel (т.е. даты обрабатываются как их числовой эквивалент, добавляются, и результирующая дата возвращается, что, кстати, является по крайней мере одним случаем где допустимо добавление дат, несмотря на мнение этого потока в противном случае)

Это имеет еще больший смысл, поскольку я заменю нулевое значение даты на 0, поэтому функционально это должно быть похоже на добавление числа к дате (31.12.14 + 1 = 1/1/15).

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

SELECT DISTINCT Avg(NVL(to_number(to_char(trunc(fcr.actual_start_date, 'hh')))*86400, 0) + NVL(to_Number(to_char(trunc(acr.actual_start_date, 'hh')))*86400, 0)) Start_Num, SUM(AA.SESSIONCPU) TotalCPU, Count(1) Cnt
, SUM((NVL(to_number(to_char(trunc(fcr.actual_completion_date, 'hh')))*86400, 0) + NVL(to_Number(to_char(trunc(acr.actual_completion_date, 'hh')))*86400, 0) 
- NVL(to_number(to_char(trunc(fcr.actual_start_date, 'hh')))*86400, 0) - NVL(to_Number(to_char(trunc(acr.actual_start_date, 'hh')))*86400, 0))) TotRun

FROM PSTAT.A$_A AA
    LEFT OUTER JOIN APPL.FND_CR FCR On FCR.O_SES_ID = AA.SEsID
    LEFT OUTER Join XX.E_FND_CR ACR on ACR.O_SES_ID = aa.sesid
WHERE (trunc(fcr.actual_start_date) >= to_date('28-Dec-2014', 'DD-MON-YYYY')
    Or trunc(acr.actual_start_date) >= to_date('28-Dec-2014', 'DD-MON-YYYY'))
    AND rownum <= 1048500
    and (acr.status_code = 'C' or fcr.status_Code = 'C')
    AND aa.sessioncpu is not null
GROUP BY to_number(NVL(trunc(fcr.actual_start_date, 'hh'), 0))*86400 + to_Number(NVL(trunc(acr.actual_start_date, 0), 'hh'))*86400
ORDER BY 2, 1;

Моя явная проблема с приведенным выше кодом заключается в том, что Toad продолжает игнорировать приведения типов и говорит, что ожидает значение даты, когда получает число (0 выделяется). Итак, если кто-то может:

A) Скажите мне, почему Toad игнорирует приведения (он должен видеть число и поэтому не должен ожидать даты)

B) Предоставьте любые предложения о том, как заставить дополнение работать, или, если это не удастся, предложите альтернативный маршрут для объединения трех таблиц, чтобы я мог сгруппировать по значениям даты начала.

Как всегда, любая помощь очень ценится.


sql oracle outer-join toad
person JMichael    schedule 24.03.2015    source источник
comment
Придирка: это не ЖАБА чего-то ожидает или выдает ошибку. Это база данных Oracle. Любой другой клиент SQL выдаст ту же ошибку. Кстати: to_number(to_char(trunc(fcr.actual_start_date, 'hh'))) можно сократить до: extract(hour from fcr.actual_start_date)   -  person a_horse_with_no_name    schedule 25.03.2015
comment
I'm needing to get ALL records from a given data range, thus I need to join the two tables - Вам не нужно к ним присоединяться. Они не пересекаются, поэтому вы можете использовать UNION ALL.   -  person David Faber    schedule 25.03.2015

Ответы (2)


arrow_upward
0
arrow_downward

Добавление дат или преобразование их в числа выдает ORA-00975: date+date not allowed и ORA-01722: invalid number. Итак, что здесь можно сделать, чтобы работать с датами как в Excel? Моя идея состоит в том, чтобы вычесть первый день из календаря to_date(1, J) из каждой даты, с которой вы хотите работать.

Пример с датами испытаний:

with test_data as (
  select sysdate dt from dual union all 
  select to_date(1, 'J') from dual union all 
  select null from dual )
select nvl(trunc(dt, 'hh') - to_date(1, 'J'), 0) num_val, dt, 
    to_char(dt, 'J') tc1, to_char(dt, 'yyyy-mm-ss hh24:mi:ss') tc2
  from test_data

   NUM_VAL DT         TC1     TC2               
---------- ---------- ------- -------------------
2457105,96 2015-03-24 2457106 2015-03-14 23:12:14 
         0 4712-01-01 0000001 4712-01-00 00:00:00 
         0
person Ponder Stibbons    schedule 24.03.2015
comment
Пытаюсь убедиться, что я следую. Чтобы реализовать ваше предложение, я бы использовал nvl(trunc(dt, 'hh') - to_date(1, 'J'), 0) в полях даты и выполнил соответствующую арифметику, чтобы получить каждое из значений, которые я пытаюсь получить? бывший. Start_Num будет nvl(trunc(fcr.actual_start_date, 'hh') - to_date(1, 'J'), 0) + nvl(trunc(acr.actual_start_date, 'hh') - to_date(1, 'J'), 0)' - person JMichael; 25.03.2015
comment
Да. Но в вашем случае объединение данных сначала решает проблему, поэтому нет необходимости добавлять даты. - person Ponder Stibbons; 25.03.2015

arrow_upward
0
arrow_downward

@ Дэвид, кажется, твое предложение сработало как шарм. Для тех, кто придет позже, мой код обновлен следующим образом:

SELECT trunc(cr.actual_start_date, 'hh') Start_Date, SUM(AA.SESSIONCPU) TotalCPU, 
   Count(1) Cnt, SUM((cr.Actual_Completion_Date - cr.Actual_Start_Date)*86400) TotalRun
FROM (SELECT Actual_Start_Date, Actual_Completion_Date, Oracle_Session_ID, Status_Code 
        FROM APPL.FND_CR
    UNION ALL
    SELECT Actual_Start_Date, Actual_Completion_Date, Oracle_Session_ID, Status_Code 
        FROM XX.E_FND_CR) cr
RIGHT OUTER JOIN PSTAT.A$_A AA ON cr.Oracle_Session_ID = AA.SessionID
WHERE trunc(cr.actual_start_date) >= to_date('28-Dec-2014', 'DD-MON-YYYY')
AND rownum <= 1048500
and cr.status_code = 'C'
GROUP BY trunc(cr.actual_start_date, 'hh')
ORDER BY 1;
person JMichael    schedule 25.03.2015