1 Reply Latest reply: Jun 7, 2013 8:36 AM by user12111409 RSS

    htmldb_item.select_list_from_lov: drop down shows duplicate values

    user12111409
      Hello,

      I am using Apex 3.2 on Oracle 11g.

      I wasn't able to locate similar issues here in the forum.

      I have a report region where one of the columns is defined as htmldb_item.select_list_from_lov.

      The LOV is a shared component LOV : MODULE_LOV

      This is the structure of the select statement for the LOV:

      select distinct modname d, modoid r
      from (SELECT DISTINCT a.module_name modname, a.module_oid modoid
      FROM T_WAO_MODULE a, T_WAO_OEM_MODULE b
      where a.module_oid = b.module_oid
      and   b.wao_oem_oid = :F512_WAO_ENTY_OID)

      The drop down is rendering duplicate values for the current value which is in the report region.

      I would like to have included a screen shot but I didn't see a way to do that.

      It is probably overkill using distinct the way I di, but I was trying to get rid of the duplicates.

      I have verified and reverified that the table, T_WAO_OEM_MODULE, does not
      contain duplicate modules for each :F512_WAO_ENTY_OID. The parent table,
      T_WAO_MODULE does not either.

      This is the structure of the column in question in the report region. I have verified that the data from this main select is showing correctly.

      HTMLDB_ITEM.SELECT_LIST_FROM_LOV(MOD (ROWNUM - 1, 50) + 1,mod_name,'MODULE_LOV') || HTMLDB_ITEM.hidden (MOD (ROWNUM - 1, 50) + 1, mod_name) mod_name,

      Below is the sub select rendering mod_name for the above select:

      FROM
      ( SELECT f.fld_name,
      f.fld_dscr,
      NVL (w.rqr_ind, '3') AS rqr_ind,
      NVL (w.PASS_THRU_FLD_IND, 'N') AS hidden_ind,
      NVL (w.PASS_THRU_FLD_IND, 'N') AS hidden_ind_old2,
      m.module_name mod_name,
      m.module_name mod_name_old2,
      w.fld_type_code field_type_code
      FROM T_WAO_PTNR_ENTY_PROFILE_FIELD w,
      T_WAO_APPLICATION_FIELD f,
      T_WAO_MODULE m,
      T_WAO_OEM_MODULE om
      WHERE w.fld_name = f.fld_name
      AND f.FLD_APLCBL_TO_CRDT_APLCN_IND = 'Y'
      AND w.PRFL_APLCBL_TO_TYPE_CODE = 'DEALMGMT'
      AND SUBSTR (f.fld_name, 1, 3) != 'co_'
      AND SUBSTR (f.fld_name, 1, 13) != 'contractTerms'
      AND w.wao_ptnr_enty_type_code = :p31_ptnr_enty_type_code_oem
      AND ( f.PROD_TYPE_code = 'A'
      OR f.PROD_TYPE_code = :P31_PRODUCT_TYPE_OEM)
      AND ( f.CRDT_APLCN_TYPE_CODE = 'C'
      OR f.CRDT_APLCN_TYPE_CODE = :P31_APP_TYPE_OEM)
      AND w.wao_ptnr_enty_oid = :p31_ptnr_enty_oid
      AND w.wao_ptnr_enty_oid = om.wao_oem_oid(+)
      AND w.module_oid = om.module_oid(+)
      AND om.module_oid = m.module_oid(+)
      ORDER BY f.page_num, f.sort_ord);

      Any suggestions would be helpful.

      Thank you.