Trouble building select statement... 1

[Bvontur]

Hello!

I'm running Crystal Reports 8.5 on Windows NT accessing a DB2 database.

Here's my sources:

TABLE1

RefNum....Received
123321....01/01/2003
123322....04/01/2003
123323....04/04/2003

TABLE2

RefNum....Action
123321....A
123321....D
123321....F
123322....A
123322....F
123323....A
123323....C
123323....C
123323....F

I want to return the data from table2 for each RefNum if one of the actions is D. My result set from the mystery sql/procedure would be:

123321....A
123321....D
123321....F

Thanks in advance for any help.

-Brad

 

[mbarron]

Here how I would do it/have done it.

You'll need to group on the RefNum

Create a formula and place it in your details section:
if action="D" then 1

insert a summary for this field - maximum or sum will work

then use your group select expert to display all groups where the summary field <>0



Mike

 

[Bvontur]

That did it. Thank you very much.

-Brad

 

[GMcNamara]

I think this may be easier, more efficient if you handled this in the SQL statement. Something like:

select [fields] from table1
left outer join table2
where refnum in (select refnum from table2 where action = 'D')


Not that I disagree with mbarron, just wanted to offer another option.