AnsweredAssumed Answered

nSQL for Global Audit

Question asked by ThomasCampbell on Feb 14, 2018
Latest reply on Feb 15, 2018 by am1

I'm looking to query against the Audit...and want to translate value_before and value_after that are coming from a Lookup.  Is there an easy way to do that?

 

I've heard of the CMN_LOOKUPS table, but I'm not sure how to join against it?

 

Current Query:

 

SELECT
@SELECT:DIM:USER_DEF:IMPLIED:AUDIT:d.aud_id:aud_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:d.aud_object_code:aud_object_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:d.aud_column_name:aud_column_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:d.aud_table_name:aud_table_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:d.aud_operation_code:operation_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:d.aud_value_before:aud_value_before@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:d.aud_value_after:aud_value_after@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:d.aud_last_updated_date:aud_last_updated_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:d.aud_last_updated_by:aud_last_updated_by@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:d.res_unique_name:res_unique_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:d.res_full_name:res_full_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:d.inv_code:inv_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:d.inv_name:inv_name@

FROM (
SELECT aud.id aud_id, aud.object_code aud_object_code, aud.column_name aud_column_name,
aud.table_name aud_table_name, aud.operation_code aud_operation_code,
aud.value_before aud_value_before, aud.value_after aud_value_after,
aud.last_updated_date aud_last_updated_date, aud.last_updated_by aud_last_updated_by,
res.unique_name res_unique_name, res.full_name res_full_name,
inv.code inv_code, inv.name inv_name
FROM
cmn_audits aud,
srm_resources res,
inv_investments inv
where aud.last_updated_by = res.user_id
and aud.object_id = inv.id
) d

WHERE @FILTER@

Outcomes