Results 1 to 14 of 14

Thread: view help

  1. #1
    Join Date
    Apr 2011
    Posts
    23

    Unanswered: view help

    Here is my view code
    Code:
    create or replace view excessivefullorders_view as
    select 'Customer name is ' || cust_firstname || ' ' || cust_surname || ' order number ' || order_id ||  ' placed on '   || order_date || ' fulfilled on ' order_completed
    from customer
    join orderheader on customer.cust_id = orderheader.order_customer
    where order_completed - order_date >14;
    now everything works apart from the fact I don't get anything returned for order_completed which is a date also from using the above how could i find out the days exceeded i.e if a order has taken 16 days to completed then it is over by say 10days.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suggest you separate & differentiate data extraction operation from data presentation operation.

    In other words remove the static strings from the VIEW definition.
    Of course, you are free to simply ignore this advice.

    >how could i find out the days exceeded
    What exceeded what?
    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
    Apr 2011
    Posts
    23

    We have to follow that spec

    so it needs to show this when select * from viewname

    Customer Name (cust_firstname + cust_surname) order number (order_id) placed on (order_date) fufilled on (order_complete) number od days exceeded.

    i've tryed sticking a || after and before order_completed but it comes with column alias error

    In order to find the days exceeded I basillay need to do a cancluation to return the number of days over 14 an order has taken to complete:

    says an order_ date was 10-MAR-11 and order_completed was 30-MAR-11 then it should return 6 says exceeded. hope this makes sense.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  select '10-MAR-11' DATE_ORDERED, '30-MAR-11' ORDER_COMPLETED,
      2* (TO_DATE('30-MAR-11','DD-MON-RR')-TO_DATE('10-MAR-11','DD-MON-RR')-14) DAYS_EXCEEDED FROM DUAL
    SQL> /
    
    DATE_ORDE ORDER_COM DAYS_EXCEEDED
    --------- --------- -------------
    10-MAR-11 30-MAR-11		6
    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
    Apr 2011
    Posts
    23
    ok I see what to do with the exceeded part but I still can't get my first question to work why won't this work:

    Code:
    create or replace view excessivefullorders_view as
    select 'Customer name is ' || cust_firstname || ' ' || cust_surname || ' order number ' || order_id ||  ' placed on '   || order_date || ' fulfilled on ' || order_completed
    from customer
    join orderheader on customer.cust_id = orderheader.order_customer
    where order_completed - order_date >14;

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This query is different from the one you first posted. What does not work with it?

  7. #7
    Join Date
    Apr 2011
    Posts
    23
    Managed to get the first one finished now. having problems with the second one thouh it says ORA-01858: a non-numeric character was found where a numeric was expected

    here is the code

    Code:
    create or replace view unfufilledorders_view AS
    select cust_firstname ||' '|| cust_surname "Customer Name", order_id "Order", order_date "Placed on", TO_DATE(Order_completed)-TO_DATE(SYSDATE) "Number of Days outstanding"
    from customer
    join orderheader on customer.cust_id = orderheader.order_customer
    where order_completed = 'NULL';
    They above lets me create the view but when I run select * from unfufilledorders_view I get the error message at the top instead of returning multiple orders which have no complete date.

  8. #8
    Join Date
    Apr 2011
    Posts
    23
    Code:
    create or replace view unfufilledorders_view AS
    select cust_firstname ||' '|| cust_surname "Customer Name", order_id "Order", order_date "Placed on", TO_DATE(SYSDATE)-TO_DATE(ORDER_DATE) "Number of Days outstanding"
    from customer
    join orderheader on customer.cust_id = orderheader.order_customer
    where order_completed = 'NULL';
    This is the correct code but having the same problem as above.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by mitchnufc View Post
    TO_DATE(SYSDATE)-TO_DATE(ORDER_DATE)
    to_date() converts a character string into a "real" date.
    sysdate is already a date, so it is absolutely senseless to apply to_date() on it (why would you want to convert a date into a date?)

    If this is the same table definition as in your previous post, tehn the same goes for order_date. Is it is already a date - there is no sense in applying to_date() on it

    Try sysdate - order_date instead.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >where order_completed = 'NULL';
    Comparing a DATE to a STRING, will likely always be FALSE
    Last edited by anacedent; 04-04-11 at 19:58.
    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.

  11. #11
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    >where order_completed = 'NULL';
    Comparing a DATE to a STRING, will likely always be FALSE
    Very true, I missed that one.

    Most probably that should be
    Code:
    where order_completed IS NULL;
    mitchnufc: note the missing single quotes around the keyword NULL

  12. #12
    Join Date
    Apr 2012
    Posts
    3

    adding more

    How would you calculate another value in this view with the total number of items outstanding?

    count(orderline_fulfilled) "Items outstanding"

    ...


    orderline_fulfilled = 'N';


    The order line table has a foreign key to the order table via orderline_number

    Thanks

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    NOTBINARY,

    Welcome to this forum.

    Consider reading & following URL below
    http://www.dbforums.com/oracle/10316...s-posters.html

    I suggest you start you own/new thread & stop hijacking threads started by others
    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.

  14. #14
    Join Date
    Apr 2012
    Posts
    3
    Im sorry I am aware of thread protocol etc. Just thought it would be easier as its relevant to this exact question and would avoid repetition on the forum.

Posting Permissions

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