Any troubleshooting tips for figuring out why a query will run in SQL Query Analyzer but never
returns when run through iBATIS?
The query looks like this:
INSERT INTO table_name1 (col1, col2, etc)
SELECT col1, col2, etc.
FROM table_name2
The actual query is much more complicated than that. I'll include it below. It also uses a temporary
table that is created and populated prior to running the above INSERT statement.
When I run the query in SQL Query Analyzer it returns in about 15 seconds. I waited for 20 minutes
in my debugger and never saw it return when running it through iBATIS.
Any help would be greatly appreciated.
TIA,
Brian Barnett
The actual query:
INSERT INTO intervention_work(sch_student_id, student_id, student_name, grade_level,
faculty_name, school_name, browser_session_id, program_group_id)
SELECT DISTINCT st.sch_student_id, st.student_id,
st.last_name + ', ' + st.first_name + ' ' + st.middle_name AS student_name,
p.grade_level, f.preferred_name, sc.school_name,
'AEF53C44ABD90B4A5BD39D647EED9F8A' AS browser_session_id, 6 AS program_group_id
FROM student st, school sc , ##sppr1134173132150 p
LEFT OUTER JOIN faculty f on f.faculty_id = p.faculty_id
WHERE
(
(sc.school_id = p.school_id
AND st.sch_student_id = p.sch_student_Id
AND p.sch_student_id IN (SELECT pp.sch_student_id
FROM ##sppr1134069477255 pp
WHERE pp.sch_student_id = p.sch_student_id
AND pp.assessment_id = 13
AND pp.ppl_id = 3)
)
OR
(sc.school_id = p.school_id
AND st.sch_student_id = p.sch_student_Id
AND p.sch_student_id IN (SELECT pp.sch_student_id
FROM ##sppr1134069477255 pp
WHERE pp.sch_student_id = p.sch_student_id
AND pp.assessment_id = 11
AND pp.ppl_id = 3)
)
OR
(sc.school_id = p.school_id
AND st.sch_student_id = p.sch_student_Id
AND p.sch_student_id IN (SELECT pp.sch_student_id
FROM ##sppr1134069477255 pp
WHERE pp.sch_student_id = p.sch_student_id
AND pp.assessment_id = 14
AND pp.ppl_id = 3)
)
OR
(sc.school_id = p.school_id
AND st.sch_student_id = p.sch_student_Id
AND p.sch_student_id IN (SELECT pp.sch_student_id
FROM ##sppr1134069477255 pp
WHERE pp.sch_student_id = p.sch_student_id
AND pp.assessment_id = 15
AND pp.ppl_id = 3)
)
OR
(sc.school_id = p.school_id
AND st.sch_student_id = p.sch_student_Id
AND p.sch_student_id IN (SELECT pp.sch_student_id
FROM ##sppr1134069477255 pp
WHERE pp.sch_student_id = p.sch_student_id
AND pp.assessment_id = 9
AND pp.ppl_id = 3)
)
OR
(sc.school_id = p.school_id
AND st.sch_student_id = p.sch_student_Id
AND p.sch_student_id IN (SELECT pp.sch_student_id
FROM ##sppr1134069477255 pp
WHERE pp.sch_student_id = p.sch_student_id
AND pp.assessment_id = 6
AND pp.ppl_id = 3)
)
OR
(sc.school_id = p.school_id
AND st.sch_student_id = p.sch_student_Id
AND p.sch_student_id IN (SELECT pp.sch_student_id
FROM ##sppr1134069477255 pp
WHERE pp.sch_student_id = p.sch_student_id
AND pp.assessment_id = 5
AND pp.ppl_id = 3)
)
)
ORDER BY sc.school_name,f.preferred_name,student_name
Broadband interface (RIA) + mail box saftey = http://iBatis_for_Java_Users_List.roomity.com
*Your* clubs, no sign up to read, ad supported; try broadband internet.
