| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> PL/SQL Perhaps?
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,EndTrip,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 Received on Sun Aug 06 2006 - 20:22:21 CDT
 |
 |