minimum function and select criteria

[bubu2006]

I have a date column called my_date in my database view. I want select data in the report when minimum(my_date)>currentdate. I can not write this in selection criteria. I have tried to create a formula but that is not available in selection criteria. Is there a way I can use this in the report? I am using Crystal XI and Oracle 11G.

Thanks,
BB

 

[fisheromacse]

have you tried a SQL expression?

(
select min("YourField")
from YourTable
)


This will return 1 value. the minimum value for your field.

 

[lbass]

Do you really mean you only want one record returned? Or is it per some group, e.g., like {table.ID}? If so, create a SQL expression {%mindt} like this:

(
select min("date")
from "table" A
where A."ID" = "table"."ID"
)

As shown (for XI), do not add the tablename within the summary--just the fieldname. Then go to report->selection formula->record and enter:

{table.date} = {%mindt}

This would return the earliest date in the table for each ID.

-LB

 

[bubu2006]

the thing is I can not use sql expression. Also I can not use min function in select criteria.

 

[lbass]

Please explain why you can't use a SQL expression--doesn't it appear as an option in your field explorer? Once you have the SQL expression you CAN use it in your record selection formula.

-LB