Clarity

  • 1.  NSQL - Filter by OBS in a porlet

    Posted Aug 18, 2017 04:11 PM

    Hello, I contact with the community because I need to include in my sql query an OBS filter.
    We have defined a OBS that contain the organizational structure. The name of this field, in project's objetc is "OBS Ejecucion", and this field use the browse " OBS_BROWSE_FLT_PRJ". I need to filter for one of this structure.

    The query in SQL looks like this:

     

    Select COUNT(*) AS CANTIDAD, lookup.NAME as Tipo

    from niku.INV_INVESTMENTS as inv
    inner join niku.ODF_CA_INV as ca on inv.ID=ca.ID
    inner join niku.ODF_CA_PROJECT as prj on inv.ID=prj.ID
    inner join niku.CMN_LOOKUPS_V as lookup on obj_request_type=lookup.LOOKUP_CODE

    where inv.IS_ACTIVE=1
    and lookup.LOOKUP_TYPE= 'OBJ_IDEA_PROJECT_TYPE' and lookup.LANGUAGE_CODE='es'
    and (obj_request_type = 'type100' or obj_request_type = 'type200' or obj_request_type = 'type300') --Oro / Plata / Bonce--
    and (osde_res_sol_cc = '01') -- Alta Aprobada

    and (HOW TO FILTER BY OBS)????? = '' /OSDE Binario/Dirección de Procesos y Sistemas/Gerencia de Soluciones de Negocio"

    group by lookup.NAME

     

    Anyone can help me with this query?
    Regards.
    Patricio.



  • 2.  Re: NSQL - Filter by OBS in a porlet
    Best Answer

    Broadcom Employee
    Posted Aug 18, 2017 09:42 PM

    When you write a query to get OBS information in the filter section, the obs structure should not be same as OBS association browse in the project (i.e Hierarchy structure).

     

    You need to write a query to get the corresponding OBS information like OBS ID, OBS Unique Name, OBS Name, Depth & OBS Path in the query.

     

    Query as below

     

    SELECT U.ID, U.UNIQUE_NAME, U.NAME, U.DEPTH, SUBSTR(NBI_GET_OBS_PATH_FCT(U.ID), 4, LENGTH(NBI_GET_OBS_PATH_FCT(U.ID)))
    OBS_PATH FROM PRJ_OBS_UNITS U JOIN PRJ_OBS_TYPES T ON U.TYPE_ID = T.ID AND T.UNIQUE_NAME = 'organizational'

     

     

    Thanks

    Senthil



  • 3.  Re: NSQL - Filter by OBS in a porlet

    Posted Aug 22, 2017 04:43 PM

    Thanks for your help Senthil, but how to write this condition?

     

     

    Regards.

    Patricio.



  • 4.  Re: NSQL - Filter by OBS in a porlet

    Posted Aug 21, 2017 03:52 AM

    I usually use a bit of code as below to add a "param_obs" filter to a NSQL portlet - associate the param_obs attribute with the OBS Projects Filter Browse lookup in the query to get it to work.

     

    (inv_investments being aliased to i in the main part of the query)

     

    AND
    (
         @WHERE:PARAM:USER_DEF:INTEGER:OBS@ IS NULL
         OR
         (
              SELECT pou.id AS prj_unit_id
                FROM prj_obs_associations poa
                JOIN prj_obs_units pou ON pou.ID = poa.unit_id
               WHERE poa.table_name = 'SRM_PROJECTS'
                 AND poa.record_id = i.ID
                 AND pou.type_id = (SELECT P.type_id
                                           FROM prj_obs_units P
                                          WHERE P.id = @WHERE:PARAM:USER_DEF:INTEGER:OBS@ )
         )
         IN
         (
         SELECT F.unit_id
           FROM prj_obs_units_flat F
          WHERE branch_unit_id = @WHERE:PARAM:USER_DEF:INTEGER:OBS@
         )
    )