Hi,

I would appreciate help on the following.
I have a column in a table. Each entry in the column can
have 0,1,2,3... maybe four entries in it.

I would like to parse the column and grab each of the entries and
feed it into another select statement. This entry that needs
to be parsed would be found through a select statement.
I would like to know what a good way is for doing this.

For example an entry in the column may be
AI732541 AI791498 AA508861
another entry may be
AA508867

Basically, in the first example
AI732541 AI791498 AA508861 would be the the answer ie. access_num for my first
select statement
Select access_num from inventory where prod_id = current_prod_id;

I would like to parse the entry
to allow me to access AI732541 to feed into another select statement
find out the answer and capture in a variable.
Then I would like to be able to access the next entry AI791498 and
perform the same task of feeding it into another select statement
and capturing the answer in a variable an so on.

I am not sure what the syntax is if I use a while loop. I don't think
that I need to involve a perl yet as I am only retrieving one row and
want to process within the row and not several rows.

thanks in advance,
bwreath

While there are ways to solve this problem using SQL, they are ugly. This table violates 1NF (the first normal form), and the only practical way to fix that is with some kind of iterative loop. I don't know of any JOIN or standard function that will fix it.

Write a short Perl script to decompose this column (probably using the split() function), and rewrite the table with only one access_num per row.

-PatP