Horton

Thread

  1. Help with outer joins

    Tchouante, Merlin <mtchouan@umaryland.edu> — 2019-11-08T16:55:33Z

    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<mailto:mtchouan@umaryland.edu>
410-706-4489 * 410-706-1500 fax

Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu<mailto:dl-citsbbsupport@umaryland.edu>
Please send Mediasite questions to the CITS support email address:  DL-CITSMediasiteSupport@umaryland.edu<mailto:DL-CITSMediasiteSupport@umaryland.edu>

[New UMB Logo]

  2. RE: Help with outer joins

    Tchouante, Merlin <mtchouan@umaryland.edu> — 2019-11-11T15:21:54Z

    Never mind, I figured it out.

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<mailto:mtchouan@umaryland.edu>
410-706-4489 * 410-706-1500 fax

Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu<mailto:dl-citsbbsupport@umaryland.edu>
Please send Mediasite questions to the CITS support email address:  DL-CITSMediasiteSupport@umaryland.edu<mailto:DL-CITSMediasiteSupport@umaryland.edu>

[New UMB Logo]

From: Tchouante, Merlin
Sent: Friday, November 8, 2019 11:56 AM
To: pgsql-sql@lists.postgresql.org
Subject: Help with outer joins

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<mailto:mtchouan@umaryland.edu>
410-706-4489 * 410-706-1500 fax

Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu<mailto:dl-citsbbsupport@umaryland.edu>
Please send Mediasite questions to the CITS support email address:  DL-CITSMediasiteSupport@umaryland.edu<mailto:DL-CITSMediasiteSupport@umaryland.edu>

[New UMB Logo]