swaxolez wrote:
> 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
Sorry I missed the fact that the second was a view.
Look at CONNECT BY. There is a demo in Morgan's Library at
www.psoug.org and lots of syntax docs at tahiti.oracle.com.
--
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 Mon Aug 07 2006 - 00:14:00 CDT
