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 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

 

  • 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.