AnsweredAssumed Answered

NSQL - Differences in results between a direct SQL statement and via Clari

Question asked by ca.portal.admin on Apr 21, 2010
Latest reply on Apr 21, 2010 by Dave
Hi all!  I'm a newbie to Clarity and I'm having many problems with it.  I'm trying to figure if Clarity does any security check when a user executes a query via a Lookup that contains a Dynamic Query.I have created a NSQL query that I execute via my Oracle client, and the results are ok; but when I open the Lookup that contains that query it doesn't show me any results.  It's a query that combines custom objects with OBS security, and I can't figure out why works in plan SQL commands and it does not when throwing it via Clarity.  When executing it via my Oracle client, I modify @WHERE:PARAM:USER_ID@   with the user_id in SRM_RESOURCES table (I have already checked that the same code is used both in Clarity and in my Oracle client).  I think that Clarity might apply some restrictions that I am not aware of, maybe a OBS security filter.  The NSQL is like this:  SELECT      br.id BR_ID,
     br.ib_br_code BR_CODE,
     br.name BR_NAME,
     br.IB_BR_TYPE2 BR_TYPE_ID,
     br.IB_BR_SOURCE2 BR_SOURCE_ID,
      prnt.id BR_PRNT_ID,
     prnt.code BR_PRNT_CODE,
     prnt.name BR_PRNT_NAME,
     rm.id RM_ID,
     rm.code RM_CODE,
     rm.name RM_NAME,
     rm.IB_BRM_YEAR RM_YEAR,
     rm.IB_BRM_ISHOLDING RM_ISHLD,
     Q.path as BR_PATH, Q.LVL as BR_LVL  -- This is a hierarquical query to draw something like a treeFROM      odf_ca_ib_busrisk br
     INNER JOIN (SELECT V.ID, V.NAME, V.LVL, V.PATH
                 FROM (SELECT r.ID, r.NAME, LEVEL AS LVL, SYS_CONNECT_BY_PATH(r.name, '\') AS PATH
                            FROM odf_ca_ib_busrisk r
                            CONNECT BY PRIOR r.id = r.IB_BR_PARENT)
                 V
                 INNER JOIN (SELECT ID, max(LVL) mlvl
                            FROM (SELECT r.ID, r.NAME, LEVEL AS LVL, SYS_CONNECT_BY_PATH(r.name, '\') AS PATH
                                 FROM odf_ca_ib_busrisk r
                             CONNECT BY PRIOR r.id = r.IB_BR_PARENT)
                            group by id)
                 W ON W.id = V.ID
                 WHERE V.lvl = w.mlvl)
              Q ON Q.id = BR.ID
INNER JOIN odf_ca_ib_busriskmap rm ON rm.id = br.ODF_PARENT_ID
LEFT JOIN odf_ca_ib_busrisk prnt ON prnt.id = br.IB_BR_PARENT
WHERE @FILTER@
AND
-- There we select those RMs that belong to the OBS the user is part of (My OBS) and those which have the attribute IS_HOLDING set to 1
(rm.id in (SELECT BRM.ID
           FROM PRJ_OBS_ASSOCIATIONS poa
           INNER JOIN odf_ca_ib_busriskmap BRM on BRM.ID = poa.RECORD_ID
           WHERE poa.unit_id = (
                 SELECT poa.unit_id
                 FROM PRJ_OBS_ASSOCIATIONS poa
                 INNER JOIN PRJ_OBS_UNITS pou ON pou.ID = poa.unit_id
                 INNER JOIN SRM_RESOURCES SRES on sres.id = poa.RECORD_ID
                 WHERE sres.user_id = @WHERE:PARAM:USER_ID@  
                 AND poa.TABLE_NAME = 'SRM_RESOURCES')
           AND poa.TABLE_NAME = 'ib_busriskmap')
OR rm.id in (SELECT BRM.ID
           FROM odf_ca_ib_busriskmap BRM
           WHERE BRM.IB_BRM_ISHOLDING=1))
HAVING @HAVING_FILTER@  Thanks folks!

Outcomes