Re: Very Rusty with SQL and Oracle (mainly SQL)

From: Jeffrey Kemp <anonymous_at_somewhere.net>
Date: Sat, 4 Sep 2004 09:13:30 +0800
Message-ID: <413916bf$1$19713$5a62ac22@per-qv1-newsreader-01.iinet.net.au>

You need to propagate the outer join to alias:

select
  to_char(a.alias_fnam || ' ' || a.alias_lnam) "Name",

  cg.orga_# "Campus #",
  cg.grp_id "Group ID",
  co.cour_id "Course ID"
 

from

   cour_off co,
   alias a,
    inst_rost ir,
   calendar_group cg  

where

   ( co.grp_id = cg.grp_id and co.orga_# = cg.orga_# ) and
   ( co.orga_# = ir.orga_# (+) ) and
   ( co.cour_off_# = ir.cour_off_# (+) ) and
   ( a.pers_# (+) = ir.pers_# ) and 
   ( cg.orga_# = 3 ) and
   ( cg.calendar_end_date > trunc(sysdate) ) and
   ( cg.grp_id like 'ADVIOG161%' )

  "Relvinian" <m_at_msn.com> wrote in message news:taGdnQD2xuohY6rcRVn-tQ_at_comcast.com...   Sorry, accidentally sent it before I was ready. I have added more to the bottom of this to help fill in some of the information I didn't get a chance to.     "Relvinian" <m_at_msn.com> wrote in message news:J5ednXx907U_YKrcRVn-rw_at_comcast.com...     Hey all,

    I'm trying to get a query to work in Oracle and having troubles. I'll hopefully try and explain what I'm looking for in a result set and what my current SQL code looks like.

    What the result set should look like is the following (of course, multiple rows for each instructor at the campus, etc):

    NAME             Campus #             Group ID                  Course ID

----------------------------------------------------------------------------

    <first> <last>   <campus num>      <a number>              <course num>

    Now, the information to fill the query set is coming from four different tables.

    Here's two seperate query which give me all the information I need but I would like to get them combined into one query.

    First Query:
    select

      to_char(NULL) "Name",
      cg.orga_# "Campus #",
      cg.grp_id "Group ID",
      co.cour_id "Course ID"

    from
       cour_off co,
        inst_rost ir,
       calendar_group cg

    where


       ( co.grp_id = cg.grp_id and co.orga_# = cg.orga_# ) and

       ( co.orga_# = ir.orga_# (+) ) and

       ( co.cour_off_# = ir.cour_off_# (+) ) and

       ( cg.orga_# = 3 ) and

       ( cg.calendar_end_date > trunc(sysdate) ) and

       ( cg.grp_id like 'ADVIOG161%' )

    This gives me everything I need but the instructor's name.

    The second query:
    select

      to_char(a.alias_fnam || ' ' || a.alias_lnam) "Name", 
      cg.orga_# "Campus #",
      cg.grp_id "Group ID",
      co.cour_id "Course ID"

    from
       cour_off co,
       alias a, 
        inst_rost ir,
       calendar_group cg

    where


       ( co.grp_id = cg.grp_id and co.orga_# = cg.orga_# ) and

       ( co.orga_# = ir.orga_# (+) ) and

       ( co.cour_off_# = ir.cour_off_# (+) ) and

       ( a.pers_# = ir.pers_# ) and 

       ( cg.orga_# = 3 ) and

       ( cg.calendar_end_date > trunc(sysdate) ) and

       ( cg.grp_id like 'ADVIOG161%' )

    This is almost what I need but missing information. If the ir.pers_# is null then I don't get the entry in my result set.

    Any help would be appreciated in "fixing" my query to work correctly.

    If I haven't explained well enough, let me know please.

    Relvinian Received on Fri Sep 03 2004 - 20:13:30 CDT