RE: Help with outer joins

Never mind, I figured it out. 

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