How to create Query using IN?

Question

I need to collect data and add it to temporary table in AX 2012 R3 using X++.

This is the Query on SQL

select store, receiptid, itemid, str(qty,16,0) as Qty, str(price,16,0) as   Price, str(DISCAMOUNT,16,0) DiscAmount, str(taxamount,16,0) SalesTaxAmount ,convert(date, transdate) transdate, DATAAREAID from RETAILTRANSACTIONSALESTRANS
where DATAAREAID in ('5740','5760') and transdate >='2016-03-21' and transdate <='2016-03-27' and store in ('JTJDRN1','JNUSADP','JOFFICE')
and INVENTSTATUSSALES='2' and itemid in ('10010038') and receiptid in (select receiptid from RETAILTRANSACTIONPAYMENTTRANS where transdate >='2016-03-21' and transdate <='2016-03-27')
order by transdate

User can input transDate, itemid and storeid

this is what form looks like

this is my code so far

private void RetailPromoReport()
{
str         receiptId, curDatetxt,fileLocation, filePath, itemtxt,  startPtxt, endPtxt,
            storetxt, item_txt, item2, receiptId2, rcptid_txt, store_txt, store2;
FileName    fileName;
str 50      item, itemid, store;
container   items, receiptid_con, stores;
int         i,x, ware, itm, tot, y,z, rcptLen, storeLen;

Date        emptyDate, startP, endP;

RetailTransactionPaymentTrans   rtpt;
RetailTransactionSalesTrans     rtst;
ReportRetailTemp                rrpi_tmp, rrpi_tmp2;

QueryBuildRange         qbr1, qbr2, qbr3, qbr4, qbr5;

QueryRun                queryRun;
Query                   query, query2;
QueryBuildDataSource    qbdsRetailTransactionPaymentTrans, qbdsRetailTransactionSalesTrans;

RecordInsertList       recordILCRppi_tmp = new RecordInsertList(tableNum(ReportRetailTemp),false,false,false,false,false,rrpi_tmp);
;

startP   = DateFrom.dateValue();
endP     = DateTo.dateValue();
tot      = 0;

delete_from rrpi_tmp;

while  select receiptId from rtpt group by rtpt.receiptId where rtpt.transDate >= startP && rtpt.transDate <= endP
{
    receiptid_con += rtpt.receiptId;
}

query = new Query();

qbdsRetailTransactionSalesTrans = query.addDataSource(tableNum(RetailTransactionSalesTrans));
qbr1 = qbdsRetailTransactionSalesTrans.addRange(fieldNum(RetailTransactionSalesTrans,TransDate));
qbr1.value(strfmt('(%3.transDate>=%1) && (%3.transDate<=%2)',Date2StrXpp(startP),Date2StrXpp(endP),qbdsRetailTransactionSalesTrans.name()));

qbr2 = qbdsRetailTransactionSalesTrans.addRange(fieldNum(RetailTransactionSalesTrans,inventStatusSales));
qbr2.value(queryValue(enum2str(RetailInventStatusSales::Posted)));

items = msCtrlCust.getSelectedFieldValues();
itemtxt = multilookupItem.valueStr();

stores = msCtrlStore.getSelectedFieldValues();
storetxt = multilookupStore.valueStr();

if(itemtxt != "")
{
    item_txt = conPeek(items,1);
    item2 = strFmt('(%2.itemId == "%1") ',queryValue(conPeek(items,1)),qbdsRetailTransactionSalesTrans.name());

    itm = conlen(items);
    if(itm > 1)
    {
         for (i = 2; i <= itm;i++)
         {
             item = conPeek(items,i);
             item2 = strFmt('%1 || (%3.itemId == "%2") ',item2, queryValue(item),qbdsRetailTransactionSalesTrans.name());
         }
    }

    qbr3 = qbdsRetailTransactionSalesTrans.addRange(fieldNum(RetailTransactionSalesTrans, itemId));
    qbr3.value(strFmt("%1",item2));
}

rcptLen = conlen(receiptid_con);
receiptId2 = strFmt('(%2.receiptId == "%1") ',queryValue(conPeek(receiptid_con,1)),qbdsRetailTransactionSalesTrans.name());

if(rcptLen > 1)
{
    for (y = 2; y <= rcptLen; y++)
    {
        rcptid_txt = conPeek(receiptid_con,y);
        receiptId2 = strFmt('%1 || (%3.receiptId == "%2") ',receiptId2, queryValue(rcptid_txt),qbdsRetailTransactionSalesTrans.name());
     }
}

qbr4 = qbdsRetailTransactionSalesTrans.addRange(fieldNum(RetailTransactionSalesTrans, receiptId));
qbr4.value(strFmt("%1",receiptId2));

if(storetxt != '')
{
    store_txt = conPeek(stores,1);
    store2 = strFmt('(%2.store == "%1") ',queryValue(conPeek(stores,1)),qbdsRetailTransactionSalesTrans.name());

    storeLen = conlen(stores);
    if(storeLen > 1)
    {
         for (z = 2; z <= storeLen;z++)
         {
             store = conPeek(stores,z);
             store2 = strFmt('%1 || (%3.store == "%2") ',store2, queryValue(store),qbdsRetailTransactionSalesTrans.name());
         }
    }

    qbr5 = qbdsRetailTransactionSalesTrans.addRange(fieldNum(RetailTransactionSalesTrans, store));
    qbr5.value(strFmt("%1",store2));
}


qbdsRetailTransactionSalesTrans.addSortField(fieldNum(RetailTransactionSalesTrans, transDate),SortOrder::Ascending);
qbdsRetailTransactionSalesTrans.addSortField(fieldNum(RetailTransactionSalesTrans, itemId),SortOrder::Ascending);

queryRun = new QueryRun(query);

while (queryRun.next())
{
    rtst                    = queryRun.getNo(1);

    rrpi_tmp.store          = rtst.store;
    rrpi_tmp.receiptId      = rtst.receiptId;
    rrpi_tmp.itemId         = rtst.itemId;
    rrpi_tmp.qty            = rtst.qty;
    rrpi_tmp.price          = rtst.price;
    rrpi_tmp.discAmount     = rtst.discAmount;
    rrpi_tmp.SalestaxAmount = rtst.taxAmount;
    rrpi_tmp.transDate      = rtst.transDate;

    recordILCRppi_tmp.add(rrpi_tmp);
    tot++;
}

 ttsBegin;
    recordILCRppi_tmp.insertDatabase();
 ttsCommit;

    ReportRetailTemp_ds.research(true);
    ReportRetailTemp_ds.refresh();

    if(tot > 0)
    {
        Box::info(strFmt("%1 row data",tot));
    }
    else
    {
        Box::info(strFmt("No Data",tot));
    }
}

My code doesn't show any error in short period but because receiptId is stored in str,

receiptId2 = strFmt('%1 || (%3.receiptId == "%2") ',receiptId2, queryValue(rcptid_txt),qbdsRetailTransactionSalesTrans.name());

there is limitation and show error for long periode

Can someone make my code more efficient and is there any way to create Query in x++ that have same function like "IN" on SQL

Answer 1

You have two options:

• You can use more than one query range for the same field; it will automatically count as an or

  for (i = conLen(items); i > 0; i--)
      qbdsRetailTransactionSalesTrans.addRange(fieldNum(RetailTransactionSalesTrans, itemId)).value(queryValue(conPeek(items,i)));
  

  You may need special handling, if the container is empty!

• Often it is better to use an (exists) join instead

  ds = qbdsRetailTransactionSalesTrans.addDatasource(tableNum(RetailTransactionPaymentTrans));
  ds.joinMode(JoinMode::ExistsJoin);
  ds.relations(true); // Or do ds.addLink(...) etc.
  

  I am not sure I follow the correct logic here :)

If you need to do crosscompany selections, do so using the interface for that:

qbdsRetailTransactionSalesTrans.allowCrossCompany(true);
qbdsRetailTransactionSalesTrans.addCompanyRange('5740');
qbdsRetailTransactionSalesTrans.addCompanyRange('5760');