Forum Discussion

jaimecalero's avatar
jaimecalero
Visitor
5 years ago

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 th...
Fyodor_A's avatar
Fyodor_A
SmartBear Alumni (Retired)
5 years ago

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.