Need to convert some SQL into a Crystal formula 1

[jwoodland]

I need to convert the following string of SQL to a crystal report formula,

and
(t1.f1) = (select max (t1.f1) from (t1) where
(t1.f2) = t2.f1))

{where t = table and f = field}
any help would be greatly appreciated

 

[Naith]

Hey,

I guess you're talking about a Record Selection formula here. The thing is that you can't handle SQL subqueries in Crystal like that.

If you're going to query directly against the database, you have to extract all the records in the main select, and use a subreport for the subquery. You use the grouping in the mainreport to derive that the data brought back by the subreport matches the equivalent of the subquery

(select max (t1.f1) from (t1) where (t1.f2) = t2.f1))

.

If I may, it looks like the point of this subquery is to eradicate duplicate records. Is this right? To do this, if you're going to extract everything anyway, you might as well just use the Suppress Duplicate Rows option in the details section to lose the duplicates.

Alternatively, you could group by t1.f2, order by t1.f1 ascending, suppress your details section, and place your t1.f1 information in the group footer. This would only return the maximum f1 value per f2.

As another alternative, you could also use the well documented Details conditional suppression of:

Not OnLastRecord;
f1 = Next(f1)

If I've misunderstood what your subquery is being used for, then apologies for the spam...

Naith