AnsweredAssumed Answered

Dynamic lookup field with Connect By Level

Question asked by info_tel on Feb 5, 2018
Latest reply on Feb 5, 2018 by Shoichi_Sugawara

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

Outcomes