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

    Unanswered: multi table query / joins

    The problem: List customer name, order date, number ordered, quoted price, amount billed and description for items ordered from Premiere Products. Organize this report by order date with customer name
    The tables:

    Code:
    SQL> desc customer
     Name
     ----------------------
     CUSTOMER_NUM
     CUSTOMER_NAME
     STREET
     CITY
     STATE
     ZIP
     BALANCE
     CREDIT_LIMIT
     REP_NUM
    
    SQL> desc orders
     Name
     ----------------------
     ORDER_NUM
     ORDER_DATE
     CUSTOMER_NUM
    
    SQL> desc order_line
     Name
     ----------------------
     ORDER_NUM
     PART_NUM
     NUM_ORDERED
     QUOTED_PRICE
    
    SQL> desc part
     Name
     ----------------------
     PART_NUM
     DESCRIPT
     ON_HAND
     CLASS
     WAREHOUSE
     PRICE
    Here's what I am using for a query, and the result:
    Code:
     SQL>  SELECT CUSTOMER.CUSTOMER_NAME, ORDERS.ORDER_DATE,
       ORDER_LINE.NUM_ORDERED, ORDER_LINE.QUOTED_PRICE,
       SUM(Num_Ordered*Quoted_Price) AS Amt_Billed,
       PART.DESCRIPT
       FROM customer
       INNER JOIN ORDERS ON CUSTOMER.CUSTOMER_NUM =ORDERS.CUSTOMER_NUM
       INNER JOIN ORDER_LINE ON ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM
       Inner JOIN ORDER_LINE ON PART.PART_NUM = ORDER_LINE.PART_NUM;
       Inner JOIN ORDER_LINE ON PART.PART_NUM = ORDER_LINE.PART_NUM
                                *
    ERROR at line 8:
    ORA-00904: "PART"."PART_NUM": invalid identifier
    It runs fine in Access, but not in oracle11g.

    HELP!

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    Hi, i don't see how it can possibly run in access. Where is the part table in your code?
    I've just had a loo at your other threads. It appears that you can't be bothered to acknowledge when other posters try to help you. Shame.
    Last edited by pablolee; 10-04-13 at 07:16.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    you reap what you sow.
    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.

  4. #4
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by pablolee View Post
    Hi, i don't see how it can possibly run in access. Where is the part table in your code?
    I've just had a loo at your other threads. It appears that you can't be bothered to acknowledge when other posters try to help you. Shame.
    I did play w/ the access code a bit.
    Part is a linking table, maybe. Either way, point noted.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >for items ordered from Premiere Products.
    How is above satisfied?
    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.

  6. #6
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    >for items ordered from Premiere Products.
    How is above satisfied?
    Not sure I understand what you're asking?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by bwilson95 View Post
    Not sure I understand what you're asking?
    for this discussion ignore all other requirement; just satisfy the requirement below

    post SELECT statement which returns all items from "Premier Products"
    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.

  8. #8
    Join Date
    Sep 2013
    Posts
    57
    as you can see,(end line) the instructor specifies how many rows/columns.
    List customer name, order date, number ordered, quoted price, amount billed and description for items ordered from Premiere Products. Organize this report by order date with customer name.
    Code:
    SQL> SELECT
      2        c.customer_name,
      3        o.order_date,
      4        ol.num_ordered,
      5        ol.quoted_price,
      6        ol.num_ordered * ol.quoted_price as amt_Billed,
      7        p.descript
      8     from
      9        customer c
     10           join orders o
     11              ON c.customer_num = o.customer_num
     12              join order_line ol
     13                 ON o.order_num = ol.order_num
     14                 join part p
     15                    ON ol.part_num = p.part_num
     16     order by
     17        o.order_date,
     18        c.customer_name;
    
    CUSTOMER_NAME                       ORDER_DAT NUM_ORDERED QUOTED_PRICE AMT_BILLED DESCRIPT
    ----------------------------------- --------- ----------- ------------ ---------- --------------
    Al's Appliance and Sport            20-OCT-10          11        21.95     241.45 Iron
    Ferguson's                          20-OCT-10           1          495        495 Gas Range
    Ferguson's                          20-OCT-10           1       399.99     399.99 Washer
    Brookings Direct                    21-OCT-10           2          595       1190 Dishwasher
    The Everything Shop                 21-OCT-10           4       329.95     1319.8 Dryer
    Al's Appliance and Sport            23-OCT-10           1          495        495 Gas Range
    Johnson's Department Store          23-OCT-10           2         1290       2580 Treadmill
    Johnson's Department Store          23-OCT-10           4          150        600 Microwave Oven
    Johnson's Department Store          23-OCT-10           2       794.95     1589.9 Home Gym
    6 columns, 9 rows.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    is Premier Products just the fictitious name of the company running this business.

    Without knowing the background it appeared to be something like below was part of the SQL

    WHERE PROVIDER = 'PREMIER PRODUCTS'
    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.

  10. #10
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    is Premier Products just the fictitious name of the company running this business.

    Without knowing the background it appeared to be something like below was part of the SQL

    WHERE PROVIDER = 'PREMIER PRODUCTS'
    There is no 'Provider'

    This is a fictitious DB created for this class. I mean, it's real when I log onto the cmd prompt interface, but Premier Products doesn't exist outside of the DB.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    does below get you closer to a solution?
    Code:
    SELECT CC.customer_name, 
           OO.order_date, 
           OL.num_ordered, 
           OL.quoted_price, 
           SUM(num_ordered * quoted_price) AS Amt_Billed, 
           PP.descript 
    FROM   customer CC, 
           orders OO, 
           order_line OL, 
           part PP 
    WHERE  CC.customer_num = OO.customer_num 
           AND OO.order_num = OL.order_num 
           AND OL.part_num = PP.part_num
    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.

  12. #12
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    does below get you closer to a solution?
    Code:
    SELECT CC.customer_name, 
           OO.order_date, 
           OL.num_ordered, 
           OL.quoted_price, 
           SUM(num_ordered * quoted_price) AS Amt_Billed, 
           PP.descript 
    FROM   customer CC, 
           orders OO, 
           order_line OL, 
           part PP 
    WHERE  CC.customer_num = OO.customer_num 
           AND OO.order_num = OL.order_num 
           AND OL.part_num = PP.part_num
    I got it, instructor encourages group work, so I got on the school i/m, and worked on it w/ 3 other students.

Posting Permissions

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