X++ Union query

Let's see this scenario.

Condition: We'd like to have the following results. 1) all sales lines which has all status, except invoiced and cancelled and 2) all sales lines which has status = invoiced, and invoiceDate(in CustInvoiceJour) >= today

For above purpose, we can apply an union query like below.

void initQuery()
{
    Query                   query;
    QueryBuildDataSource    qbdsSalesLine,
                            qbdsCustInvoiceJour;
    ;

    query               = new query();
    query.queryType(QueryType::Union);

    qbdsSalesLine       = query.addDataSource(tableNum(SalesLine), identifierstr(SalesLine_1));
    qbdsSalesLine.addRange(fieldnum(SalesLine, SalesStatus)).value(queryValue(SalesStatus::Backorder));
    qbdsSalesLine.addRange(fieldnum(SalesLine, SalesStatus)).value(queryValue(SalesStatus::Delivered));
    qbdsSalesLine.addRange(fieldnum(SalesLine, SalesStatus)).value(queryValue(SalesStatus::None));

    qbdsSalesLine       = query.addDataSource(tablenum(SalesLine), identifierstr(SalesTable_2), UnionType::Union);
    qbdsSalesLine.addRange(fieldnum(SalesLine, SalesStatus)).value(queryValue(SalesStatus::Invoiced));

    qbdsCustInvoiceJour = qbdsSalesLine.addDataSource(tableNum(CustInvoiceJour));
    qbdsCustInvoiceJour.relations(false);
    qbdsCustInvoiceJour.addLink(fieldNum(SalesLine, SalesId), fieldNum(CustInvoiceJour, SalesId));
    qbdsCustInvoiceJour.joinMode(JoinMode::ExistsJoin);
    qbdsCustInvoiceJour.addRange(fieldnum(CustInvoiceJour, InvoiceDate)).value(SysQuery::range(today(), dateMax()));

    queryrun = new SysQueryRun(query);
}

Note that:

Union – remove duplicated records

UnionAll – keep duplicated records