CODEWITHSUNDEEP
oracle-database
MatteoS
MatteoS

Reputation: 95

Excluding rows from outer join

I am trying to extract rows from a table to gain the start and end time for events, these are held in a one to many table relationship so that for each instanceid in the master table you can have a number of entryids in the child. I have successfully written the below query that extracts the required data converting epoch timestamps, but am getting rows with NULL values. I can see that to exclude these I need to only select rows from table_a where the status value reflect 1 or 6, but am not sure of syntax for achieving this, I have tried combos of bold highlighted line below to no avail.

SELECT a.summary
      to_char(date '1970-01-01' + b.create_date/86400, 'DD Mon YYYY HH24:MI:SS') as start_date, 
      to_char(date '1970-01-01' + c.create_date/86400, 'DD Mon YYYY HH24:MI:SS') as completed_date,
      to_char (TRUNC (SYSDATE) + NUMTODSINTERVAL ((c.create_date - b.create_date), 'second'), 'hh24:mi:ss') as Elapse_Time,
     a.status
FROM o2_hpov_casecreation a
LEFT OUTER JOIN o2_hpov_casecreation_audit b
    ON (a.instanceid = c.entryid
       AND B.action= 'Queueing Simulation phase on SYS:Action')
LEFT OUTER JOIN o2_hpov_casecreation_audit c
    ON  (a.instanceid = c.entryid
        AND c.action = 'Notifications completed')
    AND a.status IN (2,6)

Upvotes: 0

Views: 36

Answers (1)

Ricardo Arnold
Ricardo Arnold

Reputation: 913

You need to add your filter criteria to the where clause

below is a corrected version of your query

SELECT a.summary
      to_char(date '1970-01-01' + b.create_date/86400, 'DD Mon YYYY HH24:MI:SS') as start_date, 
      to_char(date '1970-01-01' + c.create_date/86400, 'DD Mon YYYY HH24:MI:SS') as completed_date,
      to_char (TRUNC (SYSDATE) + NUMTODSINTERVAL ((c.create_date - b.create_date), 'second'), 'hh24:mi:ss') as Elapse_Time,
     a.status
FROM o2_hpov_casecreation a
LEFT OUTER JOIN o2_hpov_casecreation_audit b
    ON (a.instanceid = c.entryid
       AND B.action= 'Queueing Simulation phase on SYS:Action')
LEFT OUTER JOIN o2_hpov_casecreation_audit c
    ON  (a.instanceid = c.entryid
        AND c.action = 'Notifications completed')
WHERE a.status IN (2,6)

Upvotes: 1

Related Questions