How to return data from another record based on a table lookup 1

[aks12]

Hello, I'm using Crystal XI and need some outside advice. I have the following two tables:

Claims

[u]Claim#[/u]  [u]EventID[/u]   [u]EventDate[/u]
00123        1    4/22/10 1:23pm
00123        2    4/22/10 1:24pm
00123        3    4/22/10 1:25pm
00299        5    3/31/10 9:26am
00299        14   4/1/10 10:02am
00299        3    4/1/10 11:54am

Each EventID from the Claims table can be either the beginning or ending event of a particular claim# as seen in the following table:

Event_Pairs

[u]BeginEvent[/u] [u]EndEvent[/u]
1          18
1          -9
1           2
1           5
14          3

Output should look like:

[u]Claim#[/u]  [u]EventID[/u]  [u]Event_Begin_Date[/u]  [u]Event_End_Date[/u] 
00123        1   4/22/10 1:23pm    4/22/10 1:24pm
00123        2   4/22/10 1:24pm  
00123        3   4/22/10 1:25pm  
00299        5   3/31/10 9:26am
00299        14  4/1/10 10:02am    4/1/10 11:54am
00299        3   4/1/10 11:54am

In other words, within each unique Claim#, I want to look up its EventID from the Event_Pairs table and return the EventDate if a matching pair was found. In the example output above, the first line would show an Event_End_Date of 4/22/10 1:24pm because EventIDs 1 and 2 are a valid event pair. Likewise, in Claim# 00299, the Event_End_Date for EventID 14 is 4/1/10 11:54am because 14 and 3 are a valid event pair. If an EventID from the Claims table is only an EndEvent or does not exist in the Event_Pairs table, then its Event_End_Date should be left blank in the output.

What would be the best way to accomplish this?

 

[briangriffin]

This would almost have to be done on the database side, using a stored procedure or command object. If that's a possibility for you then provide your database type and version and I can give you a good head start in that direction.

 

[aks12]

I'm using Oracle 10g. I don't have any write access to the database, so a command object would be great.

 

[lbass]

I think you could handle this by using a left outer join FROM Claims.EventID TO EventPairs.BeginEvent and then add the Claims table again (it will appear as Claims_1) and link EventPairs.EndEvent to Claims_1.EventID using a left outer join again (From EventPairs to Claims_1). Then create a formula for {@EventEndDate}:

if {EventPairs.BeginEvent} = {Claims.EventID} and
not isnull({EventPairs.EndEvent}) and
{EventPairs.EndEvent} = {Claims_1.EventID} then
{Claims_1.EventDate}

-LB

 

[aks12]

lbass, your solution is close, but it's giving me extra rows and assigning event end dates to claim# where it shouldn't. Here's the output I get when I set up the join and formula you suggested:

Claim#  EventID Event_Begin_Date  Event_End_Date
00123	1	  4/22/10 1:23pm     3/31/10 9:26am
00123	1	  4/22/10 1:23pm     4/22/10 1:24pm
00123	1	  4/22/10 1:23pm	
00123	1	  4/22/10 1:23pm	
00123	2	  4/22/10 1:24pm	
00123	3	  4/22/10 1:25pm	
00299	5	  3/31/10 9:26am	
00299	14	 4/1/10 10:02am     4/22/10 1:25pm
00299	14	 4/1/10 10:02am     4/1/10 11:54am
00299	3	  4/1/10 11:54am

 

[lbass]

Change the formula to:

if {EventPairs.BeginEvent} = {Claims.EventID} and
not isnull({EventPairs.EndEvent}) and
{EventPairs.EndEvent} = {Claims_1.EventID} and
[red]{Claims.Claim#} = {Claims_1.Claim#}[/red] then
{Claims_1.EventDate}

-LB