Insert using subquery by adding dates between two dates based on days [oracle 11g]

Question

This is presentation table:

ID     PRESENTATIONDAY         PRESENTATIONSTART       PRESENTATIONEND        PRESENTATIONSTARTDATE             PRESENTATIONENDDATE 
622         Monday                   12:00:00              02:00:00              01-05-2016                            04-06-2016 
623        Tuesday                   12:00:00              02:00:00              01-05-2016                            04-06-2016 
624        Wednesday                 08:00:00              10:00:00              01-05-2016                            04-06-2016 
625        Thursday                  10:00:00              12:00:00              01-05-2016                            04-06-2016

I would like to insert availabledate in schedule table. This is my current query :

insert into SCHEDULE (studentID,studentName,projectTitle,supervisorID,    
                      supervisorName,examinerID,examinerName,exavailableID,
                     availableday,availablestart,availableend,
                     availabledate) //PROBLEM STARTS HERE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
values (?,?,?,?,?,?,?,?,?,?,?,?));

The value availabledate are retrieved based on the exavailableID . For example, if exavailableID = 2, the availableday = Monday, availablestart= 12pm, availableend = 2pm.

The dates will only be chosen only between PRESENTATIONSTARTDATE to PRESENTATIONENDDATE from presentation table.

In presentation table, it will match PRESENTATIONDAY, PRESENTATIONDATESTART and PRESENTATIONDATEEND with availableday, availablestart and availableend to get a list of all possible dates.

This is the query to get list of all possible dates based on particular days:

select 
  A.PRESENTATIONID,
  A.PRESENTATIONDAY,
  A.PRESENTATIONDATESTART+delta LIST_DATE
from 
  PRESENTATION A, 
  (
     select level-1 as delta 
     from dual 
     connect by level-1 <= (
       select max(PRESENTATIONDATEEND- PRESENTATIONDATESTART) from PRESENTATION
     )
  )
where A.PRESENTATIONDATESTART+delta <= A.PRESENTATIONDATEEND
and
  a.presentationday = trim(to_char(A.PRESENTATIONDATESTART+delta, 'Day'))
order by 1,2,3;

This query result is:

622 Monday  02-05-2016 12:00:00
...
622 Monday  30-05-2016 12:00:00
623 Tuesday 03-05-2016 12:00:00
...
623 Tuesday 31-05-2016 12:00:00
624 Wednesday   04-05-2016 12:00:00
...
624 Wednesday   01-06-2016 12:00:00
625 Thursday    05-05-2016 12:00:00
...
625 Thursday    02-06-2016 12:00:00

It will automatically assign dates from the SELECT query to be inserted in schedule table. However, each date can be used only 4 times. Once it reached 4 times, it will proceed to next date. For example, if Monday, '02-05-2016' to '09-05-2016'

How can I corporate these two queries (INSERT and SELECT) to have a result like this:

  StudentName  projectTitle SupervisorID ExaminerID  availableday    availablestart       availableend      availabledate
        abc         Hello     1024       1001            MONDAY         12.00pm                2.00pm         02-05-2016
        def         Hi        1024       1001            MONDAY         12.00pm                2.00pm         02-05-2016
        ghi         Hey       1002       1004            MONDAY         12.00pm                2.00pm         02-05-2016
        xxx         hhh       1020       1011            MONDAY         12.00pm                2.00pm         02-05-2016
        jkl         hhh       1027       1010            MONDAY         12.00pm                2.00pm         09-05-2016 
        try         ttt       1001       1011            MONDAY         12.00pm                2.00pm         09-05-2016
        654         bbb       1007       1012            MONDAY         12.00pm                2.00pm         09-05-2016
        gyg         888       1027       1051            MONDAY         12.00pm                2.00pm         09-05-2016
        yyi         333       1004       1022           TUESDAY         12.00pm                2.00pm         03-05-2016
        fff         111       1027       1041           TUESDAY          ..
        ggg         222       1032       1007           TUESDAY          ..    ..                       ..               ..
        hhh         444       1007       1001           TUESDAY         12.00pm                2.00pm         03-05-2016


         and so on :)

In short, I would like to use the list of dates from presentation table based on the day, start time and end time to insertion query where each date will only used 4 times. Thank you!

Dahamsta · Answer 1 · 2016-05-24T11:13:28.863

0

I am not sure this kind of syntax works with oracle (and have no good way to check), but changing the select part of insert like this may or may not work.

select 
  A.PRESENTATIONID,
  A.PRESENTATIONDAY,
  A.PRESENTATIONDATESTART+delta LIST_DATE
from 
  PRESENTATION A, 
  (
     select level-1 as delta 
     from dual 
     connect by level-1 <= (
       select max(PRESENTATIONDATEEND - PRESENTATIONDATESTART) from PRESENTATION
     )
  ),
  --MIGHT NEED ADDITIONAL LOGIC FOR THE EXAVAILABLEID COMPARISON
  (SELECT count(S.*) as counter FROM SCHEDULE S WHERE S.EXAVAILABLEID=A.ID) C
where A.PRESENTATIONDATESTART+delta <= A.PRESENTATIONDATEEND
and
  a.presentationday = trim(to_char(A.PRESENTATIONDATESTART+delta, 'Day'))
and 
  C.counter<4
order by 1,2,3;

EDIT: Changed the operator. Had >= before. Placed teh WHERE check at the right place. Deleted aliases.

EDIT2: changed the syntax to where the counter select statement is a part of the from clause.

edited May 24 '16 at 11:13

answered May 24 '16 at 09:09

Dahamsta

97
2
9

Thank you for your time @Dahamsta :) how does this query helped me to only use the date 4 times only each? – user6308605 May 24 '16 at 09:13
Sorry, I completely misunderstood the problem. I get it now. What you might do is create a trigger before updates that checks and throws an error when there if there are already four rows with the presentation ID in the schedule table. – Dahamsta May 24 '16 at 09:21
I dont understand the trigger cascade part... any general example at least? – user6308605 May 24 '16 at 09:33
This is quite complex!! Actually, `exavailableID` is not from `presentationID`... `exavailableID` is retrieved from another table. So I just match them with day, start time and end time because the IDs are not similar.. However, I tried your query with some adjustments, I got an error : `ORA-04089: cannot create triggers on objects owned by SYS` – user6308605 May 24 '16 at 10:02
If you use a specific schema, you should define the trigger as CREATE OR REPLACE TRIGGER insert_schedule before INSERT ON "your schema".schedule. Also this will only stop the insertion of the fifth element with the same presentation ID with a raised error. – Dahamsta May 24 '16 at 10:07
Hmmmm is there any other solution by using only `select` statement and truncate? – user6308605 May 24 '16 at 10:11
Yeah this solution I have will most likely not work for you. The problem is outlined here: http://stackoverflow.com/questions/15377346/why-cannot-i-create-triggers-on-objects-owned-by-sys. I presume your current schema is the default system schema. Gonna look into truncate. – Dahamsta May 24 '16 at 10:13
I have this idea... But Idk how to execute it. Something like : `trunc(( somevariables - 1) / 4), availabledate`. Maybe `partition by` clause... – user6308605 May 24 '16 at 10:16
Hmm thank you!!! Ill try to modify your query based on my requirements. I think I understand what you are trying to do :D Thank you again! – user6308605 May 24 '16 at 14:53
Hello, I tried the query again.. the first part of the query, works well. the second part (`(SELECT count(S.*)...`)of the query have error :` invalid user.table.column, table.column, or column specification` – user6308605 Jun 14 '16 at 05:02

Insert using subquery by adding dates between two dates based on days [oracle 11g]

1 Answers1