The SELECT is sound. When I run it, it will give me the standard 100 rows without much delay at all.
When I first ran it in context, the table that I was attempting to INSERT rows into had over 100K records. I thought that might be a problem given the subquery : AND t.ID not in (select TASK_ID from tasks_master_tracker_idb) , but that didn't seem to have any impact.
I tried this in an attempt to figure out how may records I was attempting to INSERT...
select count(*) from (SELECT t.ID, t.CUSTID, t.INTID, t.NAME, t.DESCRIPTION, r.MAIN_6, t.DIRECTION, t.PRIORITY, tsk.CRITICAL_TO_OPERATION, t.COMPLEXITY, t.COUNTRY, r.MAIN_1, r.MAIN_7, r.REQ_STATUS, r.TECH_21, r.TECH_5, tsk.MONDAY, tsk.TUESDAY, tsk.WEDNESDAY, tsk.THURSDAY, tsk.FRIDAY, tsk.SATURDAY, tsk.SUNDAY, r.TECH_29, r.TECH_20, r.TECH_20, rt.TRANSPORT_3, rt.TRANSPORT_4, rt.TRANSPORT_21, rt.TRANSPORT_5, rt.TRANSPORT_26, rt.TRANSPORT_4, rt.TRANSPORT_21, rt.TRANSPORT_5, rt.TRANSPORT_9, rt.TRANSPORT_22, rt.TRANSPORT_10, rt.TRANSPORT_26, rt.TRANSPORT_9, rt.TRANSPORT_22, rt.TRANSPORT_10, r.DATA_6, rt.TRANSPORT_18, rt.TRANSPORT_18, rt.TRANSPORT_19, rt.TRANSPORT_19, rt.TRANSPORT_24, rt.TRANSPORT_24, (SELECT c.USER_ID FROM contacts c where c.RID=t.ID and c.ROLE='PM' and RTYPE='TASK' LIMIT 1), t.VENDOR, t.CREATED_BY, t.CREATE_DT, t.UPDATED_BY, t.LAST_MODIFIED_DT FROM tasks t LEFT JOIN tasks_ams tsk ON t.ID = tsk.RID LEFT JOIN requirements r on t.ID = r.INTID_ and r.ID = (select MAX(ID) from requirements where INTID_ = t.ID) LEFT JOIN requirements_transport rt ON t.ID = rt.INTID__ and rt.ID = (select MAX(ID) from requirements_transport where INTID__ = t.ID) WHERE (t.INTID is not null and t.CUSTID is not null ) AND t.ID not in (select TASK_ID from tasks_master_tracker_idb))
...and I got an error that said, "Every derived table must have it's own alias," which doesn't make sense since the SELECT that I'm counting from can be run independently.
When I first ran it in context, the table that I was attempting to INSERT rows into had over 100K records. I thought that might be a problem given the subquery : AND t.ID not in (select TASK_ID from tasks_master_tracker_idb) , but that didn't seem to have any impact.
I tried this in an attempt to figure out how may records I was attempting to INSERT...
Open in new window
...and I got an error that said, "Every derived table must have it's own alias," which doesn't make sense since the SELECT that I'm counting from can be run independently.What do you think?