Re: PL/SQL Perhaps?

Hey,

The first four fields are duplicated because they are needed to show in the VIEW and are needed to operate on. I'm not sure how else the design could be any different?

I managed to solve my problem strictly with SQL using CASE statements

Thanks

DA Morgan wrote:
> swaxolez wrote:
> > Hi there,
> >
> > Looking for some suggestions for something probably pretty easy in
> > PL/SQL. Unfortunately the one dummies book I have on PS/SQL really
> > does make me feel like a dummy. Here are the tables and views I have
> > created thus far:
> >
> > Table Trip_Info:
> >
> > ID,NAME,REQUESTDATE,ACTION
> >
> > 1> 4432,Alpha,11-Jan-06,Start Trip
> > 2> 4432,Alpha,12-Jan-06,End Trip
> > 3> 4432,Alpha,13-Jan-06,End Trip
> > 4> 4432,Alpha,14-Jan-06,Start Trip
> > 5> 4432,Alpha,13-Jan-06,End Trip
> > 6> 4433,Beta,10-Jan-06,End Trip
> > 7> 4433,Beta,11-Jan-06,Start Trip
> > 8> 4433,Beta,12-Jan-06,End Trip
> >
> > View of Trip_Info:
> >
> > ID,NAME,REQUESTDATE,ACTION,PREVREQUESTDATE,PREVIOUSACTION,NEXTACTION
> >
> > 1> 4432,Alpha,11-Jan-06,Start Trip,NULL,NULL,End Trip
> > 2> 4432,Alpha,12-Jan-06,End Trip,11-Jan-06,Start Trip,End Trip
> > 3> 4432,Alpha,13-Jan-06,End Trip,12-Jan-06,End Trip,Start Trip
> > 4> 4432,Alpha,14-Jan-06,Start Trip,13-Jan-06,End Trip,End Trip
> > 5> 4432,Alpha,16-Jan-06,End Trip,14-Jan-06,Start Trip,NULL
> > 6> 4433,Beta,10-Jan-06,End Trip,NULL,NULL,Start Trip
> > 7> 4433,Beta,11-Jan-06,Start Trip,10-Jan-06,End Trip,End Trip
> > 8> 4433,Beta,12-Jan-06,End Trip,11-Jan-06,Start Trip,NULL
> >
> > What I would like to do is add to the above view based on information
> > from the previous columns so that it looks like this:
> >
> > ID,NAME,REQUESTDATE,ACTION,PREVREQUESTDATE,PREVIOUSACTION,NEXTACTION,COMPLETE_TRIP,MISS_ACTION
> >
> > 1> 4432,Alpha,11-Jan-06,Start Trip,NULL,NULL,End Trip,NULL,NULL
> > 2> 4432,Alpha,12-Jan-06,End Trip,11-Jan-06,Start Trip,End Trip,YES,NULL
> > 3> 4432,Alpha,13-Jan-06,End Trip,12-Jan-06,End Trip,Start Trip,NULL,YES
> > 4> 4432,Alpha,14-Jan-06,Start Trip,13-Jan-06,End Trip,End
> > Trip,NULL,NULL
> > 5> 4432,Alpha,16-Jan-06,End Trip,14-Jan-06,Start Trip,NULL,YES,NULL
> > 6> 4433,Beta,10-Jan-06,End Trip,NULL,NULL,Start Trip,NULL,YESS
> > 7> 4433,Beta,11-Jan-06,Start Trip,10-Jan-06,End Trip,End Trip,NULL,NULL
> > 8> 4433,Beta,12-Jan-06,End Trip,11-Jan-06,Start Trip,NULL,YES,NULL
> >
> > So basically each COMPLETE_TRIP field and MISS_ACTION field is
> > determined to be either 'YES' or NULL from the previous columns
> > ACTION,PREVIOUSACTION,NEXTACTION. For instance, in line 2 the
> > COMPLETE_TRIP field is 'YES' because the ACTION was 'End Trip' and the
> > PREVIOUSACTION was 'Start Trip'. I have some other conditional rules
> > to determine what constitutes a MISS_ACTION. For instance, in line 3
> > the ACTION was 'End Trip' and the PREVIOUSACTION was 'End Trip' and the
> > NEXTACTION was 'Start Trip' which makes MISS_ACTION 'YES'. The idea is
> > that I want to be able to generate a report that tells me how many
> > incomplete TRIPS a person logged i.e. should have logged a 'Start Trip'
> > followed by an 'End Trip'. Unfortunately, many people seem to be
> > forgetting to do these in sequence so sometimes they log in 3
> > consecutive 'Start Trips' and then an 'End Trip' meaning the first two
> > 'Start Trips' do not tell me when the trips ended.
> >
> > So I guess I can't figure out how to set up a PL/SQL view to do this.
> > I've been looking at INSTEAD OF TRIGGER but don't see any info on how
> > to update a column with variable data. Any help or other suggestions
> > would be greatly appreciated.
> >
> > Thanks
>
> The only thing I see is a schema that appears non-relational.
>
> Table Trip_Info:
> ID,NAME,REQUESTDATE,ACTION
> 1> 4432,Alpha,11-Jan-06,Start Trip
>
> View of Trip_Info:
> ID,NAME,REQUESTDATE,ACTION,PREVREQUESTDATE,PREVIOUSACTION,NEXTACTION
> 1> 4432,Alpha,11-Jan-06,Start Trip,NULL,NULL,End Trip
>
> Why are the first four columns duplicated? Seems like the design
> needs to be fixed before any SQL is written.
>
> The rest of your request is equally obscure to me but perhaps just
> because I'm focused on the fact that the design is either flawed or
> I don't understand the business rules.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
Received on Sun Aug 06 2006 - 23:33:33 CDT