Using if statement with an empty part in SQL

Question

First, this should be implement in the Oracle DBMS but I have no access to it so I have no way to try it.

I have the following query to create:

A flight attendant working at your airline requests day off on the 21st of November, they usually cover the 12 to 22 shift, check the employee’s schedules to make sure someone is covering the same working hours in order to determine if their day off can be given, if not then update working hours of an employee finishing at 12 so they do a double shift to make sure 24 hours are covered with the day off taken.

Relevant info: I have an entity EMPLOYEE that has a relationship with a weak entity SCHEDULE which has the attributes "Day", "StartTime", "EndTime"

This is my first time using the IF statement and this is my attempt:

IF( SELECT StartTime, EndTime
    FROM SCHEDULE S
   WHERE S.StartTime<=12 AND S.EndTime>=22 AND DAY="21 November") != NULL

want this part to be empty or return something like success*

ELSE   
   UPDATE SCHEDULE 
   SET EndTime=22  
   WHERE EndTime=12 AND Day="21 November"

I am new to SQL with no access to a DBMS, any help would be appreciated.

What type of SQL are you working with? Please update your question and tag the specific type of SQL — devlin carnate, Nov 18 '20 at 18:17
Are you using only SQL? If yes which DBMS? Do you use a programming language to access you data? — SuperPoney, Nov 18 '20 at 18:17
Does this answer your question? [MySQL - UPDATE query based on SELECT Query](https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query) — Daria Pydorenko, Nov 18 '20 at 18:18

score 0 · Answer 1 · answered Nov 18 '20 at 18:26

0

Then you can use a PL/SQL script like this one:

    DECLARE
        mycounter NUMBER := 0;
    BEGIN
        SELECT count(*)
        INTO mycounter
        FROM SCHEDULE S
        WHERE S.StartTime<=12
        AND S.EndTime>=22
        AND S.DAY='21 November';
    
        IF mycounter > 0 THEN
            UPDATE SCHEDULE 
            SET EndTime=22  
            WHERE EndTime=12 AND Day='21 November'
        END IF;
    END;
    /

And don't forget to use simple quote ' and not double quotes " for characters.

answered Nov 18 '20 at 18:26

SuperPoney

563
6
21

3

To **not** storing a date as a *String* would be probably a better hint... – Marmite Bomber Nov 18 '20 at 18:42
Just had the chance to try it in Oracle DBMS and it yielded the following errors: ORA-06550: line 15, column 9: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 12, column 13: PL/SQL: SQL Statement ignored ORA-06550: line 16, column 8: PLS-00103: Encountered the symbol ";" when expecting one of the following: if 1. DECLARE 2. mycounter NUMBER := 0; 3. BEGIN – Sergio Nov 20 '20 at 17:49

Using if statement with an empty part in SQL

1 Answers1