Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Red face Unanswered: Left join sub query as view

    I'm trying to get the SQL below to execute, but I'm getting an error stating that a column may not be outer joined to a subquery and it recommends that I create a view for the subquery.

    Below is my SQL currently, can anyone give an example of how I can turn the subquery on SLBRDEF into a view so that it can be used in my left join? I need to left join SLBRDEF onto SSRMEET with the SLBRDEF_TERM_CODE_EFF equal to the max term_code_eff where the bldg_code = ssrmeet_bldg_code and the room_code = ssrmeet_room_number and the term_code_eff <= '201103'

    Can anyone help?

    Thanks !

    SQL
    ----
    select SSBSECT.SSBSECT_CAMP_CODE "Camp",
    STVCAMP.STVCAMP_DESC "Campus",
    SSBSECT.SSBSECT_CRN "Crn",
    SSBSECT.SSBSECT_SUBJ_CODE "Subject",
    SSBSECT.SSBSECT_CRSE_NUMB "Course",
    SSBSECT.SSBSECT_SEQ_NUMB "Section",
    SCBCRSE.SCBCRSE_TITLE "Title",
    SSBSECT.SSBSECT_MAX_ENRL "Max_Enroll",
    SSBSECT.SSBSECT_ENRL "Enrolled",
    SSBSECT.SSBSECT_WAIT_COUNT "Wait_Count",
    SSBSECT.SSBSECT_SESS_CODE "Load",
    SSRMEET.SSRMEET_SUN_DAY "S",
    SSRMEET.SSRMEET_MON_DAY "M",
    SSRMEET.SSRMEET_TUE_DAY "T",
    SSRMEET.SSRMEET_WED_DAY "W",
    SSRMEET.SSRMEET_THU_DAY "R",
    SSRMEET.SSRMEET_FRI_DAY "F",
    SSRMEET.SSRMEET_SAT_DAY "A",
    SSRMEET.SSRMEET_BEGIN_TIME "Begin_Time",
    SSRMEET.SSRMEET_END_TIME "End_Time",
    SSRMEET.SSRMEET_ROOM_CODE "Room",
    Ssbsect.Ssbsect_Ptrm_Code "P",
    SSRMEET.SSRMEET_START_DATE "Start_Date",
    SSRMEET.SSRMEET_END_DATE "End_Date",
    SSBSECT.SSBSECT_SSTS_CODE "C",
    SSBSECT.SSBSECT_TERM_CODE,
    SSRMEET.SSRMEET_CREDIT_HR_SESS "Credit_Hr",
    SSRMEET.SSRMEET_BLDG_CODE "Building_Code",
    ssrmeet.ssrmeet_room_code "Room_Code",
    SCBCRSE.SCBCRSE_DIVS_CODE "Divs",
    Stvterm.Stvterm_Desc,
    B.Slbrdef_Capacity
    from SATURN.SSBSECT SSBSECT,
    SATURN.STVCAMP STVCAMP,
    SATURN.SCBCRSE SCBCRSE,
    saturn.stvterm stvterm,
    Saturn.Ssrmeet Ssrmeet,
    saturn.slbrdef B
    where ( SSBSECT.SSBSECT_CAMP_CODE = STVCAMP.STVCAMP_CODE
    and SSBSECT.SSBSECT_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
    and SSBSECT.SSBSECT_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
    and SSBSECT.SSBSECT_TERM_CODE = STVTERM.STVTERM_CODE
    and SSBSECT.SSBSECT_TERM_CODE = SSRMEET.SSRMEET_TERM_CODE
    and SSBSECT.SSBSECT_CRN = SSRMEET.SSRMEET_CRN )
    And ( Ssbsect.Ssbsect_Ssts_Code ='O'
    And Ssbsect.Ssbsect_Term_Code = '201103'
    and SSBSECT.SSBSECT_CAMP_CODE IN ('1','2','3','4')
    and SCBCRSE.SCBCRSE_EFF_TERM in
    ( select Max( SCBCRSE1.SCBCRSE_EFF_TERM ) "Max_SCBCRSE_EFF_TERM"
    from SATURN.SCBCRSE SCBCRSE1
    where SCBCRSE1.SCBCRSE_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
    And Scbcrse1.Scbcrse_Crse_Numb = Scbcrse.Scbcrse_Crse_Numb
    And Scbcrse1.Scbcrse_Eff_Term <='201103' ))
    and Ssrmeet.Ssrmeet_Bldg_Code (+)= B.Slbrdef_Bldg_Code And Ssrmeet.Ssrmeet_Room_Code (+)= B.Slbrdef_Room_Number
    And B.Slbrdef_Term_Code_Eff In (Select Max(D.Slbrdef_Term_Code_Eff)
    From Slbrdef D
    Where D.Slbrdef_Bldg_Code=B.Slbrdef_Bldg_Code
    And D.Slbrdef_Room_Number=B.Slbrdef_Room_Number
    And D.Slbrdef_Term_Code_Eff <= '201103');

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You specified SSRMEET (inner)joined to SSBSECT
    and SSBSECT.SSBSECT_TERM_CODE = SSRMEET.SSRMEET_TERM_CODE
    and SSBSECT.SSBSECT_CRN = SSRMEET.SSRMEET_CRN )
    and Ssrmeet right outer joined to B(saturn.slbrdef)
    and Ssrmeet.Ssrmeet_Bldg_Code (+)= B.Slbrdef_Bldg_Code And Ssrmeet.Ssrmeet_Room_Code (+)= B.Slbrdef_Room_Number
    Isn't it a contradiction?
    Last edited by tonkuma; 04-13-12 at 13:41. Reason: Change "left joined" to "right outer joined"

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I suspected that you might misunderstand the usage of outer join operator(+).

    From "SQL Language Reference 11g Release 2 (11.2) E10592-04" page 9-12
    To write a query that performs an outer join of tables A and B and returns all rows
    from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause,
    or apply the outer join operator (+) to all columns of B in the join condition in the
    WHERE clause. For all rows in A that have no matching rows in B, Oracle Database
    returns null for any select list expressions containing columns of B.

    It must be better to use LEFT [OUTER] JOIN syntax.

    Example 1:
    Note:
    (1) Formatted the query and used [CODE] tag to preserve the format.
    (2) Changed to shorter alias for tables(used 3rd and 4th characters of each table name).
    (3) Add blanks between column names and aliases.
    (4) Remove unnecessary parentheses in WHERE clause.
    (5) Add schema name("saturn.") to "slbrdef D" in the subquery.
    (6) There might be some misunderstandings of your requirements or syntax errors. But, it would be a good starting point.
    Code:
    select bs.SSBSECT_CAMP_CODE      "Camp"
         , vc.STVCAMP_DESC           "Campus"
         , bs.SSBSECT_CRN            "Crn"
         , bs.SSBSECT_SUBJ_CODE      "Subject"
         , bs.SSBSECT_CRSE_NUMB      "Course"
         , bs.SSBSECT_SEQ_NUMB       "Section"
         , bc.SCBCRSE_TITLE          "Title"
         , bs.SSBSECT_MAX_ENRL       "Max_Enroll"
         , bs.SSBSECT_ENRL           "Enrolled"
         , bs.SSBSECT_WAIT_COUNT     "Wait_Count"
         , bs.SSBSECT_SESS_CODE      "Load"
         , rm.SSRMEET_SUN_DAY        "S"
         , rm.SSRMEET_MON_DAY        "M"
         , rm.SSRMEET_TUE_DAY        "T"
         , rm.SSRMEET_WED_DAY        "W"
         , rm.SSRMEET_THU_DAY        "R"
         , rm.SSRMEET_FRI_DAY        "F"
         , rm.SSRMEET_SAT_DAY        "A"
         , rm.SSRMEET_BEGIN_TIME     "Begin_Time"
         , rm.SSRMEET_END_TIME       "End_Time"
         , rm.SSRMEET_ROOM_CODE      "Room"
         , bs.Ssbsect_Ptrm_Code      "P"
         , rm.SSRMEET_START_DATE     "Start_Date"
         , rm.SSRMEET_END_DATE       "End_Date"
         , bs.SSBSECT_SSTS_CODE      "C"
         , bs.SSBSECT_TERM_CODE
         , rm.SSRMEET_CREDIT_HR_SESS "Credit_Hr"
         , rm.SSRMEET_BLDG_CODE      "Building_Code"
         , rm.ssrmeet_room_code      "Room_Code"
         , bc.SCBCRSE_DIVS_CODE      "Divs"
         , vt.Stvterm_Desc
         , Br.Slbrdef_Capacity
     from  SATURN.SSBSECT bs
     INNER JOIN
           SATURN.STVCAMP vc
      ON   bs.SSBSECT_CAMP_CODE = vc.STVCAMP_CODE
     INNER JOIN
           SATURN.SCBCRSE bc
      ON   bs.SSBSECT_SUBJ_CODE = bc.SCBCRSE_SUBJ_CODE
       and bs.SSBSECT_CRSE_NUMB = bc.SCBCRSE_CRSE_NUMB
     INNER JOIN
           saturn.stvterm vt
      ON   bs.SSBSECT_TERM_CODE = vt.STVTERM_CODE
     INNER JOIN
           Saturn.Ssrmeet rm
      ON   bs.SSBSECT_TERM_CODE = rm.SSRMEET_TERM_CODE
       and bs.SSBSECT_CRN       = rm.SSRMEET_CRN
     LEFT  OUTER JOIN
           saturn.slbrdef Br
      ON   rm.Ssrmeet_Bldg_Code = Br.Slbrdef_Bldg_Code
       And rm.Ssrmeet_Room_Code = Br.Slbrdef_Room_Number
     where
           bs.Ssbsect_Ssts_Code = 'O'
       And bs.Ssbsect_Term_Code = '201103'
       and bs.SSBSECT_CAMP_CODE IN ('1','2','3','4')
       and bc.SCBCRSE_EFF_TERM
           in (select Max(bc1.SCBCRSE_EFF_TERM)
                from  SATURN.SCBCRSE bc1
                where bc1.SCBCRSE_SUBJ_CODE =  bc.SCBCRSE_SUBJ_CODE
                  And bc1.Scbcrse_Crse_Numb =  bc.Scbcrse_Crse_Numb
                  And bc1.Scbcrse_Eff_Term  <= '201103'
              )
       And
      (    Br.Slbrdef_Term_Code_Eff IS NULL
       OR  Br.Slbrdef_Term_Code_Eff
           In (Select Max(D.Slbrdef_Term_Code_Eff)
                From  saturn.slbrdef D
                Where D.Slbrdef_Bldg_Code     =  Br.Slbrdef_Bldg_Code
                  And D.Slbrdef_Room_Number   =  Br.Slbrdef_Room_Number
                  And D.Slbrdef_Term_Code_Eff <= '201103'
              )
      )
    ;
    Last edited by tonkuma; 04-13-12 at 14:43. Reason: Update sample code. Add Note(3),(4),(5).

  4. #4
    Join Date
    Apr 2012
    Posts
    2

    Success!

    Thank you tonkuma, it works!

    I'm going to print out what you gave me and go through it so I understand what you're doing, but it worked. 855 rows returned.

    Thanks so much!

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If Example 1 worked well,
    Example 2 might be worth to try to see the performance comapred with Example 1.


    Example 2:
    Note:
    I afraid that "Scbcrse_Eff_Term <= '201103'" AND "Slbrdef_Term_Code_Eff <= '201103'" were not specified in your original query
    (they were specified only in subqueries),
    and the fact might be influenced to output(so, Example 2 returned incorrect result).
    Code:
    select bs.SSBSECT_CAMP_CODE      "Camp"
         , vc.STVCAMP_DESC           "Campus"
         , bs.SSBSECT_CRN            "Crn"
         , bs.SSBSECT_SUBJ_CODE      "Subject"
         , bs.SSBSECT_CRSE_NUMB      "Course"
         , bs.SSBSECT_SEQ_NUMB       "Section"
         , bc.SCBCRSE_TITLE          "Title"
         , bs.SSBSECT_MAX_ENRL       "Max_Enroll"
         , bs.SSBSECT_ENRL           "Enrolled"
         , bs.SSBSECT_WAIT_COUNT     "Wait_Count"
         , bs.SSBSECT_SESS_CODE      "Load"
         , rm.SSRMEET_SUN_DAY        "S"
         , rm.SSRMEET_MON_DAY        "M"
         , rm.SSRMEET_TUE_DAY        "T"
         , rm.SSRMEET_WED_DAY        "W"
         , rm.SSRMEET_THU_DAY        "R"
         , rm.SSRMEET_FRI_DAY        "F"
         , rm.SSRMEET_SAT_DAY        "A"
         , rm.SSRMEET_BEGIN_TIME     "Begin_Time"
         , rm.SSRMEET_END_TIME       "End_Time"
         , rm.SSRMEET_ROOM_CODE      "Room"
         , bs.Ssbsect_Ptrm_Code      "P"
         , rm.SSRMEET_START_DATE     "Start_Date"
         , rm.SSRMEET_END_DATE       "End_Date"
         , bs.SSBSECT_SSTS_CODE      "C"
         , bs.SSBSECT_TERM_CODE
         , rm.SSRMEET_CREDIT_HR_SESS "Credit_Hr"
         , rm.SSRMEET_BLDG_CODE      "Building_Code"
         , rm.ssrmeet_room_code      "Room_Code"
         , bc.SCBCRSE_DIVS_CODE      "Divs"
         , vt.Stvterm_Desc
         , Br.Slbrdef_Capacity
     from  SATURN.SSBSECT bs
     INNER JOIN
           SATURN.STVCAMP vc
      ON   vc.STVCAMP_CODE = bs.SSBSECT_CAMP_CODE
     INNER JOIN
          (SELECT bc.*
                , RANK()
                     OVER( PARTITION BY SCBCRSE_SUBJ_CODE
                                      , Scbcrse_Crse_Numb
                               ORDER BY bc.SCBCRSE_EFF_TERM DESC
                         ) AS rank_EFF_TERM
            FROM  SATURN.SCBCRSE bc
            WHERE Scbcrse_Eff_Term <= '201103'
          ) AS bc
      ON   bc.SCBCRSE_SUBJ_CODE = bs.SSBSECT_SUBJ_CODE
       and bc.SCBCRSE_CRSE_NUMB = bs.SSBSECT_CRSE_NUMB
       AND bc.rank_EFF_TERM     = 1
     INNER JOIN
           saturn.stvterm vt
      ON   vt.STVTERM_CODE = bs.SSBSECT_TERM_CODE
     INNER JOIN
           Saturn.Ssrmeet rm
      ON   rm.SSRMEET_TERM_CODE = bs.SSBSECT_TERM_CODE
       and rm.SSRMEET_CRN       = bs.SSBSECT_CRN
     LEFT  OUTER JOIN
          (SELECT br.*
                , RANK()
                     OVER( PARTITION BY Slbrdef_Bldg_Code
                                      , Slbrdef_Room_Number
                               ORDER BY Slbrdef_Term_Code_Eff DESC
                         ) AS rank_Code_Eff
            FROM  saturn.slbrdef Br
            WHERE Slbrdef_Term_Code_Eff <= '201103'
          ) AS br
      ON   Br.Slbrdef_Bldg_Code   = rm.Ssrmeet_Bldg_Code
       And Br.Slbrdef_Room_Number = rm.Ssrmeet_Room_Code
       AND br.rank_Code_Eff       = 1
     where
           bs.Ssbsect_Ssts_Code = 'O'
       And bs.Ssbsect_Term_Code = '201103'
       and bs.SSBSECT_CAMP_CODE IN ('1','2','3','4')
    ;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •