Hello group,
I’m trying to convert the select statement below to work with postgresql but having some issues with the outer joins ‘(+)’ written for Oracle SQL:
select u.user_id,cm.course_id,gm.title,gt.name,a.attempt_date,a.grade, a.score,gm.possible,gm.visible_ind,
(select count(*) from bblearn.attempt aa where aa.gradebook_grade_pk1 = gg.pk1) number_of_attempts,
gg.manual_grade, gg.manual_score, gg.last_override_date
from gradebook_main gm, course_main cm, users u, course_users cu,
gradebook_type gt, gradebook_grade gg,.attempt a
where gm.crsmain_pk1 = cm.pk1
and cu.crsmain_pk1 = cm.pk1
and cu.users_pk1 = u.pk1
and cm.course_id = 'Org.dent.Training'
and gm.gradebook_type_pk1 = gt.pk1 (+)
and gm.deleted_ind = 'N'
and gm.pk1 = gg.gradebook_main_pk1
and cu.pk1 = gg.course_users_pk1
and a.gradebook_grade_pk1(+) = gg.pk1
and a.pk1(+) = gg.highest_attempt_pk1
order by u.user_id, gm.title;
psql:/export/home/bbuser/banner/gradeload/test2.sql:20: ERROR: syntax error at or near ")"
LINE 11: and gm.gradebook_type_pk1 = gt.pk1 (+)
^
I had tried using ‘left outer join’ but got different results when doing for multiple tables. This is what I came up with, but the count is off. I get 32,392 instead of 36,594 (Oracle SQL):
select count(u.user_id)
from course_main cm, users u, course_users cu,
attempt a
left join gradebook_grade gg on a.gradebook_grade_pk1 = gg.pk1 and a.pk1 = gg.highest_attempt_pk1,
gradebook_type gt
right join gradebook_main gm on gm.gradebook_type_pk1 = gt.pk1
where gm.crsmain_pk1 = cm.pk1
and cu.crsmain_pk1 = cm.pk1
and cu.users_pk1 = u.pk1
and cm.course_id = 'restore_org.dent.training'
and gm.deleted_ind = 'N'
and gm.pk1 = gg.gradebook_main_pk1
and cu.pk1 = gg.course_users_pk1;
Any assistance is greatly appreciated.
Thanks,
-- Merlin
Merlin D. Tchouante, Sr. IT Enterprise Application Developer
Center for Information Technology Services (CITS)
601 West Lombard Street
Baltimore, Maryland 21201-1512
mtchouan@umaryland.edu
410-706-4489 * 410-706-1500 fax
Please send Blackboard questions to the CITS support email address: DL-CITSBbSupport@umaryland.edu
Please send Mediasite questions to the CITS support email address: DL-CITSMediasiteSupport@umaryland.edu