I have three parameters on report , one is boolean and two are dates.Condition is if user selects True from boolean parameter,it should display all data from table regardless of date ranges,whereas if user select False it should display data within date range.Problem is  its not giving any result if user selects true.when i change the parameter type from boolean to String its giviing result.I have a limitation that i cannot change the parameter type from boolean to String.Below is the query for doing that:

select event_id,event_dt  from event
where event_dt  like  decode('{?alldates}','True','%')  or event_dt between
 {?startdate} and {?endedate}

any suggestions ? 

Yeah, I agree that you should remove the quotes from 'True'. However, I'm a bit confused. I know that Boolean in SQL is a 0 or 1, Not the word true or false like it is in CR Syntax. Thus, I would swap out 'True' with 1 (or is it -1 in SQL? I forget). Secondly, the 'like' operator only works with strings. Thus, that is why it makes sense that you have to convert True to a string. But I wouldn't use a string anyway. I would do something like

where {?alldates} =1  or event_dt between  {?startdate} and {?endedate}

To fully research this, I would have to see how CR passes a boolean parameter to SQL. What exact value does SQL see on it's end? Actually, this is a great topic for the book. Please report back if you have a breakthrough. I'll have to put it on my to-do list as well.

Edited by BrianBischof - 28 Jun 2007 at 11:04am

Thanks for passing  the solution on to me. Never can tell when I will need to put it to use.