Date Filter Error

[berks321]

A report that I am working on has the following filter which appears to work correctly:
[Physical Layer].[D_CAL].[CAL_SKEY] <
to_number(to_char(_first_of_month (sysdate()),'YYYYMMDD'))

However, when I try to change this filter to the following so that I use a chosen prompt end date rather than the first of the month of the sysdate:
[Physical Layer].[Transaction Date (D_CAL)].[CAL_SKEY] <
to_number(to_char(?pEndDate?,'YYYYMMDD'))

I get this error:
UDA-SQL-0114 The cursor supplied to the operation "APICursor::OpenResult" is inactive.UDA-SQL-0107 A general exception has occurred during the operation "open result".ORA-01722: invalid number RSV-SRV-0042 Trace back:RSReportService.cpp(722): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(263): QFException: CCL_RETHROW: RSReportServiceMethod::process(): pagingNextPage_RequestRSASyncExecutionThread.cpp(808): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(260): QFException: CCL_CAUGHT:

[berks321]

53 views and 0 replies. Do I need to give more detail?
Is anyone able to help?

[MFGF]

A report that I am working on has the following filter which appears to work correctly:
[Physical Layer].[D_CAL].[CAL_SKEY] <
to_number(to_char(_first_of_month (sysdate()),'YYYYMMDD'))

However, when I try to change this filter to the following so that I use a chosen prompt end date rather than the first of the month of the sysdate:
[Physical Layer].[Transaction Date (D_CAL)].[CAL_SKEY] <
to_number(to_char(?pEndDate?,'YYYYMMDD'))

I get this error:
UDA-SQL-0114 The cursor supplied to the operation "APICursor::OpenResult" is inactive.UDA-SQL-0107 A general exception has occurred during the operation "open result".ORA-01722: invalid number RSV-SRV-0042 Trace back:RSReportService.cpp(722): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(263): QFException: CCL_RETHROW: RSReportServiceMethod::process(): pagingNextPage_RequestRSASyncExecutionThread.cpp(808): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(260): QFException: CCL_CAUGHT:

What database are you using? Looks like you're calling database functions in your expression.

My guess is that ?pEndDate? isn't evaluating as a date datatype. What do you see if you replace it with #prompt('pEndDate','date')#

MF.

[Lynn]

Have you done any sort of troubleshooting to isolate the issue?

Why not create a query calculation in your layout to show what the result is before you try using it as a filter. Then you could see the result of the inner to_char bit as well as the final result with to_number.

You've also got different query references between the two filters ( [D_CAL] in the first and [Transaction Date(D_CAL)] in the second. Are those different data items that might somehow be problematic? Do all combinations of the four different elements there work or fail in the same way? Can you say for sure which change is moving you from a correct result to an incorrect result?

I haven't used Oracle in quite a while now, but my wild guess would be that the date format of the parameter isn't quite what to_char is expecting and so it isn't converting to a valid number. The above troubleshooting ideas might help you see if that is the case or not.

In addition to what Mr Muppet suggested, you can try being more explicit in your conversion from date to number, as below and eliminate the Oracle functions entirely.

extract ( year , ?pEndDate? ) * 10000
+
extract ( month, ?pEndDate? ) * 100
+
extract ( day, ?pEndDate? )

As a gentle reminder, we are all volunteering our time here so responses come based on people's available time and interest.