SQL Oracle SQL conditional join

Question

I'm working on an Oracle SQL query of an asset management system. My query updates a data warehouse with information from the AMS's equipment table. Unfortunately the AMS is a bit rubbish, and the Equipment table doesn't include a last modified date, nor is there any way to get one using audits.

The Equipment table has 2,000,000 records, so I don't want to update the whole thing during office hours. Instead I want to limit it to Equipment linked to Work Orders which were updated recently. But I also need to update the whole table overnight to pick up any new records which haven't been attached to Work Orders. I'd like to avoid having two imports so I don't get in the situation where one import gets changed but the other doesn't.

What I'm after is a way that, based on the time of day, I can add an extra join.

So during office hours it would be:

SELECT
*
FROM
Equipment
INNER JOIN
  ( SELECT DISTINCT Equipment_ID FROM Work_Orders
    WHERE Work_Orders.Last_Update > SYSDATE - 2 
) WO
ON
Equipment.Equipment_ID = WO.Equipment_ID

But after say 9pm it would be

 SELECT
 *
 FROM
 Equipment

I've seen examples elsewhere which used joins where the ON criteria were conditional, however I haven't found anywhere where even having the join is conditional. Is this even possible?

David Faber · Accepted Answer · 2018-05-01 15:12:45Z

2

I think you could do something like this:

SELECT * FROM equipment e
 WHERE EXISTS ( SELECT 1 FROM work_orders wo
                 WHERE wo.equipment_id = e.equipment_id
                   AND wo.last_update > SYSDATE-2 )
    OR TO_CHAR(SYSDATE, 'HH24') BETWEEN '09' and '17'; -- Assuming work hours are 9:00 am to 5:00 pm

Now you mention this in the OP: "But I also need to update the whole table overnight to pick up any new records which haven't been attached to Work Orders." You could get new records either by checking the date or for checking the existence in the work_orders table:

SELECT * FROM equipment e
 WHERE NOT EXISTS ( SELECT 1 FROM work_orders wo
                     WHERE wo.equipment_id = e.equipment_id );

Putting the two queries together, we might get something like the following:

SELECT * FROM equipment e
 WHERE EXISTS ( SELECT 1 FROM work_orders wo
                 WHERE wo.equipment_id = e.equipment_id
                   AND wo.last_update > SYSDATE-2 )
    OR ( TO_CHAR(SYSDATE, 'HH24') BETWEEN '09' and '17'
     AND NOT EXISTS ( SELECT 1 FROM work_orders wo
                       WHERE wo.equipment_id = e.equipment_id ) );

Hope this helps.

edited May 1, 2018 at 15:12

answered May 1, 2018 at 14:16

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TheNewNo2 Over a year ago

The system doesn't like the HH24 bit, but using EXTRACT instead works. Thanks!

David Faber Over a year ago

Sorry, had double quotes instead of single - should work unaltered now.

Dave Costa · Accepted Answer · 2018-05-01 16:20:18Z

2

It's easier to do this with an IN clause rather than a join, using OR to allow one of several conditions to apply. Let's say you want the "select all equipment" option to be enabled between 9 PM and 3 AM. You could do this:

SELECT
*
FROM
Equipment
WHERE TO_CHAR( SYSDATE, 'HH24' ) > '21'
   OR TO_CHAR( SYSDATE, 'HH24' ) < '03'
   OR Equipment_ID IN (
         SELECT DISTINCT Work_Orders.Equipment_ID FROM Work_Orders
         WHERE Work_Orders.Last_Update > SYSDATE - 2 
   )

edited May 1, 2018 at 16:20

answered May 1, 2018 at 13:43

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

1 Comment

TheNewNo2 Over a year ago

The system doesn't like the HH24 bit, but using EXTRACT instead works. Thanks!

Sander · Accepted Answer · 2018-05-01 14:32:04Z

My take on this question. I'm using the lazy evaluation of an OR statement in the WHERE clause with and EXISTS construction. This way all conditions are part of the WHERE clause.

Sample data

create table Equipment
(
  Id varchar(3)
);

insert into Equipment values ( 'E01' );
insert into Equipment values ( 'E02' );

create table Orders
(
  Num varchar(5),
  LastModified date,
  EquipmentId varchar(3)
);

insert into Orders values ( 'OR001', SYSTIMESTAMP-4, 'E01' );
insert into Orders values ( 'OR002', SYSTIMESTAMP-3, 'E02' );
insert into Orders values ( 'OR003', SYSTIMESTAMP, 'E01' );
insert into Orders values ( 'OR004', SYSTIMESTAMP, 'E01' );

Fetch all applicable equipment ID's. Before 9pm the first part of the OR condition fails and the next part is evaluated. After 9pm the first part of the OR condition is valid (and due to lazy evaluation the next part is no longer validated).

Actual solution

select distinct Id
from Equipment
where extract(HOUR from SYSTIMESTAMP) > 21 -- after 9pm do not evaluate the next part of OR condition
   or exists (  select 'x'
                from Orders
                where EquipmentId = Id
                  and LASTMODIFIED > SYSDATE-2 );

[SQL Fiddle]

Matt · Accepted Answer · 2018-05-01 13:43:20Z

0

Use EXTRACT(HOUR FROM CAST(sysdate AS timestamp)) to determine the time of day.

I determined "working hours" to be 9am to 5pm in the below example.

SELECT * 
FROM CASE 
     WHEN EXTRACT(HOUR FROM CAST(sysdate AS timestamp)) BETWEEN 9 AND 17
     THEN (SELECT *
           FROM Equipment e
           INNER JOIN (SELECT DISTINCT Equipment_ID 
                       FROM Work_Orders w
                       WHERE w.Last_Update > SYSDATE - 2) WO
           ON e.Equipment_ID = WO.Equipment_ID)
    ELSE (SELECT * FROM Equipment)
    END

answered May 1, 2018 at 13:43

Matt

15.1k28 gold badges109 silver badges161 bronze badges

2 Comments

Dave Costa Over a year ago

I don't think that CASE can be used at that position in a SELECT statement.

TheNewNo2 Over a year ago

Putting it into SQL developer says no that doesn't work (SQL command not properly ended at line 3 column 11)

Collectives™ on Stack Overflow

SQL Oracle SQL conditional join

4 Answers 4

2 Comments

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related