"Mike" < none_at_nospan.com> wrote in message
news:8tpnl0h2nf75hqji3jaejcsst0qkrtjip9_at_4ax.com...
>
>
> I can't believe my request for a couple of rows to be added to a view
> can't be done through Oracle 9.2. But then again, I am application
> developer and don't know the full limitation of Oracle. So I would
> like to propose this question to this group, which is bound to know.
>
> The main problem he is having is that the external data coming in to
> us (which we have no control over) is not consistently formatted.
> Sometimes this field is null, other times it is spaced weird, plus it
> can have multiple values. The field coming in is called RECURTIMES. It
> is a VarChar2 field that contains 3 sets of data that must be parsed.
> The data contained in this field are: DaysOfWeek, Qualifier, and
> TimesInWeek respectively, and an example would be as follows:
> 48 1 09001500;
>
> - The 48 corresponds to DaysOfWeek which can be found using a
> lookup table (48 = "Mondays and Tuesdays"
>
> - The 1 is a qualifier we ignore.
>
> - The 09001500 is a recurrent time which simply would convert
> from 9:00 AM to 3:00PM. This is the only field that could have
> multiple values.
>
> The bottom line is I would like the DBA to populate a view with a
> DaysOfWeek (e.g. "Mondays and Tuesdays") and a TimesInWeek (e.g. "from
> 9:00 AM to 3:00PM") field based on the above RECURTIMES values.
>
> OK, so far no problem as long as the data comes in consistently, but
> the some times the RECURTIMES field is NULL or it could look as
> follows:
>
> 96 1 13002000;
> 62 1 00000300 04001000;
> 62 1 00000300 04001000 12001700;
> 62 1 ;
> 62 1 ;
> 62 1 07001500;
> 62 1 06001800;
> 31 1 21000600;
>
> 0 1 07001400 ;
> 62 1 07000600;
>
>
> The DBA says all the inconsistent spaces are causing problems and that
> any function he writes can't handle more then one TimesInWeek field
> (e.g. can't handle 00000300 04001000) .
>
> Any thoughts on if this can be handled? As far as I know there are no
> limitations on what we can do on the Oracle side( we can use
> functions, procedures or packages).
>
> Thanks
>
>
Write the logic on how you want to parse the field for every case. Then
write a function in pl/sql and add the function to the view.
Jim
Received on Thu Sep 30 2004 - 09:52:27 CDT
