deafult value for #prompt#

[lawrencebenson]

Hello everyone,

I am using Report Studio 8.2.

I am trying to make the sql dynamic so the filter can be applied to the data prior to grouping it.  I have had no luck trying to make the following SQL statement work using a #prompt# with a default value for handling cases where no filter is passed but I am having no luck:


Select  a.evt_org, a.evt_class, b.cls_desc, trunc(avg(trunc(sysdate) - trunc(a.evt_Created))) AvgAge, count(1) "Count"
from (
        select * from r5events where evt_priority like #prompt('p_priority','varchar','%')#
        ) a,
r5classes b
where a.evt_rstatus = 'R'
and a.evt_class = b.cls_code
and a.evt_org = b.cls_org
and b.cls_entity = 'EVNT'
group by a.evt_org, a.evt_class, b.cls_desc

I get the following error:
An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.

UDA-SQL-0107 A general exception has occurred during the operation "prepare". ORA-00911: invalid character RSV-SRV-0042 Trace back: RSReportService.cpp(666): UDASQLException: CCL_CAUGHT: RSReportService::process() RSReportService.cpp(611): UDASQLException: RSAsyncThrowable: RSReportService::process RSASyncExecutionThread.cpp(203): UDASQLException: CCL_CAUGHT: RSASyncExecutionThread::run(): asynchRunSpecification_Request RSASyncExecutionThread.cpp(656): UDASQLException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_Request RSQueryMgr.cpp(1466): UDASQLException: CCL_RETHROW: RSQueryMgr::executeRsapiCommand QFSSession.cpp(1479): UDASQLException: CCL_RETHROW: QFSSession::ProcessDoRequest() QFSSession.cpp(1477): UDASQLException: CCL_CAUGHT: QFSSession::ProcessDoRequest() QFSSession.cpp(1444): UDASQLException: CCL_RETHROW: QFSSession::ProcessDoRequest() QFSConnection.cpp(1671): UDASQLException: CCL_RETHROW: QFSConnection::DescribeDataSourceQuery QFSQuery.cpp(450): UDASQLException: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery QFSQuery.cpp(450): UDASQLException: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery Source/AQE_UDAFacade.cpp(1685): UDASQLException: CCL_RETHROW: apidyn.cpp(240): UDASQLException: CCL_RETHROW: sqlPrepareWithOptions


Any alternate solutions are welcome.

[roadwarrior]

Try the following syntax:

query_item like #sq(prompt( 'prompt_name', 'token', '%' ))#

when prompted, if you leave the response blank you will get the % as the default as desired, and you should return all results.
when prompted, if you enter A%, you will return all rows where query_item starts with A.