How to Set Dynamic Parameter of Query From External Script
Below is the perl code
##############################################
my $workSpace=$userSession->GetWorkSpace(); #$userSession should be created before
#Personal Queries/Dymic is a query with 2 dynamic fields state and submit date
my $queryDef=$workSpace->GetQueryDef("Personal Queries/Dymic");
my $rstSet=$userSession->BuildResultSet($queryDef);
# set state value list
$rstSet->AddParamValue(1,"Submitted");
$rstSet->AddParamValue(1,"Assigned");
# first set compare operator for the submit date. 9 means "between"
$rstSet->SetParamComparisonOperator(2,9);
# then set 2 time value for between
$rstSet->AddParamValue(2,"2010-04-23 00:00:00");
$rstSet->AddParamValue(2,"2010-04-23 23:59:59")
$rstSet->Execute;
print $rstSet->GetSQL();
#Personal Queries/Dymic is a query with 2 dynamic fields state and submit date
my $queryDef=$workSpace->
my $rstSet=$userSession->
# set state value list
$rstSet->AddParamValue(1,"
$rstSet->AddParamValue(1,"
# first set compare operator for the submit date. 9 means "between"
$rstSet->
# then set 2 time value for between
$rstSet->AddParamValue(2,"
$rstSet->AddParamValue(2,"
$rstSet->Execute;
print $rstSet->GetSQL();
##############################################
The output likes this,
select distinct T1.dbid,T1.id from new_defect T1,statedef T2 where T1.state = T2.id and (T1.dbid 0 and ((T2.name in ('Submitted','Assigned') and T1.submit_date between {ts '2010-04-21 08:00:00'} and {ts '2010-04-23 08:00:00'})))
select distinct T1.dbid,T1.id from new_defect T1,statedef T2 where T1.state = T2.id and (T1.dbid 0 and ((T2.name in ('Submitted','Assigned') and T1.submit_date between {ts '2010-04-21 08:00:00'} and {ts '2010-04-23 08:00:00'})))
