delimited string as join link?

Hi, I'm having trouble extracting some data.

Table 1
field 1 field 2 field 3
1 a Fred
2 b John
3 c Paul

Table 2
Field 1 field 2
1 a,b
2 b,c
3 a,c


So table 2 field 2 are comma delimited values.
Each value relates to table 1 field 2.

If I know Table 2 field 1, I want to display table 1 field 3.

My current plan is to convert the string to an array and then make a loop that extracts each element of table 2 field 2 from the array and somehow equates that element to table 1 field 2 to then derive table 1 field 3.

Am I on the right track? Is this even possible in formula workshop?

Any suggestions would be grateful. Apologies if I've not made myself clear.
James

Welcome to CodeGuru forums !

Pleas, say Crystal Reports version and database type & version

JG

hiya,

crystal xi, oracle db 10g.

Thanks

AFAIK, In Crystal Reports You can extract each element of table 2 field 2 from the array, BUT You can't link each element to table 1 field 2
IMO, You should develop a Stored Procedure or a small procedure inside an application

JG

Yeah, I'd got as far as the extraction and creating a loop for the link but couldn't figure out to make the join.

Thanks for having a look.

I haven't experience with oracle's sql queries, but study the next sp and modify to apply to your case

Code:

---

CREATE PROC dbo.GetOrderList2
(
        @OrderList varchar(500)
)
AS
BEGIN
        SET NOCOUNT ON

        CREATE TABLE #TempList
        (
                OrderID int
        )

        DECLARE @OrderID varchar(10), @Pos int

        SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
        SET @Pos = CHARINDEX(',', @OrderList, 1)

        IF REPLACE(@OrderList, ',', '') <> ''
        BEGIN
                WHILE @Pos > 0
                BEGIN
                        SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
                        IF @OrderID <> ''
                        BEGIN
                                INSERT INTO #TempList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
                        END
                        SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
                        SET @Pos = CHARINDEX(',', @OrderList, 1)

                END
        END       

        SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
        FROM        dbo.Orders AS o
                JOIN
                #TempList t
                ON o.OrderID = t.OrderID
               
END
GO

---

The above stored procedure receives a list of OrderIDs separated by commas, as an input parameter.
It then parses the parameter, extracts individual OrderIDs from the comma separated list, inserts the OrderIDs into a temporary table, and then joins the temporary table with the main Orders table, to get the requested results.

(Found in my sql notebook that says it belongs to Narayana Vyas Kondreddi)

JG