Combining dates fields from separate tables

Question

I have two tables that are almost exactly the same. The only difference is one is an archive table (call that B) that has any records removed from the other table (call that A)

I'm needing to get ALL records from a given data range, thus I need to join the two tables (and actually join them to a third to get a piece of information that's not on those tables, but that doesn't affect my problem).

I'm wanting to group by the hour the record comes from (i.e. trunc(<date_field>, 'hh')

However, since I need to get records from each hour from the two tables it seems I would need to generate a single date field to group on, otherwise the group wouldn't make sense; each record will only have a date from one field so if I group by either table's date field it would inherently omit records from the other, and if I group by both I'll get no data back as no record appears in both tables.

SO, what I want to do is add two "dates" and have it work like it would in Excel (i.e. the dates get treated as their numeric equivalent, get added, and the resultant date is returned, which by the way is at least one case where adding dates is valid, despite this thread's opinion otherwise)

This makes even more sense as I'll be replacing the null date value with 0 so it should functionally be like adding a number to a date (12/31/14 + 1 = 1/1/15).

I just haven't been able to get it to work. I've tried several iterations to get the calculation to work the latest being:

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;

My explicit problem with the code above is that Toad keeps ignoring the casts and says it is expecting a date value when it gets a number (the 0 gets highlighted). So if someone could:

A) Tell my why Toad would ignore the casts (it should be seeing a number and so should have absolutely no expectation of a date)

B) Provide any suggestions on how to get the addition to work, or failing that suggest an alternative route to combine the three tables so that I'm able to group by the start date values

As always, any help is much appreciated.

Nitpicking: it's not "TOAD" that expects anything or gives an error. It's the Oracle database. Any other SQL client would give you the same error. Btw: `to_number(to_char(trunc(fcr.actual_start_date, 'hh')))` can be shortened to: `extract(hour from fcr.actual_start_date)` — , Mar 24 '15 at 22:48
`I'm needing to get ALL records from a given data range, thus I need to join the two tables` - You don't need to join them. They are disjoint so you can use `UNION ALL`. — David Faber, Mar 25 '15 at 02:50

score 0 · Answer 1 · answered Mar 24 '15 at 22:19

Adding dates or casting them to number throws ORA-00975: date+date not allowed and ORA-01722: invalid number. So what can be done here to operate on dates in Excel way? My idea is to substract first day from calendar to_date(1, J) from each date you want to operate on.

Example with test dates:

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

Trying to make sure I follow. To implement your suggestion I would use `nvl(trunc(dt, 'hh') - to_date(1, 'J'), 0)` on the date fields and perform the appropriate arithmetic to get each of the values I'm attempting to derive? ex. `Start_Num` would be `nvl(trunc(fcr.actual_start_date, 'hh') - to_date(1, 'J'), 0) + nvl(trunc(acr.actual_start_date, 'hh') - to_date(1, 'J'), 0)'` — JMichael, Mar 25 '15 at 12:50
Yes. But in your case unioning data at first solves the problem, so no need to _add_ dates. — Ponder Stibbons, Mar 25 '15 at 16:00

score 0 · Accepted Answer · answered Mar 25 '15 at 14:14

@David, your suggestion seems to have worked like charm. For those who come along afterwards my code as updated follows:

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;

Combining dates fields from separate tables

2 Answers2