Display data from 2 records on same line 2

[amoeba102]

Using CR9 and Enterprise 9 against Oracle 10

I have a product table and an option table. For each product there may be many options, 2 of which are gender options.

I need 1 line per product and need to find the male option and display the maximum enrollment field from that option record, and then find the female option and display the maximum enrollment field from that option record on the same line. I can get either one, but can't get both.

I have no trouble doing it in subreports, but don't want to do it that way for other reasons. I'm probably dense and blind, but could use an assist.

 

[Turkbear]

Hi, you may need to write a Command Object ( Sql Code)
that uses an inline query ( or build a view with same)..

Like ( very pseudo)

select product,(select max(enrollment_field) from option
                where gender = 'M' and product.key = option.key) Malemax,(select max(enrollment_field) from option where gender = 'F' and product.key = option.key) Femalemax
from product;

It should give some idea of what I mean...

 

[lbass]

You might try using a crosstab, using product as the row, {option.gender} as the column and maximum of the enrollment date as the summary.

-LB