Steve --

The error message just indicates that the one (or more) of the field's data type in one SELECT doesn't match up exactly with its counterpart in the other SELECT.

My impression is that you're real close. I suspect the problem is with the empty date field (in both SELECTS). Try sticking in other potential empty date values, or, even a date literal, just to get the query going. Or, perhaps, convert both to a string value, and the actual dates to strings also. Sorry that I can't be more specific -- I'm just not familiar enough with that environment.

Jay



>I didn't quite get that. I slapped an extra ; after the UNION keyword but I got an error "Selects are not Union compatible". But at any rate, the client will be inputting the cPolicySymbol and cPolicyNumber every time, so I have to fit that into the WHERE clause as well (can't use HAVING since I don't get index optimization). I am playing with your idea to use UNION but haven't had any luck so far.....
>
>Thanks.
>
>
>
>
>
>
>
>
>>Not sure if the empty date format will work properly for you.
>>
>>A couple of issues.
>>
>>1. The SELECT in itself cannot guarantee that only one record will be returned for each of the unioned selects. That's a data driven issue, and you may have problems if multiple records have the same effective or expiration date.
>>
>>2. The goal is essentially a cross tab result set. Within the constraints of a one line SQL statement, I don't know how do that. The above query returns two rows, assuming that there is only one record for each of the min and max values. Will that work for you?
>>
>> Jay