where clause

[Guest]

Hi i have this where clause

WHERE
Season = <CFIF isdefined ("FORM.Years")>'#FORM.Years#'<CFELSE>2</CFIF>

this works but what i need to do is if the form is not defined then = the maximum of the column

eg if in the column was 1,1,2,2,3,3
then = 3

can this be done?

[The_ScareCrow]

Select *
From myTable
Where Season =
<CFIF isdefined ("FORM.Years")>'#FORM.Years#'
<CFELSE>
(Select Max(myColumn) From myTable)
</CFIF>

Ken

[BKBK]

Just to add that, for consistency, it has to be '#FORM.Years#' and '2' or else #FORM.Years# and 2, depending on the datatype of the season column.