SQL SELECT MAX

[Jewel142]

Hi -

I'm working in Crystal 2008. I have a form that generates a form for all 11830 employees and retirees that we have. There are several tables used to generate this form but the two that I'm usting to create my SQL statement are EMPLOYEE and PAPOSITION. The EMPLOYEE table only has one record per employee. However, the PAPOSITION contains numerous records for the same position number as funding numbers change (The two tables are linked via the position number).

Because of this, I'm getting duplicate forms even though I the Distinct Records selected. I'm trying to write a SQL command that returns only the maximum or must current date for PAPOSITION table. I've tried writing the following command but I keep getting an error. Any suggestions?

SELECT "PAPOSITION"."EFFECT_DATE"

FROM "PAPOSITION"

WHERE "PAPOSITION"."EFFECT_DATE" = (select Max("PAPOSITION"."EFFECT_DATE") from PAPOSITION)


Jewel

 

[lbass]

You could just write a SQL expression (field explorer->SQL expression->new) like this, which I'm calling {%maxdate}:

(
select max(A."EFFECT_DATE")
from PAPOSITION A
where A."POSITION_NO" = "PAPOSITION"."POSITION_NO"
)

This syntax should work in CR2008 (or in 8.0 or 8.5), but not in other versions. Then in your main report, use a record selection formula like this:

{PAPOSITION.EFFECT_DATE} = {%maxdate}

If you want to use a command as the entire datasource for your report, you would handle this a little differently.

-LB