SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 5 of 5
  1. #1
    Junior Member
    Join Date
    Apr 2008
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Quality Center Back-End Database Query

    Hi guys!

    Anyone out there know how to write an SQL query for the back-end database for QualityCenter where you can get:

    1. All defects related to a single Test Run
    2. All the Test Runs related to a Defect

    Cheers guys, any help is much appreciated.

    El

  2. #2
    Member
    Join Date
    Oct 2007
    Location
    TN, India
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Quality Center Back-End Database Query

    Whether you need the query or the place where to write a query?

    Query for listing all the defects from a run/cycle.

    SELECT * FROM BUG Where BG_CYCLE_ID = 'specify the cycle id.

    (OR)

    SELECT * FROM BUG Where BUG.BG_DETECTION_DATE = ' Specify the date in which the test is runned.

    Create a new Excel Report and place the SQL query in the Excel Report Generator.

    You can go the Excel Generator thro' In the QC goto Tools -> Excel Reports.

    There you can find many tables listed in the right pane, from there select the table BUG where you can find the columns in that table, add the required column names in the above select query as per ur requirement.

    Like that the above procedure you can write the query for ur second requirement. by

    Select * from BUG where BUG.BUG_ID = ' Specify the Defect Id for which u need the details of the test run.

    Kindly let me know if you have any queries.

    Thanks
    Vill
    Cognizant

  3. #3
    Junior Member
    Join Date
    Apr 2008
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Quality Center Back-End Database Query

    Hey, cheers for the help.

    I just need the actual SQL as I can do the exporting via a tool that's been created into a report format.

    However there's a bit of an issue As a defect may have been linked to multiple Test I need to find out which Tests a defect has been linked to:
    For Example:

    Defects and all their associated links in the form of tests.

    Defect 1 - Test 1
    - Test 2

    And then I require a query that can do:

    Test 1 - All associated defects i.e.
    - Defect 1
    - Defect 2

    The other slight problem is that BG_CYCLE_ID isn't being populated in the schema I'm using - is there a way round that?

    Any help would be much appreciated.

    Cheers,

    Elliot

  4. #4
    Junior Member
    Join Date
    May 2007
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Quality Center Back-End Database Query

    I use something similar to the following SQL:

    This on returns bugs and tests based on all links to that test and uses the Test in Test Plan.
    <font class="small">Code:</font><hr /><pre> SELECT DISTINCT BUG.BG_BUG_ID, TEST.TS_NAME
    FROM V_LINK_TEST INNER JOIN
    BUG ON V_LINK_TEST.LN_BUG_ID = BUG.BG_BUG_ID INNER JOIN
    TEST ON V_LINK_TEST.LN_TEST_ID = TEST.TS_TEST_ID
    ORDER BY BUG.BG_BUG_ID </pre><hr />

    This one is the same except looks at links to a test in a test set (TESTCYCL).
    <font class="small">Code:</font><hr /><pre> SELECT DISTINCT BUG.BG_BUG_ID, TEST.TS_NAME AS Expr1
    FROM V_LINK_TESTCYCL INNER JOIN
    BUG ON V_LINK_TESTCYCL.LN_BUG_ID = BUG.BG_BUG_ID INNER JOIN
    TESTCYCL ON V_LINK_TESTCYCL.LN_TESTCYCL_ID = TESTCYCL.TC_TESTCYCL_ID INNER JOIN
    TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
    ORDER BY BUG.BG_BUG_ID </pre><hr />

    NOTE: Both of these will return bugs for indirect links as well, for instance if a bug is linked to a Step or Run of the test, it's included here as well as those directly linked to the Test.

    I'm using QC v 9.0

  5. #5
    Junior Member
    Join Date
    Jan 2009
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Quality Center Back-End Database Query

    Thanks a lot for the query, now I only need to extend it so you can also see the Defects that are linked to Test Sets.
    Please tell me if you got any pointers for this.

    I did manage to only get the bugs that are linked to a test that is placed in a specific folder in Test Lab.

    CODE:
    -------------------------------------------------------------
    SELECT DISTINCT BUG.BG_BUG_ID, BUG.BG_RESPONSIBLE, BUG.BG_CLOSING_DATE, BUG.BG_DETECTED_BY, BUG.BG_DETECTION_DATE, BUG.BG_USER_08, BUG.BG_STATUS, BUG.BG_SUMMARY,
    TEST.TS_NAME AS testcase_naam,
    TEST.TS_STATUS AS Status
    FROM V_LINK_TESTCYCL
    INNER JOIN BUG
    ON V_LINK_TESTCYCL.LN_BUG_ID = BUG.BG_BUG_ID
    INNER JOIN TESTCYCL
    ON V_LINK_TESTCYCL.LN_TESTCYCL_ID = TESTCYCL.TC_TESTCYCL_ID
    INNER JOIN TEST
    ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
    INNER JOIN CYCLE
    ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
    INNER JOIN CYCL_FOLD
    ON CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID

    WHERE
    (CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAAAAAA%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAAAAAB%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAAAAAC%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAAAAAD%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAAAAAE%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAAAAAI%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAAAAAH%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAAAAAG%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAABAAA%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAABAAB%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAABAAC%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAABAAD%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAABAAE%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAABAAI%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAABAAH%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAABAAG%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAADAAA%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAADAAB%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAADAAC%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAADAAD%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAADAAE%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAADAAI%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAADAAH%'
    OR
    CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAADAADAAG%')

    ----------------------------------------------------------

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 10.00%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 06:17 AM.

Copyright BetaSoft Inc.