Help with audit log query
Trying to retrieve the test information, status change information, and audit time for a particular status change field, but I keep getting an error on the date range search.
Here's the query:
<font class="small">Code:</font><hr /><pre>
TEST.TS_TEST_ID as Test_ID,
TEST.TS_USER_02 AS Automation_Time,
TEST.TS_USER_03 AS Automation_Complexity,
FROM TEST, AUDIT_LOG, AUDIT_PROPERTIES
WHERE AUDIT_LOG.AU_ENTITY_ID = TEST.TS_TEST_ID
AND AUDIT_PROPERTIES.AP_ACTION_ID = AUDIT_LOG.AU_ACTION_ID
AND AUDIT_LOG.AU_ENTITY_TYPE = 'TEST'
AND AUDIT_PROPERTIES.AP_FIELD_NAME = 'TS_STATUS'
AND AUDIT_PROPERTIES.AP_NEW_VALUE = 'Automation Complete'
AND AUDIT_LOG.AU_TIME BETWEEN '2011-01-01' AND '2011-03-31'</pre><hr />
and the error I get when I run states "The multi-part identifier AUDIT_PROPERTIES.AP_FIELD_NAME cannot be bound".
What I can't figure out is that this only appears when the date range condition is added, and the message appears to have nothing to do with the date range field AU_TIME).
Any ideas why this is going awry?
Re: Help with audit log query
You need joins to bind the tables. for example to get all au_entity_id's for ap_fieldname = TS_STATUS you would write:
SELECT AUDIT_LOG.AU_ENTITY_ID /*Audit Log.Entity ID*/
left outer join AUDIT_PROPERTIES on AUDIT_LOG.AU_ACTION_ID = AUDIT_PROPERTIES.AP_ACTION_ID where audit_properties.ap_field_name = 'TS_STATUS'
order by audit_log.au_entity_id
You'll need update the sample sql to include code for the joining to the test table.