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
Re: delimited string as join link?
Welcome to CodeGuru forums !
Pleas, say Crystal Reports version and database type & version
JG
Re: delimited string as join link?
hiya,
crystal xi, oracle db 10g.
Thanks
Re: delimited string as join link?
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
Re: delimited string as join link?
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.
Re: delimited string as join link?
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