Is "Group By" Clause Supported In The Search Template For PCM_OP_SEARCH?

(Doc ID 810735.1)

Last updated on SEPTEMBER 26, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.3.0.0.0 and later
Information in this document applies to any platform.
***Checked for relevance on 30-May-2014***

Symptoms

To populate the top 10 calls usage in the bill, GROUP BY clause is used to sum up the duration and counts by grouping the calling num and called num. But the search template does not support for group by clause as error on invalid sql is thrown when tested in Developer Center.

The issue can be reproduced at will with the following steps:

1. Load and execute the search input flist in TESTNAP

nap(1107)> d 1
# number of field entries allocated 20, used 8
0 PIN_FLD_POID POID [0] 0.0.0.1 /search -1 0
0 PIN_FLD_FLAGS INT [0] 0
0 PIN_FLD_OP_CORRELATION_ID STR [0] "1:ACN-10436833:UnknownProgramName:0:AWT-EventQueue-0:7:1231758277:0"
0 PIN_FLD_TEMPLATE STR [0] " select count(event_session_ipt_call_t.dnis) from /event/session/ipt/call where ( F2 LIKE V2 and F3 = V3 ) group by event_session_ipt_call_t.dnis "
0 PIN_FLD_RESULTS ARRAY [0] allocated 20, used 2
1    PIN_FLD_AMOUNT DECIMAL [0] NULL
1    PIN_FLD_CALL SUBSTRUCT [0] allocated 20, used 1
2       PIN_FLD_DNIS STR [0] ""
0 PIN_FLD_ARGS ARRAY [1] allocated 20, used 1
1    PIN_FLD_NET_QUANTITY DECIMAL [0] 0
0 PIN_FLD_ARGS ARRAY [2] allocated 20, used 1
1    PIN_FLD_CALL SUBSTRUCT [0] allocated 20, used 1
2       PIN_FLD_DNIS STR [0] "60%"
0 PIN_FLD_ARGS ARRAY [3] allocated 20, used 1
1    PIN_FLD_CALL SUBSTRUCT [0] allocated 20, used 1
2       PIN_FLD_ANI STR [0] "10000000122"

nap(1107)> xop 7 0 1
xop: opcode 7, flags 0
XOP "7" failed: err 43:PIN_ERR_STORAGE, field 0/16:PIN_FLD_POID,
loc 4:PIN_ERRLOC_DM, errclass 1:PIN_ERRCLASS_SYSTEM_DETERMINATE, rec_id 0, resvd 0
# number of field entries allocated 20, used 2
0 PIN_FLD_POID POID [0] 0.0.0.1 /search -1 0
0 PIN_FLD_ERR_BUF ERR [0]
<location=PIN_ERRLOC_DM:4 class=PIN_ERRCLASS_SYSTEM_DETERMINATE:1
errno=PIN_ERR_STORAGE:43>
<field num=PIN_FLD_POID:7,16 recid=0 reserved=0 reserved2=0 time(sec:usec)=0:0>
<facility=0 msg_id=0 version=0>

2. Error and problem query reported in dm_oracle.pinlog:

E Tue Oct 27 21:33:04 2009 testcm dm:29999 dm_subr.c(114):7732
1:blrhpts1:testnap:1107:1:7:1237346305:1
   ORACLE error: do_sql_mr_select: PINStmtExecute: code 933, op 0
=ORA-00933: SQL command not properly ended
E Tue Oct 27 21:33:04 2009 testcm dm:29999 dm_search.c(82):1020
1:blrhpts1:testnap:1107:1:7:1237346305:1
   dm_search_rflds: do_sql_mr_select failed select event_t.poid_DB, event_t.poid_ID0, event_t.poid_TYPE, event_t.poid_REV from event_t ,event_session_ipt_call_t where ( event_session_ipt_call_t.dnis LIKE :1 and event_session_ipt_call_t.ani = :2 ) group by
event_session_ipt_call_t.dnis and event_t.poid_id0 = event_session_ipt_call_t.obj_id0

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms