Clarity

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@

The VALUE_BEFORE and VALUE_AFTER should contain the translated values.  The RAW_VALUE_BEFORE and RAW_VALUE_AFTER should contain the look-up ids.  You shouldn't have to access the 'look-ups'.

For example, we having auditing on the Project Manager, and I'm seeing the individuals name in the VALUE_BEFORE (eg John Smith), whilst the RAW_VALUE_BEFORE contains their 5,000,000 number.

Additional observation on your SQL, you may want to add in additional where clause AUD.OBJECT_CODE='project', otherwise, you could get incorrect results back if you have auditing turned on other Clarity Objects.

use the view cmn_lookups_v, you can ether join or do a sub select and return name. 

Make sure to pass in lookup_type, language_code and the value.