Clarity PPM

Expand all | Collapse all

Dynamic lookup field with Connect By Level

Jump to Best Answer
  • 1.  Dynamic lookup field with Connect By Level

    Posted 02-04-2018 11:02 PM

    Hi,

    I'm trying to create a dynamic lookup which can be used in an object attribute. The lookup query uses the connect by level syntax to display 1  to 100 months. The lookup generates fine, but when the lookup is used in an object attribute, I get an error on screen saying 'ErrorERRORSorting by the column specified is not allowed'.

     

    CA PPM Version 15.3

     

    Dynamic Lookup Query:

    select lvl months,
    period
    from
    (
    select level lvl, trunc(level/12) yrs,
    mod(trunc(level), 12) mnths,
    trunc(level/12) || ' - ' || mod(trunc(level), 12) period
    FROM dual
    CONNECT BY LEVEL <= 100)
    where @FILTER@

     

     

    The app-logs have the below errors:

    ERROR 2018-02-05 14:54:58,134 [http-nio-14001-exec-17] union.persistence (clarity:harrish:9403051__0830CECC:odf.z_master_by_hpProperties)  java.sql.SQLSyntaxErrorException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "MONTHS": invalid identifier
    ERROR 2018-02-05 14:54:58,135 [http-nio-14001-exec-17] niku.xql2 (clarity:harrish:9403051__0830CECC:odf.z_master_by_hpProperties) ServiceHandler.postProcess failed. com.niku.union.odf.lookup.LookupException: com.niku.union.persistence.nsql.NSQLException: com.niku.union.persistence.PersistenceApplicationException
    Caused by: com.niku.union.persistence.nsql.NSQLException: com.niku.union.persistence.PersistenceApplicationException      at com.niku.union.persistence.nsql.NSQLQuery._execute(NSQLQuery.java:705)      at com.niku.union.persistence.nsql.NSQLQuery.execute(NSQLQuery.java:540)      at com.niku.odf.lookup.LookupControllerImpl.getLookupValue(LookupControllerImpl.java:546)      ... 71 more Caused by: com.niku.union.persistence.PersistenceApplicationException      at com.niku.union.persistence.PersistenceController.handleSQLException(PersistenceController.java:2149)      at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2847)      at com.niku.union.persistence.PersistenceController.processStatement(PersistenceController.java:868)      at com.niku.union.persistence.PersistenceController.processStatements(PersistenceController.java:768)      at com.niku.union.persistence.PersistenceController.doProcessRequest(PersistenceController.java:576)      at com.niku.union.persistence.PersistenceController.processRequest(PersistenceController.java:306)      at com.niku.union.persistence.nsql.NSQLQuery._execute(NSQLQuery.java:686)


    Any pointers on how I can get this working or an alternate approach to achieve the end result would be great.

    -Harrish


  • 2.  Re: Dynamic lookup field with Connect By Level

    Posted 02-04-2018 11:48 PM

    Hi,

     

    I run your query by using sql*plus. It returned unexpected value for MONTHS.

    MONTHS maybe reserved word or it maybe cause any conflict.

     

    COL months FORMAT A10

    select lvl months, period 

    from
    (
    select level lvl, trunc(level/12) yrs,
    mod(trunc(level), 12) mnths,
    trunc(level/12) || ' - ' || mod(trunc(level), 12) period
    FROM dual
    CONNECT BY LEVEL <= 100);

     

    MONTHS PERIOD
    ---------- ---------------
    ########## 0 - 1
    ########## 0 - 2
    ########## 0 - 3
    ########## 0 - 4
    ########## 0 - 5
    ########## 0 - 6
    ########## 0 - 7
    ########## 0 - 8
    ########## 0 - 9

     

    I changed MONTHS to MONTHS1,  it returned the following expected data.

     

    MONTHS1 PERIOD
    ---------- ---------------
    1 0 - 1
    2 0 - 2
    3 0 - 3
    4 0 - 4
    5 0 - 5
    6 0 - 6
    7 0 - 7
    8 0 - 8
    9 0 - 9
    10 0 - 10
    11 0 - 11

    ...

     

    Please try to change MONTHS to MONTHS1. It may resolve your problem.

     

    Regards,

    Shoichi



  • 3.  Re: Dynamic lookup field with Connect By Level
    Best Answer

    Posted 02-05-2018 01:25 AM

    Hi,

     

    I investigated this question and I found that your query is Hierarchical Queries, and level is Pseudo column.

    It may have some limitation.

     

    I created lookup with below query and it worked with custom object attribute.

     

    -----

    select lvl months1, period 

    from
    (
    select level lvl, trunc(level/12) yrs,
    mod(trunc(level), 12) mnths,
    trunc(level/12) || ' - ' || mod(trunc(level), 12) period
    FROM dual
    CONNECT BY LEVEL <= 100)

    where @FILTER@

    ----

     

     

    I hope it is helpful for your investigation.

     

    Regards,

    Shoichi