My Oracle Support Banner

Is "Group By" Clause Supported In The Search Template For PCM_OP_SEARCH? (Doc ID 810735.1)

Last updated on APRIL 02, 2018

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.3.0.0.0 and later
Information in this document applies to any platform.

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::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

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.