Oracle and DateTime math

Question

我正在编写一个查询以选择一些记录。我有此数据：

Event Table
------------------
Definition
    EVE_RID         NUMBER(10)
    EVE_START_DATE  Date
Data:
    EVE_RID     EVE_START_DATE
    156891      11/1/2016
Agenda Table
------------------
Definition:
    AGD_EVE_RID     NUMBER(10)
    AGD_DAY         NUMBER(2)
    AGD_START_TIME  NUMBER(4)
Data:
AGD_EVE_RID     AGD_DAY     AGD_START_TIME
156891          1           1000     
156891          1           1400     
156891          8           1000     
156891          8           1400     
156891          15          1000     
156891          15          1400     
WAList Table
------------------
Definition:
    WAL_STARTTIME   DATE
    WAL_KEY         VARCHAR2(50)
Data:
WAL_STARTTIME           WAL_KEY                
11/1/2016 10:00:00 AM   6341371019318098180
11/1/2016 2:00:00 PM    7561779448126279684
11/8/2016 10:00:00 AM   6904435321948802820
11/8/2016 2:00:00 PM    7998296559469684996
11/15/2016 10:00:00 AM  4690144247933554180
11/15/2016 2:00:00 PM   7931460546152111876

我需要的是从议程表中的Walist表中匹配记录的某种方法。我如何编写我的Where子句以将Wallist记录匹配到议程。Agd_dayRecords并返回正确的一天的正确键？这将是结果：

EVE_START_DATE   AGD_DAY    AGD_START_TIME  WAL_KEY                
11/1/2016       1           1000            6341371019318098180
11/1/2016       1           1400            7561779448126279684
11/1/2016       8           1000            6904435321948802820
11/1/2016       8           1400            7998296559469684996
11/1/2016       15          1000            4690144247933554180
11/1/2016       15          1400            7931460546152111876

Answer 1

假设数据类型为＆mdash;sessions.startdate作为varchar2和wal.starttime作为日期：

select s.startdate, s.sessionDay, s.startTime, w.key 
from sessions s join WAL w
on to_date(to_char(to_date(s.startdate, 'mm/dd/yyyy') + s.sessionDay, 'mm/dd/yyyy')
||s.starttime,'mm/dd/yyyyhh24mi') = w.startTime;