CODEWITHSUNDEEP
sqloracle-database
TheNewNo2
TheNewNo2

Reputation: 67

SQL Oracle SQL conditional join

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?

Upvotes: 2

Views: 124

Answers (4)

Dave Costa
Dave Costa

Reputation: 48111

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 
   )

Upvotes: 2

David Faber
David Faber

Reputation: 12485

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.

Upvotes: 2

Sander
Sander

Reputation: 4042

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]

Upvotes: 1

Matt
Matt

Reputation: 15071

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

Upvotes: 0

Related Questions