x++ code to define or conditions in querybuildrange for different fields

Hello,

I need OR condition for different fields in query range.

here is my code.

I have tried to keep different query range object but it is taking AND condition

qbr_ContractYr2 = qbds_contractYr.addRange(fieldNum(uerContractYear, BeginningDate));
qbr_ContractYr2.value(QueryValue(sysquery::range(fromDt, toDt)));

qbr_ContractYr3 = qbds_contractYr.addRange(fieldNum(uerContractYear, EndingDate));
qbr_ContractYr3.value(QueryValue(sysquery::range(fromDt, toDt)));

 

Even though i tried to keep same query range object it is taking AND condition.

qbr_ContractYr2 = qbds_contractYr.addRange(fieldNum(uerContractYear, BeginningDate));
qbr_ContractYr2.value(QueryValue(sysquery::range(fromDt, toDt)));

qbr_ContractYr2 = qbds_contractYr.addRange(fieldNum(uerContractYear, EndingDate));
qbr_ContractYr2.value(QueryValue(sysquery::range(fromDt, toDt)));

 

How will achieve OR condition for different fields in this case please suggest

  • You can use expressions,
    msdn.microsoft.com/.../aa893981.aspx

  • The only difference between your two snippets is whether you're reusing the same variable or not. There is no difference at all in functionality; you're creating identical ranges in both cases.

    If you want to combine two ranges for different fields, you'll have to use expression in query ranges (which are ugly, but sometimes necessary).

  • In reply to Kranthi:

    Can you please tell me how will i achieve this...actually, i saw this site but i didn't understand how to use it in my case
  • In reply to Martin Dráb:

    Can you please tell me how will i achieve this...actually, i saw this site but i didn't understand how to use it in my case
  • In reply to Nikhilborse:

    Have you tired it? If so please show us your code.
  • In reply to Kranthi:

    First i am trying with the 1st field..below code i tried

    qbr_ContractYr2 = qbds_contractYr.addRange(fieldNum(uerContractYear, BeginningDate));
    qbr_ContractYr2.value(strfmt('((%1.%2 >= %3 && %1.%2 <= %4))',qbds_contractYr.name(),fieldStr(uerContractYear, BeginningDate),fromDt,toDt));

    Its giving me runtime error

    Query extended range failure: Right parenthesis expected near pos 75.
  • In reply to Nikhilborse:

    use this example,

    query = new query();
    qbds = query.addDataSource(tableNum(CustTrans));

    qr = qbds.addRange(fieldNum(CustTrans, TransDate));
    qr.value(
    strFmt('(((%1.%2 >= %4) && (%1.%2 <= %5)) || ((%1.%3 >= %4) && (%1.%3 <= %5)))',
    qbds.name(),
    fieldStr(CustTrans, TransDate),
    fieldStr(CustTrans, DocumentDate),
    fromDate,
    toDate));

  • In reply to Kranthi:

    When i debugged the code i got this query...it should give me 2 records in lookup.....but unfortunately, it is giving me blank look up...i am not understanding now what is the issue in the expression.

    SELECT FIRSTFAST * FROM uerContractYear WHERE uerOutboundGuaranteePaymentTable.uerContractYear = uerContractYear.ContractYear AND ((uerOutboundContractId = N'10126')) AND (((((uerContractYear_1.BeginningDate >= 1/1/2011) && (uerContractYear_1.BeginningDate <= 12/31/2011)) || ((uerContractYear_1.EndingDate >= 1/1/2011) && (uerContractYear_1.EndingDate <= 12/31/2011)))))
  • In reply to Nikhilborse:

    Can you show us your query?
  • In reply to Kranthi:

    sysTableLookup = SysTableLookup::newParameters(tableNum(uerOutboundGuaranteePaymentTable), _formControl);
    sysTableLookup.addLookupField(fieldNum(uerOutboundGuaranteePaymentTable, Description));

    queryBuildDataSource = query.addDataSource(tableNum(uerOutboundGuaranteePaymentTable));

    queryBuildRange = queryBuildDataSource.addRange(fieldNum(uerOutboundGuaranteePaymentTable, uerOutboundContractId));
    queryBuildRange.value(INXL_ContractMarketingBudgetDetailTable.uerOutboundContractID);

    queryBuildRange = queryBuildDataSource.addRange(fieldNum(uerOutboundGuaranteePaymentTable, uerAdvanceorGuarantee));
    queryBuildRange.value(queryValue(uerAdvanceorGuarantee::Guarantee));

    qbds_contractYr = queryBuildDataSource.addDataSource(tablenum(uerContractYear));
    qbds_contractYr.addLink(fieldNum(uerOutboundGuaranteePaymentTable, uerContractYear), fieldNum(uerContractYear, ContractYear));
    qbds_contractYr.joinMode(JoinMode::InnerJoin);


    qbr_ContractYr1 = qbds_contractYr.addRange(fieldNum(uerContractYear, uerOutboundContractId));
    qbr_ContractYr1.value(INXL_ContractMarketingBudgetDetailTable.uerOutboundContractID);

    fromDt = mkdate(1, 1, str2int(INXL_ContractMarketingBudgetDetailTable.Year));
    toDt = mkdate(31, 12, str2int(INXL_ContractMarketingBudgetDetailTable.Year));

    qbr_ContractYr1 = qbds_contractYr.addRange(fieldNum(uerContractYear, BeginningDate));
    qbr_ContractYr1.value(strfmt('(((%1.%2 >= %4) && (%1.%2 <= %5)) || ((%1.%3 >= %4) && (%1.%3 <= %5)))',qbds_contractYr.name(),fieldStr(uerContractYear, BeginningDate),fieldStr(uerContractYear, EndingDate),fromDt,toDt));


    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
  • In reply to Nikhilborse:

    I don't see any issue with the expression. Please try by investigating the query results (running it separately in a job)
  • In reply to Kranthi:

    Or maybe you're designing wrong query. Please simplify your query to contain just the problematic fields, so we don't have to deal with unrelated code (designing a new table just for this testing would be a good idea), tell us which records you're expecting to get and that parameters are you using.
    If you provide a project that anybody can import and run, you'll have a good chance that somebody will do it, and it will make sure that we all talk about the same thing.
Related
Recommended