Clarity

Expand all | Collapse all

Hierarchical portlet problem

  • 1.  Hierarchical portlet problem

    Posted 06-06-2017 10:23 AM
      |   view attached

    Hi guys, i tried to make a portlet, simply to project dashboard, with one more level - "audit" trail for any task ( i want to show type of change, old value, new value). Everything is clear, portlet work, everything is right. Problem happens when i want to filter data by date of "create" of change (created_date from cmn_audits). When i filter, i see some for ex. projects/tasks which i can expand, but they dont have any value (which is good, because where clause work). How can i build my sql clause to delete that problem ? 

    Perhaps it is gently twisted, but when you look at ma sql, I hope you understand what I mean
    Perhaps it is gently twisted, but when you start, I hope you understand what I mean

    Attachment(s)

    zip
    SQL06.06.txt.zip   1 KB 1 version


  • 2.  Re: Hierarchical portlet problem

    Broadcom Employee
    Posted 06-13-2017 04:45 AM

    Hi,

     

      I think your issue is from the following line :

     

    -- Project Level
    SELECT DISTINCT
           'L1-' @+@ CMN_TO_CHAR_FCT(i.id) dim_id,  
           i.name level_name,
           i.code level_id,
           ' ' column_name,
           ' ' before,
           ' ' after,
           null created_date,
           CASE WHEN (

                   SELECT COUNT(*)
                   FROM   prtask ct
                   WHERE  ct.prprojectid = i.id

                  ) > 0
                   THEN  'L1-' @+@ CMN_TO_CHAR_FCT(i.id) @+@ CMN_TO_CHAR_FCT(i.id) @+@ CMN_TO_CHAR_FCT(i.id)
                WHEN (SELECT COUNT(*)
                      FROM   prsubproject sp
                      INNER  JOIN prtask sptasks ON sptasks.prid = sp.prtaskid
                      WHERE  sptasks.prprojectid = i.id

                   ) > 0
                   THEN  'L1-' @+@ CMN_TO_CHAR_FCT(i.id) @+@ CMN_TO_CHAR_FCT(i.id) @+@ CMN_TO_CHAR_FCT(i.id)
                ELSE  NULL  END hg_has_children
           FROM  inv_investments i

     

    In these line you calculate the presence of sub object, to indicate if and expand sign is displayed in the hierarchical portlet. But in the sub query that will run when you click on has a more restricted data set (due to the filter of date) :

    (Example only for the second level, and for task of project)

    SELECT DISTINCT
        'L2-' @+@ CMN_TO_CHAR_FCT(tasks.prprojectid) @+@ @NVL@(@SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,11,7),'0000000') @+@ @DBUSER@.CMN_TO_CHAR_FCT(tasks.prid) dim_id,
        tasks.prname level_name,
        tasks.prexternalid level_id,
        ' ' column_name,
        ' ' before,
        ' ' after,
        null created_date,
        CASE
        
        WHEN
            (SELECT COUNT(*)
             FROM   prtask ct
             WHERE
                 ct.prprojectid = tasks.prprojectid
             AND ct.wbs_parseq = tasks.prwbssequence
             AND (CMN.COLUMN_NAME in ('prStart','prFinish','prName') or (cmn.column_name='prStatus' and raw_value_after = 2))
             and ( (trunc(cmn.created_date,'DDD') >= @where:param:user_def:date:start_date@ or @where:param:user_def:date:start_date@ is null)
                   and (trunc(cmn.created_date,'DDD')<= @where:param:user_def:date:finish_date@ or @where:param:user_def:date:finish_date@ is null))
             and cmn.object_code='task') > 0
            
        THEN  'L2-' @+@ CMN_TO_CHAR_FCT(tasks.prprojectid)
              @+@ @NVL@(@SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,11,7),'0000000')
              @+@ @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,18,7) @+@ @DBUSER@.CMN_TO_CHAR_FCT(tasks.prwbssequence)
                
        WHEN (SELECT COUNT(*)
              FROM   prtask tasks
              WHERE  cmn.object_id= tasks.prid
              AND(CMN.COLUMN_NAME in ('prStart','prFinish','prName') or (cmn.column_name='prStatus' and raw_value_after = 2))
              and ((trunc(cmn.created_date,'DDD') >= @where:param:user_def:date:start_date@ or @where:param:user_def:date:start_date@ is null)
                    and (trunc(cmn.created_date,'DDD')<= @where:param:user_def:date:finish_date@ or @where:param:user_def:date:finish_date@ is null))
              and cmn.object_code='task') > 0
                
        THEN  'L2-' @+@ CMN_TO_CHAR_FCT(tasks.prprojectid) @+@ @NVL@(@SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,11,7),'0000000')  
              @+@ @DBUSER@.CMN_TO_CHAR_FCT(tasks.prid)       
              
        ELSE  NULL  
        
        END hg_has_children
        
    FROM  prtask tasks
           INNER JOIN inv_investments i ON i.id = tasks.prprojectid
           LEFT  OUTER JOIN cmn_lookups_v ts ON  ts.lookup_type = 'prTaskStatus'
                                             AND ts.lookup_enum = tasks.prstatus
                                             AND ts.language_code = @WHERE:PARAM:LANGUAGE@
           LEFT  OUTER JOIN prsubproject sp ON  sp.prtaskid = tasks.prid
           LEFT  OUTER JOIN CMN_AUDITS CMN ON CMN.OBJECT_ID=tasks.PRID
    WHERE
          @NVL@(sp.prtaskid,0) = 0
     AND  ( ('L1-' @+@ CMN_TO_CHAR_FCT(tasks.prprojectid) = @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,1,10) AND tasks.wbs_parseq = -1)
         OR ('L2-' @+@ CMN_TO_CHAR_FCT(tasks.prprojectid) = @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,1,10) AND @DBUSER@.CMN_TO_CHAR_FCT(tasks.wbs_parseq) = @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,25,7)) )
        AND (CMN.COLUMN_NAME in ('prStart','prFinish','prName') or (cmn.column_name='prStatus' and raw_value_after = 2))
        and ((trunc(cmn.created_date,'DDD') >= @where:param:user_def:date:start_date@ or @where:param:user_def:date:start_date@ is null)
        and (trunc(cmn.created_date,'DDD')<= @where:param:user_def:date:finish_date@ or @where:param:user_def:date:finish_date@ is null))
        and cmn.object_code='task' AND CMN.OPERATION_CODE='U'  
     AND   @WHERE:SECURITY:PROJECT:i.id@  

     

    So in some case you will have several returns when you select all task associated to a project (without any filter regarding security @WHERE:SECURITY:PROJECT:...@, and this selected task will not have any association with audit entries, or will not be selected in the second part of the query due to the security or date constraint.

     

    You need to align your filter query from the sub query with the query used to test if there existing task (1st part)

     

    Hopes that will help you :-)