Issue with Calculating a Count using Substring, Trim and Translate

[jaymoore1756]

Can someone assist me in correcting this error

I am trying to count the number of meters by size and by year. The raw data is meter type, Here are some examples
BDGR-1"
ROCKW 1.5
From the data I need only need the number, So I am using a substring to extract the data.
substring([SQL1].[meter_tp],6,5)
This gives me following
1"
1.5"
I do not need the quote and other characters ( in other records so) I am doing this
trim(translate([Sub1],'" - L N 0',' '))
All of this works, So I create columns of meter sizes i.e 1,2 1.5 etc and run the report. The report is grouped by year and meter type.

When I run the report it works (see attachment) However when I attempt to create a subtotal using a count I get the following error ...

QE-DEF-0261 RQP - Parsing text: minimum(trim(translate(substring([Q1].[SQL1].[METER_TP] from 6 for 5),'" - L N O 0',' ')) = '5/8' auto)RQP-DEF-0149 The query specification is incorrect.RQP-DEF-0457 Referenced Query 'Query3' is not defined or its query items contain unresolved references.RSV-SRV-0042 Trace back:RSReportService.cpp(725): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(838): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(293): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(894): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(684): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(303): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(397): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(179): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(347): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(303): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(347): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(347): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(397): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSDocAssemblyDispatch.cpp(347): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSAssembly.cpp(676): QFException: CCL_RETHROW: RSAssembly::createListIteratorAssembly/RSAssembly.cpp(731): QFException: CCL_RETHROW: RSAssembly::createListIteratorRSQueryMgr.cpp(514): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(581): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(673): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(275): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrBasic.cpp(265): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(170): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(162): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1147): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1145): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1102): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1078): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4469): QFException: CCL_THROW: CoordinationPlanner

[bdbits]

They are probably still seen as strings by the query engine. Surround your existing expression with a cast to a number.

[jaymoore1756]

Tried to cast the expression as a number and I am still getting an error

[MFGF]

Tried to cast the expression as a number and I am still getting an error

...and the error is...?

[jaymoore1756]

Errors - Before I try to cast I have this ...
Column 2 [SQL1].[METER_TP]
Column 3 substring ([SQL1].[METER_TP],6,5)
Column 4 trim(translate ([Sub1],'" - L N O 0',' '))
Column 5 TRIM([mtr_size],)= '5/8'
When I run this with out a count I get this ..

Year              METER_TP                Sub1       mtr_size                    5/8
1975             SINGR-5/8"               -5/8"         5/8                           1
                     TRID10-5/8              0-5/8         5/8                           1
                     TRISL 5/8"               5/8"           5/8                           1
                     BADGR 5/8"             5/8"           5/8                           1
                     CARLN-5/8"             -5/8"          5/8                           1
                     ROCKW 5/8"            5/8"        5/8                              1

I want to count the column called 5/8, the count should be equal to 6
When I add a count using the summary function  I get the following error
Parsing error before or near position: 98 of:
"minimum(trim(trim(translate(substring([Q1].[SQL1].[METER_TP] from 6 for 5),'" - L N O 0',' '))) ="

     Details
QE-DEF-0261 RQP - Parsing text:
minimum(trim(trim(translate(substring([Q1].[SQL1].[METER_TP] from 6 for 5),'" - L N O 0',' '))) = '5/8' auto)RQP-DEF-0149 The query specification is incorrect.RQP-DEF-0457 Referenced Query 'Query3' is not defined or its query items contain unresolved
references.RSV-SRV-0042 Trace back:RSReportService.cpp(725):
QFException: CCL_CAUGHT:
RSReportService::processImpl()RSReportServiceMethod.cpp(258):
QFException: CCL_RETHROW: RSReportServiceMethod::process():
asynchRunSpecification_RequestRSASyncExecutionThread.cpp(838):
QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(293):
QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl():
asynchRunSpecification_RequestRSASyncExecutionThread.cpp(894):
QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand():
asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(684):
QFException: CCL_RETHROW:
RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(303):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79):
QFException: CCL_RETHROW:
RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(397):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(179):
QFException: CCL_RETHROW:
RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(347):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(303):
QFException: CCL_RETHROW:
RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(347):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177):
QFException: CCL_RETHROW:
RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(347):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137):
QFException: CCL_RETHROW:
RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(397):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSDocAssemblyDispatch.cpp(347):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSAssembly.cpp(676):
QFException: CCL_RETHROW:
RSAssembly::createListIteratorAssembly/RSAssembly.cpp(731):
QFException: CCL_RETHROW:
RSAssembly::createListIteratorRSQueryMgr.cpp(514): QFException:
CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(581):
QFException: CCL_RETHROW:
RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(673): QFException:
CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(275):
QFException: CCL_RETHROW:
RSQueryMgrBasic::executeRsapiCommandRSQueryMgrBasic.cpp(265):
QFException: CCL_RETHROW:
RSQueryMgrBasic::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(170):
QFException: CCL_RETHROW:
RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(162):
QFException: CCL_RETHROW:
RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1147):
QFException: CCL_RETHROW:
QFSSession::ProcessDoRequest()QFSSession.cpp(1145): QFException:
CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1102):
QFException: CCL_RETHROW:
QFSSession::ProcessDoRequest()QFSSession.cpp(1078): QFException:
CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788):
QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213):
QFException: CCL_RETHROW: QFSQuery::Execute
v2CoordinationQFSQuery.cpp(4469): QFException: CCL_THROW:
CoordinationPlanner

When I cast column 5/8 using this
TRIM(cast([mtr_size],numeric))= '5/8'
I get the following error
An error occurred while performing operation 'sqlOpenResult' status='-28'.
UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" 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(725): QFException:
CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258):
QFException: CCL_RETHROW: RSReportServiceMethod::process():
asynchRunSpecification_RequestRSASyncExecutionThread.cpp(838):
QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(293):
QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl():
asynchRunSpecification_RequestRSASyncExecutionThread.cpp(894):
QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand():
asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(684):
QFException: CCL_RETHROW:
RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(303):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79):
QFException: CCL_RETHROW:
RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(397):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(179):
QFException: CCL_RETHROW:
RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(347):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(303):
QFException: CCL_RETHROW:
RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(347):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177):
QFException: CCL_RETHROW:
RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(347):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137):
QFException: CCL_RETHROW:
RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(397):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSDocAssemblyDispatch.cpp(347):
QFException: CCL_RETHROW:
RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSAssembly.cpp(676):
QFException: CCL_RETHROW:
RSAssembly::createListIteratorAssembly/RSAssembly.cpp(731):
QFException: CCL_RETHROW:
RSAssembly::createListIteratorRSQueryMgr.cpp(514): QFException:
CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(581):
QFException: CCL_RETHROW:
RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(673): QFException:
CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(275):
QFException: CCL_RETHROW:
RSQueryMgrBasic::executeRsapiCommandRSQueryMgrBasic.cpp(265):
QFException: CCL_RETHROW:
RSQueryMgrBasic::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(170):
QFException: CCL_RETHROW:
RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(162):
QFException: CCL_RETHROW:
RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1147):
QFException: CCL_RETHROW:
QFSSession::ProcessDoRequest()QFSSession.cpp(1145): QFException:
CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1102):
QFException: CCL_RETHROW:
QFSSession::ProcessDoRequest()QFSSession.cpp(1078): QFException:
CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788):
QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213):
QFException: CCL_RETHROW: QFSQuery::Execute
v2CoordinationQFSQuery.cpp(4469): QFException: CCL_THROW:
CoordinationPlanner

[MFGF]

TRIM(cast([mtr_size],numeric))= '5/8'

What are you doing in this expression?

Doesn't the TRIM() function require a character argument?

Shouldn't you be using the cast() function on the [5/8] item not the [mtr_size] item?

You can't compare a numeric value to a character string here, and the end of your expression is

Code Select Expand

='5/8' which is a character string.

I think you are using a flawed approach here too. The final column appears to be a Boolean result, but can you cast a Boolean to a numeric?

I would use the approach:

if (TRIM([mtr_size],) = '5/8') then (1) else (0)

This will then return a numeric value (either 1 or 0) that you can sum

MF.

[jaymoore1756]

I modified the 5/8 code using the if condition and it is working. Thanks for taking the time to review.