delimited string as join link?

[jimbojames79]

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

[jggtz]

Welcome to CodeGuru forums !

Pleas, say Crystal Reports version and database type & version

JG

[jimbojames79]

hiya,

crystal xi, oracle db 10g.

Thanks

[jggtz]

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

[jimbojames79]

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.

[jggtz]

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

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