Date Filter

[Former Member]

Hi All,

I have a dimension "Completed Date", data type Date, its SQL:

to_date(ABC.COMPLDATE,'j')

I created a filter for it with the formula, called it MyCode to indicate below:

@Select(path\Completed Date) LIKE @Prompt('Completed Date','D','path\Completed Date',Mono,Free,Not_Persistent,{'ALL'}) OR 'ALL' IN @Prompt('Completed Date','D','path\Completed Date',Mono,Free,Not_Persistent,{'ALL'})

when i validate the formula, I got the error "ORA-00920: invalid relational operator". I do not configure out the reason why.

Moreover, I would like to enhance the filter with the requirement:"Select “ALL” by default and prompt user to select between Completion Start Date and Completion End Date from the list of values". I think I should use BETWEEN ... AND.

@Select(path\Completed Date) BETWEEN MyCodeForStartDate AND MyCodeForEndDate

I wonder if it has any problem.

Help me please! Any suggestion would be appreciated.

Thanks in advance!

[Former Member · Accepted solution]

You can't use IN with MONO. Use IN with MULTI or = with MONO depending on what you want. Using = and MONO means a single value will be expected, which it looks like you want.

The other obvious thing to point out is that ALL is not a date, so Oracle won't like it if you use ALL because it is expecting a date.

[Former Member]

I found that the error come from the default value of @prompt. It should not {'ALL'} because I was using @prompt with the datatype D (Date). However, the requirement is "Select "ALL" (all dates) by default", Is there anybody know how to achieve it?