AnsweredAssumed Answered

2 Dimension query error from audit trail

Question asked by juu on Apr 25, 2014
Latest reply on Apr 28, 2014 by juu
My query has error as below (using oracle database and clarity 13.2)
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: SELECT aud.id aud_id, aud.value_after aud_value_after, aud.object_id object_id, inv.code inv_code, inv.name inv_name, loo2.id loo2_id, loo2.name status_after, aud.last_updated_date updated_date FROM cmn_audits aud left join inv_investments inv on inv.id = aud.object_id left join ODF_AUD_LOOKUP_CAPTIONS loo2 on TO_CHAR(loo2.id) = aud.value_after and loo2.language_code = 'en' where aud.attribute_code ='my_status' AND 1=? and 1=1 group by inv.code, inv.name having 1=? .
 
Is it possible to display inportlet like thia ??? My_status is custom statis lookup.
  My_status
inv_code A1 A2 A3
Urxxxxxxx dd-mm-yyyy dd-mm-yyyy dd-mm-yyyy
Urxxxxxxx dd-mm-yyyy dd-mm-yyyy dd-mm-yyyy
 
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:AUDIT:aud.id:aud_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.value_after:aud_value_after@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.object_id:object_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:inv.code:inv_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:inv.name:inv_name@,
 
@SELECT:DIM:USER_DEF:IMPLIED:STATUS:loo2.id:loo2_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:STATUS:loo2.name:status_after@,
 
@SELECT:METRIC:USER_DEF:IMPLIED:aud.last_updated_date:updated_date@
 
 
FROM
cmn_audits aud
left join inv_investments inv on  inv.id = aud.object_id  
left join ODF_AUD_LOOKUP_CAPTIONS loo2 on TO_CHAR(loo2.id) = aud.value_after and loo2.language_code = 'en'
 
where aud.attribute_code ='my_status' 
AND @FILTER@
 
group by inv.code
having @having_filter@
 

Outcomes