cancel
Showing results for 
Search instead for 
Did you mean: 

Need a sample SQL query for the "ALL Defects" report.

Visitor

Need a sample SQL query for the "ALL Defects" report.

We are in need of creating a custom SQL query to get an All Defects Customizable Defect Report. The Collaborator v12.2 states that there is an SQL button that would generate an SQL query which can then be customized.
Since v9.3 does not support this feature, I wonder if you can supply a sample SQL query for the All Defects report that we can use as a starting point ( a query generated with 12.2 may be sufficient).

We will also need an SQL query to get a dect detail report ( if available).
Thanks

 

1 REPLY 1
Staff

Re: Need a sample SQL query for the "ALL Defects" report.

Hi,

 

Here is the 'All Defects' report SQL query:

SELECT
d.defect_id AS `defectid`
,dReview.review_id AS `defectreviewid`
,d.defect_state AS `defectstate`
,d.defect_createdon AS `defectcreatedon`
,dCreat.user_name AS `defectcreatorusername`
,version.version_filepath AS `defectfile`
,d.defect_text AS `defecttext`
FROM
defect d
LEFT OUTER JOIN user dCreat ON (d.defect_userid = dCreat.user_id)
LEFT OUTER JOIN review dReview ON (dReview.review_id = d.defect_reviewid)
LEFT OUTER JOIN version version ON (version.version_id = d.defect_versionid)
ORDER BY
d.defect_id DESC

 

Since the database schema hasn't been drastically changed since version 9.3, this query should also work fine with this version.