Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Apr 2011
    Posts
    23

    Unanswered: Creating view in oracle

    I have 2 tables that I am trying to create a view from.

    They are Customer & orderheader. The primary key in customer is cust_id and there is a foreign key in orderheader table under the column order_customer which references this.

    I am trying to create a view that will return any orders that have taken more than 14 days to fufil here is my code:

    create view excessivefull AS
    select cust_firstname, cust_surname, order_id, order_date, order_completed
    from customer join orderheader using (order_customer)
    where order_completed - order_date >14
    group by cust_firstname, cust_surname, order_date, order_completed;

    when I try and run this I get an error sayingERROR at line 3:
    ORA-00904: "CUSTOMER"."ORDER_CUSTOMER": invalid identifier

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Please show us the CREATE TABLE statement for the customer and orderheader table.

  3. #3
    Join Date
    Apr 2011
    Posts
    23

    tables

    Quote Originally Posted by shammat View Post
    Please show us the CREATE TABLE statement for the customer and orderheader table.

    create table customer
    (
    Cust_firstname varchar2(40) not null,
    Cust_surname varchar2(40) not null,
    Cust_house number(4) not null constraint Cust_house check(cust_house >0),
    Cust_addr1 varchar2(100),
    Cust_addr2 varchar2(100),
    Cust_town varchar2(100),
    Cust_city varchar2(100),
    Cust_pc varchar2(8) not null,
    Cust_phone varchar2(14) constraint Cust_phone check(Cust_phone like '0%'),
    Cust_fax varchar2(14) constraint Cust_fax check(Cust_fax like '0%'),
    Cust_email varchar2(30) constraint customer_email check(Cust_email like '%@%'),
    Cust_creditlimit number(4) constraint cust_creditlimit check(Cust_creditlimit >=0),
    Cust_optout Char(1) default 'Y' constraint cust_optout check (Cust_optout in ('Y', 'N')),
    Cust_notesvarchar2(500),
    Cust_id Varchar2(10) constraint customer_id primary key
    )
    storage
    ( initial 2m
    next 1m
    pctincrease 0);

    create table orderheader
    (
    Order_id number(4) not null constraint order_id check(Order_id >0) constraint orderid_pk primary key,
    Order_date date not null,
    Order_customer varchar2(10) not null constraint ordercustomer_fk references customer(Cust_id),
    Order_delivery number(4) not null constraint order_delivery check(Order_delivery >0),
    Order_total number(4) constraint order_total check(Order_total >0),
    Order_vat number(4),
    Order_payment number(4),
    Order_completed date,
    Order_credit char(1) constraint order_credit check(Order_credit in('Y' , 'N')),
    Order_takenby number(4) constraint order_takenby check(Order_takenby >0) constraint ordertakenby_fk references employee(Emp_id)
    )
    storage
    ( initial 1m
    next 1m
    pctincrease 0);

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The "using" only works if both tables have the same column names (i.e. the primary key column of one table has the same name as the foreign key column of the other table)
    You need an explicit join (which I highly recommend over the implicit one anyway for clarity and maintenance reasons)
    Code:
    CREATE VIEW excessivefull 
    AS
    SELECT cust.cust_firstname,
           cust.cust_surname,
           ord.order_id,
           ord.order_date,
           ord.order_completed
    FROM customer cust
      JOIN orderheader ord ON cust.customer_id = ord.order_customer -- won't work because of different data types!
    WHERE ord.order_completed - ord.order_date > 14;
    There is no need to use an (expensive) GROUP BY as you are not using any aggregate functions.

    Edit: please use [code] tags in the future to make the SQL readable: http://www.dbforums.com/misc.php?do=bbcode

  5. #5
    Join Date
    Apr 2011
    Posts
    23
    Is there a way I can cobine the firstname and surname together I was doing the view that was as that was what was requested.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12

  7. #7
    Join Date
    Apr 2011
    Posts
    23
    So it would be something like this
    Code:
    Select 'Customer Name' || cust_firstname, cust_surname ||

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    yes, that is valid
    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.

  9. #9
    Join Date
    Apr 2011
    Posts
    23
    Quote Originally Posted by shammat View Post
    The "using" only works if both tables have the same column names (i.e. the primary key column of one table has the same name as the foreign key column of the other table)
    You need an explicit join (which I highly recommend over the implicit one anyway for clarity and maintenance reasons)
    Code:
    CREATE VIEW excessivefull 
    AS
    SELECT cust.cust_firstname,
           cust.cust_surname,
           ord.order_id,
           ord.order_date,
           ord.order_completed
    FROM customer cust
      JOIN orderheader ord ON cust.customer_id = ord.order_customer -- won't work because of different data types!
    WHERE ord.order_completed - ord.order_date > 14;
    There is no need to use an (expensive) GROUP BY as you are not using any aggregate functions.

    Edit: please use [code] tags in the future to make the SQL readable: http://www.dbforums.com/misc.php?do=bbcode

    I haven't been told to use this syntax the ord. and cust.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by mitchnufc View Post
    I haven't been told to use this syntax the ord. and cust.
    And your question is?

  11. #11
    Join Date
    Apr 2011
    Posts
    23
    WOULD THIS WORK


    Code:
    create view excessivefull AS
    select 'Customer Name' || cust_firstname || cust_surname, order_id, order_date, order_completed
    from customer, orderheader
    where customer.cust_id = orderheader.order_cust
    and where order_completed - order_date >14;
    this says missing expression
    Last edited by mitchnufc; 04-02-11 at 11:08.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    you have an additional where right after the and keyword.

    But do use the JOIN keyword as I showed you. That syntax is less prone to errors than the one you are using.

  13. #13
    Join Date
    Apr 2011
    Posts
    23
    ok do i not need the and clause

  14. #14
    Join Date
    Apr 2011
    Posts
    23
    I know what you mean I don't need the second where statment this is still givng me issues though.

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I know what you mean I don't need the second where statment this is still givng me issues though.
    Is COPY & PASTE broken for you?

    we can not debug what we can NOT see.
    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.

Posting Permissions

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