Thread: create a view

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0

    create a view


    JOB Table
    Name Null? Type
    ----------------------------------------- -------- ------------
    JOBID NOT NULL NUMBER(4)
    CLIENTID NOT NULL NUMBER(4)
    REGONO VARCHAR2(6)
    EMPLOYEEID NUMBER(4)
    PICKUPTIME NOT NULL DATE
    DELIVERYTIME NOT NULL DATE
    PICKUPADDRESS NOT NULL VARCHAR2(50)
    DELIVERYADDRESS NOT NULL VARCHAR2(50)
    COMPLETED NOT NULL CHAR(3)
    JOBDATE NOT NULL DATE

    DRIVINGHISTORY Table
    Name Null? Type
    ----------------------------------------- -------- ------------
    HISTORYID NOT NULL NUMBER(4)
    EMPLOYEEID NOT NULL NUMBER(4)
    OFFENCENO NOT NULL VARCHAR2(10)
    OFFENCETYPE NOT NULL VARCHAR2(20)
    OFFENCEDATE NOT NULL DATE
    DEMERITPOINT NOT NULL NUMBER(2)

    Conditions:
    Each row in the JOB table is a job. A set of jobs is a trip if it satisfies the following two requirements:

    1. Every member of that set has the same CLIENTID, REGONO and PICKUPTIME, and REGNO is not null;
    2. The jobs in the set can be ordered in a sequence so that for all jobs from the second to the second-last in the sequence, the DELIVERYADDRESS of one member of the sequence equals the PICKUPADDRESS of the following member of the sequence.

    The PICKUPADDRESS of the first job in a trip sequence is called FIRST and the DELIVERYADDRESS of the final job in a trip sequence is called FINAL.

    For each trip consisting of more than one job, list
    CLIENTID, REGONO, PICKUPTIME, FIRST, FINAL and the sum of the
    DEMERITPOINT of the drivers for the trip (give this column the title DEMERITS).

    I'm to create a view called TRIPS which defines the multi-job trips.

    I have difficulty with addressing how to label the trips as in how to address the delivery address of one to be the pickup address of another in the query.

    Please help with some leads.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    11
    ratumeli,

    Using Oracle 8i, I created the table:

    create table jobs (
    JOBID NUMBER(4) ,
    CLIENTID NUMBER(4) ,
    REGONO VARCHAR2(6) ,
    EMPLOYEEID NUMBER(4) ,
    PICKUPTIME DATE ,
    DELIVERYTIME DATE ,
    PICKUPADDRESS VARCHAR2(50) ,
    DELIVERYADDRESS VARCHAR2(50) ,
    COMPLETED CHAR(3) ,
    JOBDATE DATE
    );

    insert into jobs values(1,1,'NY',1,sysdate,sysdate,'NY','NJ','N',sysdate);
    insert into jobs values(1,1,'NY',1,sysdate,sysdate,'NJ','PA','N',sysdate);
    insert into jobs values(1,1,'NY',1,sysdate,sysdate,'PA','CA','N',sysdate);

    We can figure out the starting point of a trip (using a self-join and the minus set operation):


    select x
    from
    (
    select
    a.pickupaddress x, a. deliveryaddress y
    from
    jobs a, jobs b
    where
    a.jobid = 1 and
    a.deliveryaddress = b.pickupaddress
    )

    minus

    select y
    from
    (
    select
    a.pickupaddress x, a. deliveryaddress y
    from
    jobs a, jobs b
    where
    a.jobid = 1 and
    a.deliveryaddress = b.pickupaddress
    );


    Then, we can find the route using an hierarchal query:

    select pickupaddress, deliveryaddress, level
    from jobs
    start with pickupaddress = 'NY'
    connect by prior deliveryaddress = pickupaddress;

    Cheers,
    Dan
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0
    Hi Dan

    I really appreciate your time and effor you've put in here. You've made this whole exercise more clearer for me.

    Will give that a whirl.

    Thanks
    Ratu.
    Last edited by ratumeli; October 26th, 2003 at 08:43 PM.

IMN logo majestic logo threadwatch logo seochat tools logo