Expr In Query For Dates

 
Class CustTransQueryAgingBucktetStat:


    str                     dateNull;
    Range                   rangeDocDate;
    date                    sqlFromDate;
    date                    sqlToDate;

    str date2SqlStr(date _date)
    {
        str sqlDate;

        if (_date == dateNull())
        {
            sqlDate = '01\\01\\1901';
        }
        else
        {
            if(_date == maxdate())
            {
                sqlDate = '31\\12\\2150';
            }
            else
            {
                sqlDate = date2str(_date, 123, 2, 3, 2, 3, 4);
                sqlDate = strReplace(sqlDate, '-', '\\');
            }
        }
        return sqlDate;
    }

    ;



    range = transDataSource.addRange(fieldnum(CustTrans, RecId));
    sqlFromDate = statementEndDate ? statementStartDate : dateNull();
    sqlToDate   = statementEndDate   ? statementEndDate   : maxdate();
    dateNull = date2StrXpp(dateNull());
    rangeDocDate  = '((DocumentDate >= ' + date2SqlStr(sqlFromDate) + ') &&';
    rangeDocDate += '(DocumentDate <= ' + date2SqlStr(sqlToDate) + ')) ||';
    rangeDocDate += '((DocumentDate = ' + dateNull + ') &&';
    rangeDocDate += '(TransDate >= ' + date2SqlStr(sqlFromDate) + ') && ';
    rangeDocDate += '(TransDate <= ' + date2SqlStr(sqlToDate) + '))';

    range.value(rangeDocDate);

Using joins in Query

void initParmDefault()
{
    Query                       query;
    QueryBuildDataSource        queryBuildDataSource,queryBuildDSProd;
    QueryBuildRange queryBuildRange,queryBuildRangeStatus;
    ;
    super();

    query = new Query();
    queryBuildDataSource = query.addDataSource(tablenum(InventTable));
    queryBuildDataSource.addSelectionField(fieldnum(InventTable, ItemId));
    queryBuildRange = queryBuildDataSource.addRange(fieldnum(InventTable, ItemType));
    queryBuildRange.value(SysQuery::value(ItemType::BOM));
    queryBuildRange.status(RangeStatus::Locked);
    queryBuildDataSource.addRange(fieldnum(InventTable, itemId));

    queryBuildDSProd = queryBuildDataSource.addDataSource(tablenum(ProdTable));
    queryBuildDSProd.addSelectionField(fieldnum(prodTable,itemId));
    queryBuildRangeStatus = queryBuildDSProd.addRange(fieldnum(ProdTable,ProdStatus));
    queryBuildRangeStatus.value(SysQuery::range(ProdStatus::Created,ProdStatus::StartedUp));
    queryBuildRangeStatus.status(RangeStatus::Locked);

    queryBuildDSProd.joinMode(JoinMode::ExistsJoin);
    queryBuildDSProd.fetchMode(QueryFetchMode::One2One);
    queryBuildDSProd.addLink(fieldnum(inventTable,itemid),fieldnum(prodTable,itemId));

    queryRun = new QueryRun(query);

    //QueryRun = new QueryRun(querystr(InventTable));


}

Query: Using groupby and order by when using sum()

Using groupby and order by when using sum()

Select sum(qtyOrdered) from SalesLine order by SalesLine.LineNum Desc group by salesLine.LineNum, salesLine.CustAccount

Note: the order of columns in "Order By" should be same as in "Group By" else it will not work.

In case of query, the addsortfield should be done before adding the addGroupByField fields
ds.ordermode(OrderMode:GroupBy) should not be used (using this will prevent the OrderBy from working but there is a draw back. The grid sorting will not work)

Datasource-> init() method

QueryBuildDataSource OperationsDs;

;

OperationsDs = this.query().dataSourceTable(tablenum(CAP_OperationsList));

OperationsDs.addSortField(fieldNum(CAP_OperationsList, CAP_ProfileReferenceGroupId),sortOrder::Ascending);

OperationsDs.addSortField(fieldNum(CAP_OperationsList, CAP_CutLength),sortOrder::Descending);

OperationsDs.addGroupByField(fieldnum(CAP_OperationsList, CAP_ProfileReferenceGroupId));

OperationsDs.addGroupByField(fieldnum(CAP_OperationsList, CutGroupId));

//OperationsDs.orderMode(OrderMode::GroupBy);