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 'ERROR: Sorting 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