outer join with a nested table.

First thought is to create a temp table with parsing out the serial_list so
that the Serial Number and LocationID are in a 1 to one relation. I assume,
correctly I hope, that there is a common deliminator in the list field.
Use that deliminator to parse off the values into a temp table.

This may require the use of a cursor to seperate the data into the table.

Using a straight SQL query won't work because you have no relationship to
build off of.

Even if you were to use something like

SELECT table1.serial_nbr, table2.location_ID
FROM table1, table2
WHERE table2.locationID = (Select Location_ID from table2 where table1.serial_nbr
in (table2.serialList))

you would run the risk of a serial number representing the partial value
of another serial number and getting multiple locations for 1 serial number.

"Pamela Trout" <ptrout@raytheon.com> wrote:
>
>I have 2 tables. The first table(table_1) contains serial_nbr, site_id,

>and stock_id. The second table(table_2) contains site_id, location_id,
and
>serial_list, which is a list of serial_nbr for each location_id. I want
>a list of all the serial_nbr and the location_id. The problem is all the
>serial_nbr do not have a location_id associated with them. I tried the
following
>select statement, which did not work.
>
>SELECT T1.serial_nbr, T2.location_id
>FROM table_1 T1, table_2 T2, TABLE(table_2.serial_list) NT
>WHERE T1.site_id = T2.site_id
>AND T1.serial_nbr = NT.serial_nbr (+)
>
>Any suggestions.