Clarity

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

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

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