Excel Report Generator Confusion

[Jared Sherman]

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.

[Lothar Pauli]

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

[Jared Sherman]

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

[Shivaraj · Accepted solution]

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.

[Shivaraj]

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