AnsweredAssumed Answered

Failed to adding custom attributes to Transaction Review query

Question asked by JoostBreggeman76041091 on Oct 9, 2015
Latest reply on Oct 9, 2015 by Andrew_Lerner

Hi, I am fairly new to Clarity and I am trying to add a custom attribute from the Task object to the Transaction Review query. However, when running the NSQL i get the following error: "NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00979: not a GROUP BY expression SQL Text: "

 

I am using the following query in which I added the following rows (highlighted in RED). Any idea how to fix this?

 

SELECT@select:dim:user_def:implied:WIP:wip.transno:wip_transno@,
@select:dim_prop:user_def:implied:WIP:wip.transdate:wip_transdate@,
@select:dim_prop:user_def:implied:WIP:inv.id:investment_int_id@,
@select:dim_prop:user_def:implied:WIP:inv.code:investment_id@,
@select:dim_prop:user_def:implied:WIP:inv.odf_object_code:inv_object_code@,
@select:dim_prop:user_def:implied:WIP:UPPER(inv.odf_object_code):inv_object_code_upper@,
@select:dim_prop:user_def:implied:WIP:inv.name:investment_name@,
@select:dim_prop:user_def:implied:WIP:t.prid:task_int_id@,
@select:dim_prop:user_def:implied:WIP:t.prexternalid:task_id@,
@select:dim_prop:user_def:implied:WIP:CASE WHEN t.prexternalid = '~rmw' THEN tname.name ELSE t.prname END:task_name@,
@select:dim_prop:user_def:implied:WIP:CASE WHEN t.prexternalid = '~rmw' THEN 0 ELSE 1 END:task_disable@,
@select:dim_prop:user_def:implied:WIP:t.prwbssequence:task_wbs_sequence@,
@select:dim_prop:user_def:implied:WIP:cc.prid:chargecode_int_id@,
@select:dim_prop:user_def:implied:WIP:cc.prname:chargecode_name@,
@select:dim_prop:user_def:implied:WIP:r.id:resource_int_id@,
@select:dim_prop:user_def:implied:WIP:r.unique_name:resource_id@,
@select:dim_prop:user_def:implied:WIP:r.full_name:resource_name@,
@select:dim_prop:user_def:implied:WIP:tc.id:transclass_int_id@,
@select:dim_prop:user_def:implied:WIP:tc.description:transclass_name@,
@select:dim_prop:user_def:implied:WIP:tyc.prid:inputtype_int_id@,
@select:dim_prop:user_def:implied:WIP:wip.input_type:inputtype_name@,
@select:dim_prop:user_def:implied:WIP:wip.user_lov1:user_value1_id@,
@select:dim_prop:user_def:implied:WIP:userlov1.name:user_value1_name@,
@select:dim_prop:user_def:implied:WIP:wip.user_lov2:user_value2_id@,
@select:dim_prop:user_def:implied:WIP:userlov2.name:user_value2_name@,
@select:dim_prop:user_def:implied:WIP:wip.notes:notes@,
@select:dim_prop:user_def:implied:WIP:wip.quantity:quantity@,                        
@select:dim_prop:user_def:implied:WIP:prj_ent.ENTITY:prj_entity_id@,
@select:dim_prop:user_def:implied:WIP:prj_ent.shortdesc:prj_entity_name@,
@select:dim_prop:user_def:implied:WIP:res_loc.locationid:res_location_id@,
@select:dim_prop:user_def:implied:WIP:res_loc.shortdesc:res_location_name@,
@select:dim_prop:user_def:implied:WIP:res_dept.departcode:res_dept_id@,
@select:dim_prop:user_def:implied:WIP:res_dept.shortdesc:res_dept_name@,
@select:dim_prop:user_def:implied:WIP:prj_loc.locationid:prj_location_id@,
@select:dim_prop:user_def:implied:WIP:prj_loc.shortdesc:prj_location_name@,
@select:dim_prop:user_def:implied:WIP:prj_dept.departcode:prj_dept_id@,
@select:dim_prop:user_def:implied:WIP:prj_dept.shortdesc:prj_dept_name@,
@select:dim_prop:user_def:implied:WIP:wipclass.wipclass:wipclass_id@,
@select:dim_prop:user_def:implied:WIP:wipclass.shortdesc:wipclass_name@,
@select:dim_prop:user_def:implied:WIP:resclass.resource_class:resource_class_id@,
@select:dim_prop:user_def:implied:WIP:resclass.description:resource_class_name@,
@select:dim_prop:user_def:implied:WIP:prjclass.PROJCLASS:prjclass_id@,
@select:dim_prop:user_def:implied:WIP:prjclass.shortdesc:prjclass_name@,
@select:dim_prop:user_def:implied:WIP:wip.transtype:transtype_id@,
@select:dim_prop:user_def:implied:WIP:transtype.NAME:transtype_name@,
@select:dim_prop:user_def:implied:WIP:wip.cost_type:costtype_id@,
@select:dim_prop:user_def:implied:WIP:costtype.name:costtype_name@,
@select:dim_prop:user_def:implied:WIP:wip.chargeable:chargeable@,
@select:dim_prop:user_def:implied:WIP:tct.z_taskcategory:task_category@,
@select:metric:user_def:implied:SUM(CASE WHEN wv.currency_type = 'HOME' THEN wv.totalcost END):home_totalcost:AGG@,
@select:metric:user_def:implied:SUM(CASE WHEN wv.currency_type = 'HOME' THEN wv.totalamount END):home_amt:AGG@,
@select:metric:user_def:implied:MAX(CASE WHEN wv.currency_type = 'HOME'THEN wv.currency_code END):home_curr:AGG@,
@select:metric:user_def:implied:SUM(CASE WHEN wv.currency_type = 'HOME' THEN wv.rate_exchange_rate END):home_rate:AGG@,
@select:metric:user_def:implied:SUM(CASE WHEN wv.currency_type = 'NATURAL' THEN wv.totalcost END):natural_totalcost:AGG@,
@select:metric:user_def:implied:SUM(CASE WHEN wv.currency_type = 'NATURAL' THEN wv.totalamount END):natural_amt:AGG@,
@select:metric:user_def:implied:MAX(CASE WHEN wv.currency_type = 'NATURAL'THEN wv.currency_code END):natural_curr:AGG@,
@select:metric:user_def:implied:SUM(CASE WHEN wv.currency_type = 'NATURAL' THEN wv.rate_exchange_rate END):natural_rate:AGG@,
@select:metric:user_def:implied:SUM(CASE WHEN wv.currency_type = 'REPORTING' THEN wv.totalcost END):rpt_totalcost:AGG@,
@select:metric:user_def:implied:SUM(CASE WHEN wv.currency_type = 'REPORTING' THEN wv.totalamount END):rpt_amt:AGG@,
@select:metric:user_def:implied:MAX(CASE WHEN wv.currency_type = 'REPORTING'THEN wv.currency_code END):rpt_curr:AGG@,
@select:metric:user_def:implied:SUM(CASE WHEN wv.currency_type = 'REPORTING' THEN wv.rate_exchange_rate END):rpt_rate:AGG@,
@select:metric:user_def:implied:SUM(CASE WHEN wv.currency_type = 'BILLING' THEN wv.totalcost END):bill_totalcost:AGG@,
@select:metric:user_def:implied:SUM(CASE WHEN wv.currency_type = 'BILLING' THEN wv.totalamount END):bill_amt:AGG@,
@select:metric:user_def:implied:MAX(CASE WHEN wv.currency_type = 'BILLING'THEN wv.currency_code END):bill_curr:AGG@,
@select:metric:user_def:implied:SUM(CASE WHEN wv.currency_type = 'BILLING' THEN wv.rate_exchange_rate END):bill_rate:AGG@

FROM    PPA_WIP wip

LEFT    OUTER JOIN inv_investments inv ON inv.id = wip.investment_id

LEFT    OUTER JOIN prtask t ON wip.task_id = t.prid

LEFT    OUTER JOIN srm_resources r ON wip.resource_code = r.unique_name

LEFT    OUTER JOIN ppa_wip_values wv ON wip.transno = wv.transno and wv.currency_type <> 'EURO'

LEFT    OUTER JOIN prchargecode cc ON wip.charge_code = cc.prexternalid

LEFT    OUTER JOIN transclass tc ON wip.transclass = tc.transclass

LEFT    OUTER JOIN prtypecode tyc ON wip.input_type = tyc.prexternalid

LEFT    OUTER JOIN entity prj_ent ON wip.entity = prj_ent.entity

LEFT    OUTER JOIN locations res_loc ON wip.emplyhomelocation = res_loc.locationid

LEFT    OUTER JOIN departments res_dept ON wip.emplyhomedepart  = res_dept.departcode

LEFT    OUTER JOIN locations prj_loc ON wip.project_location = prj_loc.locationid

LEFT    OUTER JOIN departments prj_dept ON wip.project_department = prj_dept.departcode

LEFT    OUTER JOIN wipclass wipclass ON wip.wipclass = wipclass.wipclass

LEFT    OUTER JOIN pac_fos_resource_class resclass ON WIP.resource_class = resclass.resource_class

LEFT    OUTER JOIN projclass prjclass ON wip.projectclass = prjclass.projclass

JOINodf_ca_task tct ON wip.task_id = tct.id

 

 

LEFT    OUTER JOIN cmn_lookups_v tname ON tname.lookup_type = 'COP_PORTLET_QUERY_TRANSLATIONS' AND tname.language_code = @WHERE:PARAM:LANGUAGE@

                   AND tname.lookup_code = 'Effort'

LEFT    OUTER JOIN cmn_lookups_v userlov1 ON wip.user_lov1 = userlov1.lookup_code AND userlov1.lookup_type = 'PRTIMEENTRY_USER_LOV1' AND userlov1.language_code = @WHERE:PARAM:LANGUAGE@

LEFT    OUTER JOIN cmn_lookups_v userlov2 ON wip.user_lov2 = userlov2.lookup_code AND userlov2.lookup_type = 'PRTIMEENTRY_USER_LOV2' AND userlov2.language_code = @WHERE:PARAM:LANGUAGE@

LEFT    OUTER JOIN cmn_lookups_v transtype ON wip.transtype = transtype.lookup_code AND transtype.lookup_type = 'RPT_TRANSACTION_TYPE' AND transtype.language_code = @WHERE:PARAM:LANGUAGE@

LEFT    OUTER JOIN cmn_lookups_v costtype ON wip.cost_type = costtype.lookup_code AND costtype.lookup_type = 'LOOKUP_FIN_COSTTYPECODE' AND costtype.language_code = @WHERE:PARAM:LANGUAGE@

LEFT    OUTER JOIN ppa_wipapinfo info ON info.id = wip.ppa_wipapinfo_id AND @WHERE:PARAM:USER_DEF:STRING:info.voucherno:entryno_filter@

WHERE   wip.status = 0

AND     inv.id = @NVL@(@WHERE:PARAM:XML:INTEGER:/data/id/@value@,inv.id)

AND     @WHERE:SECURITY:INV:inv.id@

AND     @FILTER@

GROUP   BY  wip.transno, wip.transdate, inv.id,inv.code, inv.name, inv.odf_object_code, t.prid, t.prexternalid, t.prname, t.prwbssequence, cc.prid,cc.prname,r.id,

            r.full_name, r.unique_name, tc.id,tc.description, tyc.prid, wip.input_type, wip.user_lov1, userlov1.name, wip.user_lov2, userlov2.name, wip.notes, wip.quantity, tname.name, prj_ent.ENTITY,

            prj_ent.shortdesc, res_loc.locationid, res_loc.shortdesc, res_dept.shortdesc, res_dept.departcode,

            prj_dept.departcode, prj_dept.shortdesc, prj_loc.locationid, prj_loc.shortdesc,

            wipclass.wipclass, wipclass.shortdesc, resclass.resource_class, resclass.description, prjclass.PROJCLASS,

            prjclass.shortdesc, wip.transtype,transtype.NAME, wip.cost_type, costtype.NAME, wip.chargeable

HAVING  @HAVING_FILTER@

Outcomes