Clarity

  • 1.  First Hierarchical Grid Portlet

    Posted Mar 03, 2014 03:35 PM

     

    I just built my first hierarchical grid portlet.  We have between 4 to 8 levels of hierarchy in our project structure.  Just in case someone else is looking to do this with multiple hierarchy here is the simplified NSQL.

    CHILD_INVESTMENTS – for some reason Clarity leaves null records in the INV_HIERARCHIES table so this provides a list of all investments which are a child.

    INVEST_TREE – is a recursive query with the seed being all investment which do not have a parent and the recursive part walking down each node of the seed query.

    INVEST_DISTINCT – portlets like unique records so this gives the portlet what it likes.

    INVEST_NSQL – provides us the inner joins to other tables for additional fields i.e. Projects

    So on the first call to the NSQL hg_row_id is null so we get all records with a DEPTH = 0.  All expands as are requested we get the records that have the PARENT_ID of the selected record.

    SELECT @SELECT:DIM:USER_DEF:IMPLIED:X:X.UNIQUEID:UNIQUEID@
    , @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.HG_HAS_CHILDREN:HG_HAS_CHILDREN@
    , @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PARENT_ID:PARENT_ID@
    , @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRNAME:PRNAME@
    , @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRFINISH:PRFINISH@
    , @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRSTART:PRSTART@
    , @SELECT:DIM_PROP:USER_DEF:BOOLEAN:X:X.ACTIVE:ACTIVE@
    FROM (
    WITH
    CHILD_INVESTMENTS AS (
      SELECT DISTINCT inv.ID, inh.PARENT_ID, inh.CHILD_ID, inp.PARENT_ID AS HG_HAS_CHILDREN, inv.NAME
      FROM INV_INVESTMENTS inv
      LEFT OUTER JOIN INV_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL
      LEFT OUTER JOIN INV_HIERARCHIES inp on inv.ID = inp.PARENT_ID
    ),
     INVEST_TREE (ID, NAME, PARENT_ID, DEPTH, ROOT_ID, HG_HAS_CHILDREN) as (
      SELECT ID, NAME, PARENT_ID, 0 AS DEPTH, ID AS ROOT_ID, HG_HAS_CHILDREN
      FROM CHILD_INVESTMENTS
      WHERE PARENT_ID IS NULL
      UNION ALL
      SELECT ci.ID, ci.NAME, ci.PARENT_ID, DEPTH + 1, ROOT_ID, ci.HG_HAS_CHILDREN
      FROM CHILD_INVESTMENTS ci
      INNER JOIN INVEST_TREE iv ON ci.PARENT_ID = iv.ID
      ),
      INVEST_DISTINCT AS (
          SELECT DISTINCT ID, NAME, PARENT_ID, DEPTH, ROOT_ID, HG_HAS_CHILDREN FROM INVEST_TREE
      ),
      INVEST_NSQL AS (
      SELECT inv.ID UNIQUEID
        , iv.depth
        , iv.HG_HAS_CHILDREN
        , iv.PARENT_ID
        , inv.NAME PRNAME
        , inv.SCHEDULE_FINISH PRFINISH
        , inv.SCHEDULE_START PRSTART
        , inv.IS_ACTIVE ACTIVE
      FROM INVEST_DISTINCT iv
        INNER JOIN inv_investments inv ON iv.id = inv.id
        INNER JOIN ODF_CA_PROJECT odfp ON iv.id = odfp.id  
      )
      SELECT  UNIQUEID
        , HG_HAS_CHILDREN
        , PARENT_ID
        , PRNAME
        , PRFINISH
        , PRSTART
        , ACTIVE
      FROM INVEST_NSQL
      WHERE Depth = NVL(@where:param:user_def:string:hg_row_id@, 0)
      UNION
      SELECT  UNIQUEID
      , HG_HAS_CHILDREN
      , PARENT_ID
        , PRNAME
        , PRFINISH
        , PRSTART
        , ACTIVE
      FROM INVEST_NSQL
      WHERE PARENT_ID = @where:param:user_def:string:hg_row_id@
      ORDER BY PRNAME
    ) X WHERE @FILTER@

     

    Giving Back, (I have learned a lot on these boards)
    Gene



  • 2.  RE: First Hierarchical Grid Portlet

    Posted Mar 04, 2014 02:50 PM

    Thanks for sharing.

     

    Martti k.

     



  • 3.  RE: First Hierarchical Grid Portlet

    Posted Mar 04, 2014 02:53 PM

    Thank you for sharing. I can't wait to try it out. : )



  • 4.  RE: First Hierarchical Grid Portlet

    Posted Mar 04, 2014 05:01 PM

     

    This was written for an Oracle backend if you are using SQL change:

    WHERE Depth = NVL(@where:param:user_def:string:hg_row_id@, 0)
    

     

    to:

    WHERE Depth = ISNULL(@where:param:user_def:string:hg_row_id@, 0)
    

     

    V/r,

    Gene



  • 5.  Re: First Hierarchical Grid Portlet

    Posted Feb 05, 2015 02:36 PM

    Gene, Thanks for sharing this query.

     

    I tried this on a SQL environment with the ISNULL modification but I'm getting this from Clarity 14.1:

     

    [CA Clarity][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'WITH'

     

    Any idea why?

     

    Thanks again for sharing.

     

    Juan



  • 6.  Re: First Hierarchical Grid Portlet

    Posted Feb 05, 2015 04:05 PM

    This was an Oracle query.  MSQL server has issues with where the WITH is.

     

    See:

    Recursive Common Table Expressions on Clarity MS SQL 2005

     

    V/r,

    Gene



  • 7.  Re: First Hierarchical Grid Portlet

    Posted Feb 05, 2015 04:21 PM

    Yeah we have to re-code all that WITH business to get it to work in SQLServer - its fairly easy to recode but makes the NSQL even harder to read in my opinion.

     

    Wherever a "WITH" table has been used, you just copy the text of the "WITH" definition in place of the table alias in the select - you have to do this all through the query (hence why it gets messy).

     

    eg instead of;

     

    SELECT @...@

    FROM

    (

    with t_1 as ( ****************** )

    , t_2 as ( yyyyyyyyyyyyyyyyyyy)

    select

    ????

    from

    t_1 my_t1 , t_2 my_t2

    WHERE

    .....

    )

     

    you can code it like;

     

    SELECT @...@

    FROM

    (

    select

    ????

    from

    ( ****************** ) my_t1 , ( yyyyyyyyyyyyyyyyyyy) my_t2

    WHERE

    .....

    )

     

    etc



  • 8.  Re: First Hierarchical Grid Portlet

    Posted Feb 12, 2016 01:31 PM

    I did some converting, and was mostly able to move all of the tables defined using "WITH" into sub tables, it's much bigger and messier since each time a table is referenced I have to put the whole virtual table in but it runs.  I just wasn't able to get

    Select * from (with tablename as(select query) select columns from tablename;) to work.  I'm not sure what I missed.  But here's what I came up with. The one remaining question is, did I properly handle the invest_tree table conversation.  I ask because the original code references the table IN the table defenition.

    INVEST_TREE (ID, NAME, PARENT_ID, DEPTH, ROOT_ID, HG_HAS_CHILDREN) as

    (

    SELECT ID, NAME, PARENT_ID, 0 AS DEPTH, ID AS ROOT_ID, HG_HAS_CHILDREN

    FROM CHILD_INVESTMENTS

    WHERE PARENT_ID IS NULL

    UNION ALL

    SELECT ci.ID, ci.NAME, ci.PARENT_ID, DEPTH + 1, ROOT_ID, ci.HG_HAS_CHILDREN

    FROM CHILD_INVESTMENTS ci

    INNER JOIN INVEST_TREE iv ON ci.PARENT_ID = iv.ID

    )

    You see on the final line it links Invest_tree to CI, but this is done inside the defenition of invest_tree which means it refers to itself?

    Anyway here's what I did in case anyone is interested in the MSSQL version of the NSQL.

    SELECT

    @SELECT:DIM:USER_DEF:IMPLIED:X:X.UNIQUEID:UNIQUEID@

    , @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.HG_HAS_CHILDREN:HG_HAS_CHILDREN@

    , @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PARENT_ID:PARENT_ID@

    , @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRNAME:PRNAME@

    , @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRFINISH:PRFINISH@

    , @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRSTART:PRSTART@

    , @SELECT:DIM_PROP:USER_DEF:BOOLEAN:X:X.ACTIVE:ACTIVE@

    FROM

    (

    SELECT

    UNIQUEID

    , HG_HAS_CHILDREN

    , PARENT_ID

    , PRNAME

    , PRFINISH

    , PRSTART

    , ACTIVE

    FROM

    (

    SELECT inv.ID UNIQUEID

    , iv.depth

    , iv.HG_HAS_CHILDREN

    , iv.PARENT_ID

    , inv.NAME PRNAME

    , inv.SCHEDULE_FINISH PRFINISH

    , inv.SCHEDULE_START PRSTART

    , inv.IS_ACTIVE ACTIVE

    FROM

      (

      SELECT

       DISTINCT

       ID

      , NAME

      , PARENT_ID

      , DEPTH

      , ROOT_ID

      , HG_HAS_CHILDREN

      FROM

       (

       SELECT ID, NAME, PARENT_ID, 0 AS DEPTH, ID AS ROOT_ID, HG_HAS_CHILDREN

       FROM

        (

        SELECT DISTINCT inv.ID, inh.PARENT_ID, inh.CHILD_ID, inp.PARENT_ID AS HG_HAS_CHILDREN, inv.NAME

        FROM INV_INVESTMENTS inv

        LEFT OUTER JOIN INV_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL

        LEFT OUTER JOIN INV_HIERARCHIES inp on inv.ID = inp.PARENT_ID

        ) as CHILD_INVESTMENTS

       WHERE PARENT_ID IS NULL

       UNION ALL

       SELECT ci.ID, ci.NAME, ci.PARENT_ID, DEPTH + 1, ROOT_ID, ci.HG_HAS_CHILDREN

       FROM

       (

        SELECT DISTINCT inv.ID, inh.PARENT_ID, inh.CHILD_ID, inp.PARENT_ID AS HG_HAS_CHILDREN, inv.NAME

        FROM INV_INVESTMENTS inv

        LEFT OUTER JOIN INV_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL

        LEFT OUTER JOIN INV_HIERARCHIES inp on inv.ID = inp.PARENT_ID

        ) as ci

       INNER JOIN

        (

        SELECT

         ID

        , NAME

        , PARENT_ID

        , 0 AS DEPTH

        , ID AS ROOT_ID

        , HG_HAS_CHILDREN

        FROM

         (

         SELECT DISTINCT

          inv.ID

         , inh.PARENT_ID

         , inh.CHILD_ID

         , inp.PARENT_ID AS HG_HAS_CHILDREN

         , inv.NAME

         FROM INV_INVESTMENTS inv

         LEFT OUTER JOIN INV_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL

         LEFT OUTER JOIN INV_HIERARCHIES inp on inv.ID = inp.PARENT_ID

         ) as CHILD_INVESTMENTS

        WHERE PARENT_ID IS NULL

        )iv ON ci.PARENT_ID = iv.ID

       ) as INVEST_TREE

      ) iv

    INNER JOIN inv_investments inv ON iv.id = inv.id

    INNER JOIN ODF_CA_PROJECT odfp ON iv.id = odfp.id

    ) AS Invest_NSQL

    WHERE Depth = isnull(@where:param:user_def:string:hg_row_id@, 0)

    UNION

    SELECT

    UNIQUEID

    , HG_HAS_CHILDREN

    , PARENT_ID

    , PRNAME

    , PRFINISH

    , PRSTART

    , ACTIVE

    FROM

    (

    SELECT inv.ID UNIQUEID

    , iv.depth

    , iv.HG_HAS_CHILDREN

    , iv.PARENT_ID

    , inv.NAME PRNAME

    , inv.SCHEDULE_FINISH PRFINISH

    , inv.SCHEDULE_START PRSTART

    , inv.IS_ACTIVE ACTIVE

    FROM

      (

      SELECT

       DISTINCT

       ID

      , NAME

      , PARENT_ID

      , DEPTH

      , ROOT_ID

      , HG_HAS_CHILDREN

      FROM

       (

       SELECT ID, NAME, PARENT_ID, 0 AS DEPTH, ID AS ROOT_ID, HG_HAS_CHILDREN

       FROM

        (

        SELECT DISTINCT inv.ID, inh.PARENT_ID, inh.CHILD_ID, inp.PARENT_ID AS HG_HAS_CHILDREN, inv.NAME

        FROM INV_INVESTMENTS inv

        LEFT OUTER JOIN INV_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL

        LEFT OUTER JOIN INV_HIERARCHIES inp on inv.ID = inp.PARENT_ID

        ) as CHILD_INVESTMENTS

       WHERE PARENT_ID IS NULL

       UNION ALL

       SELECT ci.ID, ci.NAME, ci.PARENT_ID, DEPTH + 1, ROOT_ID, ci.HG_HAS_CHILDREN

       FROM

        (

        SELECT DISTINCT inv.ID, inh.PARENT_ID, inh.CHILD_ID, inp.PARENT_ID AS HG_HAS_CHILDREN, inv.NAME

        FROM INV_INVESTMENTS inv

        LEFT OUTER JOIN INV_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL

        LEFT OUTER JOIN INV_HIERARCHIES inp on inv.ID = inp.PARENT_ID

        ) AS ci

       INNER JOIN

        (

        SELECT

         ID

        , NAME

        , PARENT_ID

        , 0 AS DEPTH

        , ID AS ROOT_ID

        , HG_HAS_CHILDREN

        FROM

         (

         SELECT DISTINCT

          inv.ID

         , inh.PARENT_ID

         , inh.CHILD_ID

         , inp.PARENT_ID AS HG_HAS_CHILDREN

         , inv.NAME

         FROM INV_INVESTMENTS inv

         LEFT OUTER JOIN INV_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL

         LEFT OUTER JOIN INV_HIERARCHIES inp on inv.ID = inp.PARENT_ID

         ) as CHILD_INVESTMENTS

        WHERE PARENT_ID IS NULL

        )iv ON ci.PARENT_ID = iv.ID

       ) as INVEST_TREE

      ) iv

    INNER JOIN inv_investments inv ON iv.id = inv.id

    INNER JOIN ODF_CA_PROJECT odfp ON iv.id = odfp.id

    ) AS Invest_NSQL

    WHERE PARENT_ID = @where:param:user_def:string:hg_row_id@

    ) X

    WHERE @FILTER@



  • 9.  Re: First Hierarchical Grid Portlet

    Posted Feb 05, 2015 06:42 AM

    Fantastic Query.

     

    Thanks for sharing .

     

    Regards,

     

    Joni



  • 10.  RE: First Hierarchical Grid Portlet

    Posted May 14, 2020 11:12 AM
    Hi Gene,

    Thanks for sharing this. It works very well.
    If we create filter on this, only the parent nodes are filtered and the child nodes are not retrieved if there is a match.
    Could you please give pointers on how we can build the filter for this hierarchical portlet, if any.
    Thanks in advance,

    Regards,
    Lakshmi Priya ​