Home » SQL & PL/SQL » SQL & PL/SQL » useful analysis from history tables (oracle 10 g)
useful analysis from history tables [message #446469] Mon, 08 March 2010 13:13 Go to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Hi all-

I have 2 tables that I am going to use. These are AUDIT_LOG, and AUDIT_PROPERTIES. AUDIT_LOG tells me that who made the change and at what time. AUDIT_PROPERTIES basically tells me that what the old value was and what the new value is.

These tables operate at the field level. For example, I have a module called BUG. Now a BUG (as a module entity or record) has several fields. There are around 25 fields. But I am interested in only 3 of these fields- these are BG_STATUS (Status), Priority (BG_PRIORITY) and Severity (BG_SEVERITY). This is my requirement:

Between a given period of time (let us say the month of January), give me the count of bugs that, 1) had an "Open" Status, 2) Were of "Medium" Severity, 3) And had a "Low" priority....each of these 3 conditions must be true simultaneously, means, that to be a "qualified" bug, the bug must be Open, Medium severity and low priority at the same given time within the month.

Below are the query and an image of the data looks like. I would appreciate if you can help me with this query.

EA123
  • Attachment: query.PNG
    (Size: 29.06KB, Downloaded 121 times)
Re: useful analysis from history tables [message #446476 is a reply to message #446469] Mon, 08 March 2010 13:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you post a working Test case: create table and insert statements along with the result you want with these data, we will be able to work with them.

Many of us can't or don't want to download, so put it in the post.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: useful analysis from history tables [message #446512 is a reply to message #446469] Mon, 08 March 2010 23:39 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
WITH tab AS
     (
        SELECT   au_action_id, au_time, au_entity_id, ap_old_value,
                 ap_new_value, ap_field_name, ap_property_name,
                 DECODE (ap_field_name, 'BG_STATUS', ap_old_value, NULL) a,
                 DECODE (ap_field_name, 'BG_PRIORITY', ap_old_value, NULL) b,
                 DECODE (ap_field_name, 'BG_SEVERITY', ap_old_value, NULL) c
            FROM est_q2c_db.audit_log, est_q2c_db.audit_properties
           WHERE au_action_id = ap_action_id
             AND au_entity_type = 'BUG'
             AND ap_field_name IN ('BG_STATUS', 'BG_PRIORITY', 'BG_SEVERITY')
             AND au_time BETWEEN TO_DATE ('01-01-2010', 'DD-MM-YYYY')
                             AND TO_DATE ('31-01-2010', 'DD-MM-YYYY')
        ORDER BY au_entity_id)
SELECT au_entity_id,max(a),max(b),max(c)
  FROM tab
 group by au_entity_id
-- add conditions 


use CODE tags buddy
post test-cases

[Updated on: Mon, 08 March 2010 23:42]

Report message to a moderator

Re: useful analysis from history tables [message #446609 is a reply to message #446512] Tue, 09 March 2010 10:37 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Ayush- thank you this is a very elegant solution. However I face one issue- the max function that you suggested does not pick the entry that is chronologically latest but instead picks it up alphabetically. For example, let us say that there are 2 statuses A and B. Now even though A is the more recent status, it picks B as that is the natural outcome of using the max function here.

Any idea how I can avoid this?

Thank you

EA123



ayush_anand wrote on Mon, 08 March 2010 23:39
WITH tab AS
     (
        SELECT   au_action_id, au_time, au_entity_id, ap_old_value,
                 ap_new_value, ap_field_name, ap_property_name,
                 DECODE (ap_field_name, 'BG_STATUS', ap_old_value, NULL) a,
                 DECODE (ap_field_name, 'BG_PRIORITY', ap_old_value, NULL) b,
                 DECODE (ap_field_name, 'BG_SEVERITY', ap_old_value, NULL) c
            FROM est_q2c_db.audit_log, est_q2c_db.audit_properties
           WHERE au_action_id = ap_action_id
             AND au_entity_type = 'BUG'
             AND ap_field_name IN ('BG_STATUS', 'BG_PRIORITY', 'BG_SEVERITY')
             AND au_time BETWEEN TO_DATE ('01-01-2010', 'DD-MM-YYYY')
                             AND TO_DATE ('31-01-2010', 'DD-MM-YYYY')
        ORDER BY au_entity_id)
SELECT au_entity_id,max(a),max(b),max(c)
  FROM tab
 group by au_entity_id
-- add conditions 


use CODE tags buddy
post test-cases

Re: useful analysis from history tables [message #446612 is a reply to message #446609] Tue, 09 March 2010 10:43 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: useful analysis from history tables [message #446616 is a reply to message #446609] Tue, 09 March 2010 11:03 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
All:

I added a sub-query below to further enhance Ayush's query. Issue is resolved.

Thanks !!


WITH TAB As
     (
SELECT au_action_id, au_time, au_entity_id, ap_old_value,
       ap_new_value, ap_field_name, ap_property_name,
              DECODE (ap_field_name, 'BG_STATUS', AP_NEW_VALUE, NULL) a,
              DECODE (ap_field_name, 'BG_SEVERITY', AP_NEW_VALUE, NULL) b,
              DECODE (ap_field_name, 'BG_PRIORITY', AP_NEW_VALUE, NULL) c
           FROM audit_log, audit_properties
           WHERE au_action_id = ap_action_id
           And AU_ACTION_ID IN (SELECT MAX (AU_ACTION_ID)
                                FROM audit_log, audit_properties
                                WHERE au_action_id = ap_action_id
                                AND AU_ENTITY_TYPE = 'BUG'
                                AND AP_FIELD_NAME IN ('BG_STATUS', 'BG_SEVERITY',
                                 'BG_PRIORITY')
                                 AND au_time <= TO_DATE ('31-01-2010', 'DD-MM-YYYY')
                                Group by AU_ENTITY_ID, AP_FIELD_NAME)
           AND au_entity_type = 'BUG'
           AND ap_field_name IN ('BG_STATUS', 'BG_SEVERITY', 'BG_PRIORITY')
           AND au_time <= TO_DATE ('31-01-2010', 'DD-MM-YYYY')
        ORDER BY au_entity_id)
SELECT au_entity_id,max(a),max(b),max(c)
  FROM tab

Group By au_entity_id
Order By au_entity_id





Re: useful analysis from history tables [message #446630 is a reply to message #446616] Tue, 09 March 2010 13:06 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Hi- this is not resolved yet. My argument was faulty. Working on this.

EA
Re: useful analysis from history tables [message #446634 is a reply to message #446616] Tue, 09 March 2010 13:56 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
This is what I have narrowed it down to...please see the query below...this is the TAB table in the original query by Ayush. If I can have a way to find the maximum value for AU_ACTION_ID grouped by those A, B and C that might solve my issue.

Please take a look and let me know what you think.

EA


SELECT au_entity_id, au_action_id,
              DECODE (ap_field_name, 'BG_STATUS', AP_NEW_VALUE, NULL)  a,
              DECODE (ap_field_name, 'BG_PRIORITY', AP_NEW_VALUE, NULL) b,
              DECODE (ap_field_name, 'BG_SEVERITY', AP_NEW_VALUE, NULL) c
           FROM audit_log, audit_properties
           WHERE au_action_id = ap_action_id
AND au_entity_type = 'BUG'
AND au_time <= TO_DATE ('31-01-2010', 'DD-MM-YYYY')
And AP_FIELD_NAME IN ('BG_STATUS', 'BG_PRIORITY', 'BG_SEVERITY')

ORDER BY AP_FIELD_NAME




Re: useful analysis from history tables [message #446635 is a reply to message #446634] Tue, 09 March 2010 13:59 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
Please realize that we don't have your tables & we don't have your data.
Therefore we can't run, test & improve your posted SQL.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
Re: useful analysis from history tables [message #446671 is a reply to message #446634] Tue, 09 March 2010 22:39 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
/* Formatted on 2010/03/10 10:08 (Formatter Plus v4.8.7) */
WITH tab AS
     (
        SELECT   au_action_id, au_time, au_entity_id, ap_old_value,
                 ap_new_value, ap_field_name, ap_property_name,
                 DECODE (ap_field_name, 'BG_STATUS', ap_old_value, NULL) a,
                 DECODE (ap_field_name, 'BG_PRIORITY', ap_old_value, NULL) b,
                 DECODE (ap_field_name, 'BG_SEVERITY', ap_old_value, NULL) c
            FROM est_q2c_db.audit_log, est_q2c_db.audit_properties
           WHERE au_action_id = ap_action_id
             AND au_entity_type = 'BUG'
             AND ap_field_name IN ('BG_STATUS', 'BG_PRIORITY', 'BG_SEVERITY')
             AND au_time BETWEEN TO_DATE ('01-01-2010', 'DD-MM-YYYY')
                             AND TO_DATE ('31-01-2010', 'DD-MM-YYYY')
        ORDER BY au_entity_id)
SELECT   au_entity_id, MAX (a), MAX (b), MAX (c)
    FROM (SELECT au_action_id, au_time, au_entity_id, ap_old_value,
                 ap_new_value, ap_field_name, ap_property_name,
                 FIRST_VALUE (a) OVER (PARTITION BY au_entity_id ORDER BY au_time DESC)
                                                                            a,
                 FIRST_VALUE (b) OVER (PARTITION BY au_entity_id ORDER BY au_time DESC)
                                                                            b,
                 FIRST_VALUE (c) OVER (PARTITION BY au_entity_id ORDER BY au_time DESC)
                                                                            c
            FROM tab t)
GROUP BY au_entity_id
Re: useful analysis from history tables [message #446868 is a reply to message #446671] Wed, 10 March 2010 14:32 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Ayush. I had to add an additional entry in the partition clause so as to select only the most recent entries. Great solution! Thanks!

@ Moderators- how can I assign points to this post?

EA
Re: useful analysis from history tables [message #446871 is a reply to message #446469] Wed, 10 March 2010 15:17 Go to previous message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
We don't have a points system on orafaq.
Previous Topic: SQL Count and Sort?
Next Topic: External Table
Goto Forum:
  


Current Time: Wed Dec 07 18:24:20 CST 2016

Total time taken to generate the page: 0.25135 seconds