Select 0 record if exists, else select max value

[David Abramowitz]

Have you tried using OPNQRYF with a combination "IF" using the %MAX function? Dave

[Guest.Visitor]

I have a file that "expires" records by placing a date in a field. The "current" record has a date of 0, but on some occassions all records may be expired so the current record is the one with the greatest date. I have been racking my brain for a Select statement that will select the 0 record if it exists, otherwise select the record with the greatest date value if a 0 record does not exist. Any suggestions would be greatly apprceciated.

[billhamaker@mfpoer.com]

I'm tired this morning so I'm not up to being careful about the SQL syntax but something like the following should work Select * From T where (Case when DateField = 0 Then "9999-01-01" else DateField End) = (Select Max(Case when DateField = 0 Then "9999-01-01" else DateField End) From T)