Topic
  • 3 replies
  • Latest Post - ‏2014-04-21T03:05:27Z by CQAdmin
npotti
npotti
2 Posts

Pinned topic How to build a complex query using CQ API

‏2014-04-10T14:51:12Z |

Hello forum members

I need to create a PERL script for CQ API to execute following query on record type WorkOrder:

$TargetRelease variable is initialized to Specified TargetRelease value.

Query filter: (State=closed) AND ( parentDefect.Release = $TargetRelease OR parentFeature.Release=$TargetRelease)

I tried the attached script and it is not working. Can anyone please let me know right way to build the query filter. Any help is greatly appreciated.

 

Thanks

NP

 

 

Attachments

  • CQAdmin
    CQAdmin
    103 Posts
    ACCEPTED ANSWER

    Re: How to build a complex query using CQ API

    ‏2014-04-11T02:47:13Z  

    Simpler way is to create the desired query in ClearQuest first. Then select 'View SQL Pane" from "View" on the top bar. You would be able to see the SQL query. Copy it from there and use it in your script.

    The only other way is to have SQL expertise.

  • CQAdmin
    CQAdmin
    103 Posts

    Re: How to build a complex query using CQ API

    ‏2014-04-11T02:47:13Z  

    Simpler way is to create the desired query in ClearQuest first. Then select 'View SQL Pane" from "View" on the top bar. You would be able to see the SQL query. Copy it from there and use it in your script.

    The only other way is to have SQL expertise.

  • npotti
    npotti
    2 Posts

    Re: How to build a complex query using CQ API

    ‏2014-04-16T11:30:34Z  
    • CQAdmin
    • ‏2014-04-11T02:47:13Z

    Simpler way is to create the desired query in ClearQuest first. Then select 'View SQL Pane" from "View" on the top bar. You would be able to see the SQL query. Copy it from there and use it in your script.

    The only other way is to have SQL expertise.

    Thanks a lot for your help CQAdmin. I was able to get the complex query working using the solution you gave, getting the SQL query from CQ client and inserting it into the Perl script. .

    Using trial and error approach I was able to get the complex query working without using SQL query. The code is included below.


    my $sessionObj = CQSession::Build() or die "$!";

    $sessionObj->UserLogon("$username", "$pwd", "$database", "$dbset");
    $queryDefObj = $sessionObj->BuildQuery("Work_Order");

    foreach $field (@wo_fields) ### @wo_fields array needs to be initialized with desired WO fields.
    {
    $queryDefObj->BuildField($field);
    }

    $filterNode1=$queryDefObj->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_AND);

    $filterNode1->BuildFilter("State", $CQPerlExt::CQ_COMP_OP_EQ, $rWOstate);

    ####Building Filter Node 2 for (backtofeature.Target_Release eq specified TRV) OR (backtodefect.Target_Release eq specified TRV)
    $filterNode2=$filterNode1->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_OR);
    $filterNode2->BuildFilter("backtofeature.Target_Release", $CQPerlExt::CQ_COMP_OP_EQ, ["$TRV"]);
    $filterNode2->BuildFilter("backtodefect.Target_Release", $CQPerlExt::CQ_COMP_OP_EQ, ["$TRV"]);

    ####Building Filter Node 3 for (backtofeature.Parent_Release IS NULL) OR (backtodefect.Parent_Release IS NULL)
    $filterNode3=$filterNode1->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_AND);
    $filterNode3->BuildFilter("backtofeature.Parent_Release", $CQPerlExt::CQ_COMP_OP_IS_NULL, [""]);
    $filterNode3->BuildFilter("backtodefect.Parent_Release", $CQPerlExt::CQ_COMP_OP_IS_NULL, [""]);

    $resultSet=$sessionObj->BuildResultSet($queryDefObj);

    $resultSet->Execute();
     

  • CQAdmin
    CQAdmin
    103 Posts

    Re: How to build a complex query using CQ API

    ‏2014-04-21T03:05:27Z  
    • npotti
    • ‏2014-04-16T11:30:34Z

    Thanks a lot for your help CQAdmin. I was able to get the complex query working using the solution you gave, getting the SQL query from CQ client and inserting it into the Perl script. .

    Using trial and error approach I was able to get the complex query working without using SQL query. The code is included below.


    my $sessionObj = CQSession::Build() or die "$!";

    $sessionObj->UserLogon("$username", "$pwd", "$database", "$dbset");
    $queryDefObj = $sessionObj->BuildQuery("Work_Order");

    foreach $field (@wo_fields) ### @wo_fields array needs to be initialized with desired WO fields.
    {
    $queryDefObj->BuildField($field);
    }

    $filterNode1=$queryDefObj->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_AND);

    $filterNode1->BuildFilter("State", $CQPerlExt::CQ_COMP_OP_EQ, $rWOstate);

    ####Building Filter Node 2 for (backtofeature.Target_Release eq specified TRV) OR (backtodefect.Target_Release eq specified TRV)
    $filterNode2=$filterNode1->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_OR);
    $filterNode2->BuildFilter("backtofeature.Target_Release", $CQPerlExt::CQ_COMP_OP_EQ, ["$TRV"]);
    $filterNode2->BuildFilter("backtodefect.Target_Release", $CQPerlExt::CQ_COMP_OP_EQ, ["$TRV"]);

    ####Building Filter Node 3 for (backtofeature.Parent_Release IS NULL) OR (backtodefect.Parent_Release IS NULL)
    $filterNode3=$filterNode1->BuildFilterOperator($CQPerlExt::CQ_BOOL_OP_AND);
    $filterNode3->BuildFilter("backtofeature.Parent_Release", $CQPerlExt::CQ_COMP_OP_IS_NULL, [""]);
    $filterNode3->BuildFilter("backtodefect.Parent_Release", $CQPerlExt::CQ_COMP_OP_IS_NULL, [""]);

    $resultSet=$sessionObj->BuildResultSet($queryDefObj);

    $resultSet->Execute();
     

    My Pleasure.

    :-)