Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    57

    Unanswered: Are these joins right?

    Are these joins right?
    Having issues w/ converting from ACCESS code to Oracle11g code.




    One part of my assignment is to convert access SQL to Oracle SQL. I ran the query in MS access, here's the query result:


    ACCESS CODE
    Code:
    SELECT PUBLISHER.PUBLISHER_CODE, PUBLISHER.PUBLISHER_NAME, BOOK.TITLE, BOOK.TYPE, INVENTORY.BRANCH_NUM, BRANCH.BRANCH_NAME
    FROM PUBLISHER INNER JOIN (BRANCH INNER JOIN (BOOK INNER JOIN INVENTORY ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE) ON BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM) ON PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
    WHERE (((BOOK.TYPE)='FIC') AND ((BRANCH.BRANCH_NAME)='Henry on the Hill'))
    ORDER BY PUBLISHER.PUBLISHER_NAME;
    Thinking that the MS SQL might work in oracle, I tried it:

    Code:
    SQL> SELECT PUBLISHER.PUBLISHER_CODE, PUBLISHER.PUBLISHER_NAME, BOOK.TITLE, BOOK.TYPE, INVENTORY.BRANCH_NUM, BRANCH.BRANCH_NAME
      2  FROM PUBLISHER INNER JOIN (BRANCH INNER JOIN (BOOK INNER JOIN INVENTORY ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE) ON BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM) ON PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
      3  WHERE (((BOOK.TYPE)='FIC') AND ((BRANCH.BRANCH_NAME)='Henry on the Hill'))
      4  ORDER BY PUBLISHER.PUBLISHER_NAME;
    
    no rows selected
    Obviously that didnt work. So, I kinda did a cut/paste/edit thing into my oracle 11g. Here's what I got:

    Code:
    SQL> SELECT PUBLISHER.PUBLISHER_CODE, PUBLISHER.PUBLISHER_NAME, BOOK.TITLE, BOOK.TYPE,
      2   INVENTORY.BRANCH_NUM, BRANCH.BRANCH_NAME
      3  FROM PUBLISHER
      4   inner join BOOK  ON PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
      5  inner join INVENTORY on INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM
      6  inner join BRANCH on INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM
      7   where BOOK.BOOK_TYPE = 'FIC'
      8   and BRANCH.BRANCH_NAME = 'Henry on the Hill';
    inner join INVENTORY on INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM
                                                   *
    ERROR at line 5:
    ORA-00904: "BRANCH"."BRANCH_NUM": invalid identifier
    so why am I getting this: ERROR at line 5: ORA-00904: "BRANCH"."BRANCH_NUM": invalid identifier

    Code:
    select PUBLISHER.PUBLISHER_CODE, PUBLISHER.PUBLISHER_NAME, BOOK.TITLE, BOOK.TYPE, INVENTORY.BRANCH_NUM, BRANCH.BRANCH_NAME
    from PUBLISHER
    inner join BOOK on PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
    inner join INVENTORY on BOOK.BOOK_CODE = INVENTORY.BOOK_CODE
    inner join BRANCH on BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
    where BOOK.TYPE = 'FIC'
        and BRANCH.BRANCH_NAME = 'Henry on the Hill'
    order by PUBLISHER.PUBLISHER_NAME;
    Im still getting the 'no rows selected' error for final one, above-anyone see what I'm doing wrong?

    Does order for joins affect anything? I did go sequentially from table to table, as you can see.

    does syntax order matter? EX:

    inner join BRANCH on INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM or inner join BRANCH on BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM

    Does that matter?

    are the joins in the Oracle SQL formatted properly, in the correct order?

    If so, why won't it run?

    Side note, this is an online class, and while the instructor is quite attentive, I have learned so much here from reading others posts, and asking my own questions. Thank You everyone!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT publisher.publisher_code, 
           publisher.publisher_name, 
           book.title, 
           book.type, 
           inventory.branch_num, 
           branch.branch_name 
    FROM   publisher 
           INNER JOIN (branch 
                       INNER JOIN (book 
                                   INNER JOIN inventory 
                                           ON book.book_code = inventory.book_code) 
                               ON branch.branch_num = inventory.branch_num) 
                   ON publisher.publisher_code = book.publisher_code 
    WHERE  ( ( ( book.type ) = 'FIC' ) 
             AND ( ( branch.branch_name ) = 'Henry on the Hill' ) ) 
    ORDER  BY publisher.publisher_name;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    Code:
    SELECT publisher.publisher_code, 
           publisher.publisher_name, 
           book.title, 
           book.type, 
           inventory.branch_num, 
           branch.branch_name 
    FROM   publisher 
           INNER JOIN (branch 
                       INNER JOIN (book 
                                   INNER JOIN inventory 
                                           ON book.book_code = inventory.book_code) 
                               ON branch.branch_num = inventory.branch_num) 
                   ON publisher.publisher_code = book.publisher_code 
    WHERE  ( ( ( book.type ) = 'FIC' ) 
             AND ( ( branch.branch_name ) = 'Henry on the Hill' ) ) 
    ORDER  BY publisher.publisher_name;
    ''No rows selected'' yet again.
    this is frustrating, sure im doing everything right, though it doesnt work.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >this is frustrating, sure im doing everything right, though it doesnt work.
    Do you realize that the posted SQL is 100% identical to what you posted for ACCESS?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    >this is frustrating, sure im doing everything right, though it doesnt work.
    Do you realize that the posted SQL is 100% identical to what you posted for ACCESS?
    no, cause Ive been sitting at this comp for almost 12 hrs LOL

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
  •