Dynamic Query works based on OR condition ?

Hi, Everyone.

I want records based on query act like or condition. I have two queries, The case is 1st query have the records it pulls out otherwise act 2nd query and get the records. Below is my query Please review it. That’s correct or not.

public void executeQuery()
{
    Date                    _date;
    ProcessLineProdLoad     pickLastDate,_processLineProdLoad;
    Query                   q = new Query();
    Query                   q2 = new Query();
    QueryBuildDataSource    qbr1,qbds2;
    QueryBuildRange         qbr,qbr2;
    QueryRun                qr,qr2;
    NoYes                   _unrackedFlag,_rackedFlag;
    boolean                     deviatedQuery;
    ;

    _unrackedFlag = NoYes::No;
    _rackedFlag   = NoYes::Yes;

    while select pickLastDate
        order by RackStartDate desc
        {
            if(pickLastDate.RackStartDate != datenull())
            {
            _date = pickLastDate.RackStartDate;
            }
        }

        qbr1 = q.addDataSource(tablenum(ProcessLineProdLoad));

       this.query().dataSourceNo(1).clearRanges();



   if(ProductionPool.valueStr()||SchedDate.dateValue()||ProcessLine.valueStr()||UnLoadedFlag.valueStr())
   {
     
   // 1st query started

    qbr =qbr1.addRange(fieldNum(ProcessLineProdLoad,ProcessLineId));
    qbr.value(ProcessLine.valueStr());

    qbr =    qbr1.addRange(fieldNum(ProcessLineProdLoad,SchedDate));
    qbr.value(sysquery::range(SchedDate.dateValue(),SchedDate.dateValue()));

    qbr =qbr1.addRange(fieldNum(ProcessLineProdLoad,ProdPoolId));
    qbr.value(ProductionPool.valueStr());

    qbr =qbr1.addRange(fieldNum(ProcessLineProdLoad,UnRackedFlag));
    qbr.value(UnLoadedFlag.valueStr());

    qbr = qbr1.addRange(fieldnum(ProcessLineProdLoad,RackedFlag));
    qbr = qbr1.addRange(fieldNum(ProcessLineProdLoad,RackStartDate));
    qbr =  qbr1.addRange(fieldNum(ProcessLineProdLoad, RackedQty));
    
    qbr = qbr1.addRange(fieldnum(ProcessLineProdLoad,Division));


    qbr.value(strfmt('(( (%1 == %2)) || ((%3 >= %4)||(%3<=%5))&& (%6 > %7) && (%8 == %9))',    

    fieldstr(ProcessLineProdLoad,RackedFlag),
    queryValue("1"),

    fieldstr(ProcessLineProdLoad,RackStartDate),
    queryValue(_date),
    queryValue(today()),

    fieldstr(ProcessLineProdLoad,RackedQty),
    queryValue("0"),    

    fieldstr(ProcesslineProdLoad,Division),
    queryValue("22")
    ));

    qbr1.addSortField(fieldnum(ProcessLineProdLoad,ProcessLineId),SortOrder::Ascending);
    qbr1.addSortField(fieldnum(ProcessLineProdLoad,SchedDate),SortOrder::Ascending);
    qbr1.addSortField(fieldnum(ProcessLineProdLoad,LoadRunOrder),SortOrder::Ascending);
    qbr1.addSortField(fieldnum(ProcessLineProdLoad,LoadNum),SortOrder::Ascending);
    qbr1.addSortField(fieldnum(ProcessLineProdLoad,ProdId),SortOrder::Ascending);

    qr = new QueryRun(q);

    while(qr.next())
    {
        _processLineProdLoad = qr.get(tablenum(ProcessLineProdLoad));

        if(_processLineProdLoad)
        {
            this.query(q);
            deviatedQuery = true;
        }

    }

     // 1st query ended

     // 2nd query started

        if(!deviatedQuery)
        {
            qbds2 = q2.addDataSource(tablenum(ProcessLineProdLoad));

            qbr2 = qbds2.addRange(fieldnum(ProcessLineProdLoad,ProcessLineId));
            qbr2.value(ProcessLine.valueStr());

            qbr2 = qbds2.addRange(fieldnum(ProcessLineProdLoad,UnrackedFlag));
            qbr2.value(UnLoadedFlag.valueStr());

            qbr2 = qbds2.addRange(fieldnum(ProcessLineProdLoad,RackedFlag));
            qbr2.value("1");


            qr2 = new QueryRun(q2);

            while(qr2.next())
            {
                _processLineProdLoad = qr2.get(tablenum(ProcessLineProdLoad));

                if(_processLineProdLoad)
                {
                    this.query(q2);
                }

            }
        }
    
     // 2nd query ended
    }
   
        super();
   
}

It’s 130 lines of code. Which part do you want us to review? Couldn’t you simplify the code before posting it here, to demonstrate your point without unrelated things?

Line 80 has the process to proceed 1st query in case it will fail then line 96 will be executed or not? This is my doubt Martin.

Rather than how can I write OR conditions in Dynamic Query…

If I understand it correctly, you have code like this:

Query                   q = new Query();
Query                   q2 = new Query();
QueryRun                qr,qr2;
boolean                     deviatedQuery;

// ... adding data sources and ranges to q ...

qr = new QueryRun(q);

while(qr.next())
{
    deviatedQuery = true;
    break;
}

if(!deviatedQuery)
{
    // ... adding data sources and ranges to q2 ...

    qr2 = new QueryRun(q2);

    while(qr2.next())
    {
        // ...
    }
}

Line 96 was if(!deviatedQuery). Unless the code above it throws an exception, this line of code will be executed. If the first query doesn’t return any records, the condition will be met and the block below will execute. If it finds a record, the condition will be false and the if block will be skipped.

Note that your check doesn’t need more than record and a single field, therefore you could improve performance by adding firstOnly flag, throw away sorting and return only RecId.

Your while select pickLastDate is also wasting resources. You can add the check for RackStartDate to the where clause, so it will filter the data instead of returning all records from ProcessLineProdLoad table to the application. Again, you can use firstOnly and select only a single field. Like this:

select firstOnly RackStartDate from pickLastDate
    order by RackStartDate desc
    where pickLastDate.RackStartDate != datenull();

I have cleared my doubts thanks, Martin [emoticon:c4563cd7d5574777a71c318021cbbcc8]