AnsweredAssumed Answered

Ver 13: Must declare the table variable "@INVESTMENT_SQL@".

Question asked by Mayank.Joshi on Nov 12, 2013
Latest reply on Nov 26, 2013 by Mayank.Joshi

Hi all,

During an upgrade to ver 13.2. from ver 12.1.3, there were some queries failing, e.g.

Query name=Benefits by Goal : Query ID=Benefits by Goal : Content Source=niku.com : cmn_nsql_queries.id=5004001

Query "Benefits by Goal" uses @INVESTMENT_SQL@.

When we create a Querry in ver 13.2 Clarity admin tool using following NSQL, it throws errors:

=====================================================

select   @SELECT:DIM:USER_DEF:IMPLIED:GOAL:Q.GOAL_CODE_CAPTION:GOAL@,
         @SELECT:METRIC:USER_DEF:IMPLIED:SUM(@NVL@(Q.PLANNED_BEN_TOTAL, 0)):BENEFIT:AGG@
         , @SELECT:DIM_PROP:USER_DEF:IMPLIED:GOAL:PORTFOLIO_ID:PORTFOLIO_ID@
         , @SELECT:DIM_PROP:USER_DEF:IMPLIED:GOAL:SCENARIO_ID:SCENARIO_ID@
         , @SELECT:DIM_PROP:USER_DEF:IMPLIED:GOAL:COMPARE_TO_SCENARIO_ID:COMPARE_TO_SCENARIO_ID@
         , @SELECT:DIM_PROP:USER_DEF:IMPLIED:GOAL:STATUS:STATUS@
         , @SELECT:DIM_PROP:USER_DEF:IMPLIED:GOAL:ROLE_ID:ROLE_ID@
         , @SELECT:DIM_PROP:USER_DEF:IMPLIED:GOAL:BOOKING_STATUS_ID:BOOKING_STATUS_ID@
from     (@INVESTMENT_SQL@) Q
where    @FILTER@
and      Q.GOAL_CODE_CAPTION is not NULL
group by Q.GOAL_CODE_CAPTION, PORTFOLIO_ID, SCENARIO_ID, COMPARE_TO_SCENARIO_ID, STATUS, ROLE_ID, BOOKING_STATUS_ID
having   @HAVING_FILTER@
@column_list(@INVESTMENT_SQL@, GOAL_CODE_CAPTION, PLANNED_BEN_TOTAL, PORTFOLIO_ID, SCENARIO_ID, COMPARE_TO_SCENARIO_ID, STATUS, ROLE_ID, BOOKING_STATUS_ID)@

===================================

Error:NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Must declare the table variable "@INVESTMENT_SQL@".

===================================

Any suggestion please?

Thank you.

Mayank

 

 

Outcomes