Highlighted
Absent Member.
Absent Member.
362 views

Excel Report Generator Confusion

Jump to solution
I was trying to do some queries to get the number of defects that have reopens, and how many times they were reopened. I was using the Bug, Audit_Log, and Audit_Properties tables to do this, but I ran into troubles with the SQL. We used to have a SQL Server database, and all these statements worked, but now with Oracle I'm having troubles.

Here is a sample SQL statement. It isn't for the above mentioned scenerios:
SELECT * FROM BUG /*Defect*/
inner join AUDIT_LOG /*Audit Log*/ on AUDIT_LOG.AU_ENTITY_ID /*Audit Log.Entity ID*/ = BUG.BG_BUG_ID /*Defect.Defect ID*/
where BUG.BG_USER_04 /*Defect.Product*/ = 'test'

If I do Test Query it works, if I do run query I get this message:
---------------------------
Error
---------------------------
The SQL query has failed to run, with the following error:
[Mercury][Oracle JDBC Driver](Internal Error) Message 1018 not found.
---------------------------
OK
---------------------------

Any advice would be greatly appreciated.
0 Likes
1 Solution

Accepted Solutions
Highlighted
Absent Member.
Absent Member.
Here is the query to get the number of times each defect was reopened (assuming that you have a "Reopen" status for the defects) -

SELECT AU_ENTITY_ID, count(*), BG_SUMMARY
FROM audit_log, audit_properties, BUG where ap_table_name='bug' and AU_Action_ID =AP_Action_ID and AU_ENTITY_ID = BG_BUG_ID and AP_NEW_VALUE ='Reopen'
group by AU_ENTITY_ID, BG_SUMMARY, BG_SEVERITY

You can replace 'Reopen' with any other status.

View solution in original post

4 Replies
Highlighted
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class
Hi Jared,
is there more than one sheet with queries?

If you are testing the queries each sheet is executed exclusivly depending on the active sheet.
If you are running the queries all queries on every sheet will be executed.
Because the error message refers to sheet2 I think there are minimum two sheets with queries.

Copy the query on the other sheet to an external program and delete that sheet, you do not need just now. Try again.

I hope it helps.

Greetings
Lothar
Highlighted
Absent Member.
Absent Member.
I did not know that, thanks. I removed sheet2 and reran it and I get this error:

---------------------------
Error
---------------------------
The SQL query has failed to run, with the following error:
[Mercury][Oracle JDBC Driver](Internal Error) Message 1018 not found.
---------------------------
OK
---------------------------

Thanks
Jared Sherman
0 Likes
Highlighted
Absent Member.
Absent Member.
Here is the query to get the number of times each defect was reopened (assuming that you have a "Reopen" status for the defects) -

SELECT AU_ENTITY_ID, count(*), BG_SUMMARY
FROM audit_log, audit_properties, BUG where ap_table_name='bug' and AU_Action_ID =AP_Action_ID and AU_ENTITY_ID = BG_BUG_ID and AP_NEW_VALUE ='Reopen'
group by AU_ENTITY_ID, BG_SUMMARY, BG_SEVERITY

You can replace 'Reopen' with any other status.

View solution in original post

Highlighted
Absent Member.
Absent Member.
Correction - Ignore the above query. There was an error in the group by clause.

Here is the correct one -

SELECT AU_ENTITY_ID, count(*), BG_SUMMARY
FROM audit_log, audit_properties, BUG where ap_table_name='bug' and AU_Action_ID =AP_Action_ID and AU_ENTITY_ID = BG_BUG_ID and AP_NEW_VALUE ='Reopen'
group by AU_ENTITY_ID, BG_SUMMARY
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.