Re: more pl/sql question

From: TurkBear <johng_at_mm.com>
Date: Tue, 16 Jun 1998 14:15:49 GMT
Message-ID: <358679bf.1052243@news2.mm.com>

Its tough to work with someone else's badly designed stuff, isn't it...

If there is always a space ( and only one space) between the sql_ids in the returned row, then it would be possible to use the instr function to parse out the substrings form the row...use the value of the first one plus 1 to set the start value for the next....

for example
instr(row_1,1,' ') will return a number representing the first space

                   in the field - use this in a substr

function to get the actual value:
substr(row_1,1,value_returned_above -1) then

                   instr(row_1,value_returned_above+1,' ')
                   will get the next space position; use 
                   it in the next substr......
                   do this until instr() returns 0 at which point
                   use substr(row_1,last_value_returned+1)
                   to get the last part of the string....

This is not easy and is fraught with possible errors, but I don't know of another way to get the values you need for your subsequent where clause....
Btw,
( I would use the technique above to create a view of the table   and use the view in the where clause)

Email me at john.greco_at_dot.state.mn.us with questions....

David Doll <djd_at_saul5.u.washington.edu> wrote:

>
>I've been told my previous message didn't make much sense; sorry, to much
>work and not enough of sleep :-)
>
>I'll try again: the question is, is it possible to parse out multiple
>values of a value returned in a pl/sql select statement. For example:
>
>select seq_ids
>from table1
>
>returns these:
>
>61417 6298 123 98705 33489 43265 48495
>
>So the field seq_ids contains one or more groups of id numbers - I
>didn't set this up, just using something someone else wrote.
>
>Each of these numbers are then needed for a different select statment,
>something like:
>
>select dna_sequence
>from table2
>where id = 61417;
>
>which would return something like:
>
>GTAGACTTTCTAAAATAAATAC
>
>So the pl/sql code that has to be written would take the seq_ids
>(indivual id's, not the group of id's), and its dna_sequence, along
>with some admin info, and insert that info into a summary table. Any
>suggestions as to how, if at all possible, to parse out the indivual
>ids and use them in another select would be greatly
>appreciated. Thanks.
>
>David
Received on Tue Jun 16 1998 - 09:15:49 CDT