PCM_OP_SEARCH DISTINCT RETURNS NON DISTINCT VALUES (Doc ID 981667.1)

Last updated on JUNE 21, 2017

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.3.0.0.1 to 7.3.0.0.1 [Release 7.3.0]
Information in this document applies to any platform.
*** Checked for relevance on 06-14-2016 ***

Symptoms

-- Problem Statement:

PCM_OP_SEARCH distinct returns non distinct values.

  r << EOF 1
  0 PIN_FLD_POID                 POID [0] 0.0.0.1 /search 0 0
  0 PIN_FLD_TEMPLATE             STR  [0] " select X from /rate_plan where F1= V1 and F2 in ('/event/billing/remittance', V2 ) "
  0 PIN_FLD_FLAGS                INT  [0] 1792
  0 PIN_FLD_ARGS                 ARRAY [1] allocated 20, used 1
  1        PIN_FLD_PRODUCT_OBJ   POID [0] 0.0.0.1 /product 1930261 9
  0 PIN_FLD_ARGS                 ARRAY [2] allocated 20, used 1
  1         PIN_FLD_EVENT_TYPE   STR [0]  "/event/billing/product/fee/cycle/cycle_forward_monthly"
  0 PIN_FLD_RESULTS              ARRAY [0] allocated 20, used 2
  1         PIN_FLD_EVENT_TYPE   STR [0] NULL str ptr
  EOF
  xop 7 0 1

  
  Testnap output
  
  
  xop: opcode 7, flags 0
  # number of field entries allocated 20, used 4
  0 PIN_FLD_POID           POID [0] 0.0.0.1 /search 0 0
  0 PIN_FLD_RESULTS       ARRAY [0] allocated 20, used 2
  1     PIN_FLD_EVENT_TYPE      STR [0] "/event/billing/remittance"
  1     PIN_FLD_POID           POID [0] 0.0.0.1 /rate_plan 2018124 0
  0 PIN_FLD_RESULTS       ARRAY [1] allocated 20, used 2
  1     PIN_FLD_EVENT_TYPE      STR [0] "/event/billing/remittance"
  1     PIN_FLD_POID           POID [0] 0.0.0.1 /rate_plan 2019148 0
  0 PIN_FLD_RESULTS       ARRAY [2] allocated 20, used 2
  1     PIN_FLD_EVENT_TYPE      STR [0] "/event/billing/product/fee/cycle/cycle_forward_monthly"
  1     PIN_FLD_POID           POID [0] 0.0.0.1 /rate_plan 1929749 0 



Following is the query being fired

SQL_STMT dm_search_robj: cmd="select distinct event_type, poid_DB, poid_ID0, poid_TYPE, poid_REV from rate_pla
n_t where rate_plan_t.product_obj_ID0 = :1 and rate_plan_t.event_type in ('/event/billing/remittance', :2 ) "
W Fri Oct 30 04:49:27 2009 zelda dm:21231 dm_subr.c(116):7311 1:zelda:testnap:4489:1:7:1256138162:1



Expectation is : Query should be fired as below

select distinct event_type from rate_plan_t
where product_obj_id0 = 1930261
and event_type in ('/event/billing/product/fee/cycle/cycle_forward_monthly','/event/billing/remittance');



Returns all the records since the poid is unique for all the rows.  Need to retrieve only the distinct event_type in result array.

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