Query
Query to string
queryBuildDataSource qbds;
qbds = this.query().dataSourceName(this.name());
info(qbds.toString()); --> SELECT FIRSTFAST * FROM InventSum WHERE SalesLine.ItemId=InventSum.ItemId
i1 = this.query().dataSourceName(this.name()).dynalinkCount(); //Number of dynamics link (from EDT) or from another form
i2 = this.query().dataSourceName(this.name()).linkCount(); //Link
i3 = this.query().dataSourceName(this.name()).rangeCount(); //Number of ranges
info(" DL : " + int2str(i1) + " L : " + int2str(i2)+ " R : " + int2str(i3));
New query
Query query = new Query();
QueryBuildDataSource queryBuildDataSource;
QueryBuildDataSource queryBuildDataSourceAdapter;
QueryBuildRange queryBuildRange;
QueryBuildLink queryBuildLink;
SysTableLookup sysTableLookup;
;
queryBuildDataSource = query.addDataSource(tablenum(AifAdapterLookup));
queryBuildRange = queryBuildDataSource.addRange(fieldnum(AifAdapterLookup, FormInstanceId));
queryBuildRange.value(guid2str(formInstanceId));
queryBuildDataSourceAdapter = queryBuildDataSource.addDataSource(tablenum(AifAdapter));
queryBuildDataSourceAdapter.joinMode(JoinMode::NoExistsJoin);
queryBuildLink = queryBuildDataSourceAdapter.addLink(fieldnum(AifAdapterLookup, ClassId), fieldnum(AifAdapter, AdapterClassId));
//=== new quey from queryRun
queryRun qr_UI;
query = new Query(qr_UI.pack());
FirstOnly
query.dataSourceTable(tablenum(SalesLine)).firstOnly(true);
addOrderByField
//===addOrderByField
queryBuildDataSourceWorker.sortClear();
queryBuildDataSourceWorker.addOrderByField(fieldNum(HcmWorker, PersonnelNumber), SortOrder::Descending);
//=== addSortField (old method V3)
queryBuildDataSource.orderMode(OrderMode::OrderBy);
queryBuildDataSource.addSortField(fieldnum(PBAVariableVal, pbaVar));
AddSelectionField (selectionField::sum,count...)
queryBuildDataSource.addSelectionField(fieldnum(BOMVersion, InventDimId)); // needed below for the addLookupMethod
qB.addSelectionField(fieldnum(TaxTrans, SourceInvestmentTaxCur), SelectionField::Sum);
// In this case create an or condition
qbr = qbdsProdVariance.addRange(fieldnum(TmpProdStandardVariance, Key1));
qbr.value(strfmt('((%1.%2 == %3) || (%1.%4 == %5))',
qbdsProdVariance.name(), // BOMCalcTrans %1
fieldstr(TmpProdStandardVariance, InventCostLevel), // Level %2
any2int(InventCostLevel::Total), // 0 (= Level Total) %3
fieldstr(TmpProdStandardVariance, Split), // Split %4
any2int(NoYes::No)));
AddGroupByField
public void init()
{
QueryBuildDataSource queryBuildDataSource;
QueryBuildFieldList queryBuildFieldListSum;
container c;
Counter i;
void addField(fieldId fieldId, SelectionField _type)
{
if (! confind(c,fieldId))
queryBuildFieldListSum.addField(fieldId,_type);
}
;
super();
queryBuildDataSource = this.query().dataSourceName(identifierstr(WMSOrderTransGroup));
//add grouping
queryBuildDataSource.addGroupByField(fieldnum(WMSOrderTrans,ShipmentId));
queryBuildDataSource.addGroupByField(fieldnum(WMSOrderTrans,ExpeditionStatus));
queryBuildFieldListSum = queryBuildDataSource.fields();
for (i=1;i "lesser equal" queryBuildFieldListSum.fieldCount();i++)
{
if (queryBuildFieldListSum.fieldKind(i) == SelectionField::Sum)
c+=queryBuildFieldListSum.field(i);
}
addField(fieldnum(WMSOrderTrans,Qty),SelectionField::Sum);
addField(fieldnum(WMSOrderTrans,RecId),SelectionField::Count);
queryBuildDataSource.clearRanges();
----> SELECT FIRSTFAST SUM(qty), COUNT(RecId) FROM WMSOrderTrans GROUP BY WMSOrderTrans.shipmentId, WMSOrderTrans.expeditionStatus;
}
Group by special
// This code add group by
WMSOrderTrans::queryAddSortDefault(queryBuildDataSource, OrderMode::GroupBy, false);
static public void queryAddSortDefault(QueryBuildDataSource _qbS,
OrderMode _orderMode = OrderMode::OrderBy,
// 30/06/2016-SPL_INV_062_CustomerLocation_Patient-BEGIN
boolean _groupOnPatient = false
// 30/06/2016-SPL_INV_062_CustomerLocation_Patient-END
)
{
_qbS.sortClear();
_qbS.orderMode(_orderMode);
_qbS.addSortField(fieldnum(WMSOrderTrans, RouteId));
_qbS.addSortField(fieldnum(WMSOrderTrans, IsReserved),SortOrder::Descending);//un-reserved should end up in the bottom
_qbS.addSortField(fieldnum(WMSOrderTrans, ItemSortCode));
_qbS.addSortField(fieldnum(WMSOrderTrans, SortCode));
_qbS.addSortField(fieldnum(WMSOrderTrans, ItemId));
_qbS.addSortField(fieldnum(WMSOrderTrans, ItemTagging));
_qbS.addSortField(fieldnum(WMSOrderTrans, CaseTagging));
// 30/06/2016-SPL_INV_062_CustomerLocation_Patient-BEGIN
if (_groupOnPatient)
_qbS.addSortField(fieldnum(WMSOrderTrans, SPLPatientShort));
// 30/06/2016-SPL_INV_062_CustomerLocation_Patient-END
}
Another example
void initQueryWMSOrderTransSum(FormDataSource _wmsOrderTrans_DS)
{
QueryBuildDataSource qbds;
QueryBuildFieldList qbfl;
;
qbds = _wmsOrderTrans_DS.query().dataSourceName(_wmsOrderTrans_DS.name());
if (wmsPickingRouteIdList)
SysQuery::findOrCreateRange(qbds, fieldnum(WMSOrderTrans,RouteId)).value(this.list2Range(wmsPickingRouteIdList));
else
SysQuery::findOrCreateRange(qbds, fieldnum(WMSOrderTrans,RouteId)).value(queryValue(wmsPickingRouteID));
SysQuery::findOrCreateRange(qbds, fieldnum(WMSOrderTrans,ExpeditionStatus)).value(enum2Value(WMSExpeditionStatus::Picked));
SysQuery::findOrCreateRange(qbds, fieldnum(WMSOrderTrans,FullPallet)).value(enum2Value(NoYes::No));
qbds.addSortField(fieldnum(WMSOrderTrans,RouteId));
// 26/08/2012-ADMIN-SPL-INV-041-VIRTUALLOCATION-BEGIN
qbds.addSortField(fieldnum(WMSOrderTrans,SPLSpecialPicking));
// 26/08/2012-ADMIN-SPL-INV-041-VIRTUALLOCATION-END
// 05/07/2016_SBE_SPL_INV_062_CustomerLocation_Patient-BEGIN
qbds.addSortField(fieldnum(WMSOrderTrans,SPLPatientShort));
// 05/07/2016_SBE_SPL_INV_062_CustomerLocation_Patient-END
qbds.orderMode(OrderMode::GroupBy);
qbfl = qbds.fields();
qbfl.addField(fieldnum(WMSOrderTrans,Qty),SelectionField::Sum);
qbfl.addField(fieldnum(WMSOrderTrans,RecId),SelectionField::Count);
}
Example init DS with sum
// Example : Init DS for Sum
public void init()
{
QueryBuildDataSource qbds;
SPLPsDate dateTimeSearchMore1;
SPLVersion version;
super();
qbds = this.query().dataSourceTable(tablenum(SPLPsGasNominationDaily));
//add grouping
qbds.addGroupByField(fieldnum(SPLPsGasNominationDaily,DateDay));
//field sum
qbds.addSelectionField(fieldNum(SPLPsGasNominationDaily,OrderedQty),SelectionField::Sum);
qbds.addSelectionField(fieldNum(SPLPsGasNominationDaily,ApprovedQty),SelectionField::Sum);
}
AddSortIndex
//Group also on InventDIm
---->SELECT FIRSTFAST SUM(qty), COUNT(RecId) FROM WMSOrderTrans GROUP BY WMSOrderTrans.shipmentId, WMSOrderTrans.expeditionStatus,
InventDim.InventSiteId, InventDim.InventLocationId, InventDim.wMSLocationId, InventDim.wMSPalletId
JOIN FIRSTFAST * FROM InventDim WHERE WMSOrderTrans.toInventDimId = InventDim.inventDimId
if (_salesLine.ItemId)
{
onlyItemId_CustInvoiceTrans.enabled(true);
onlyItemId_CustInvoiceTrans.value(queryValue(_salesLine.ItemId));
this.query().dataSourceTable(tablenum(CustInvoiceTrans)).addSortIndex(indexnum(CustInvoiceTrans, ItemIdIdx));
this.query().dataSourceTable(tablenum(CustInvoiceJour)).addSortIndex(indexnum(CustInvoiceJour, InvoiceNumIdx));
}
else
{
onlyItemId_CustInvoiceTrans.enabled(false);
this.query().dataSourceTable(tablenum(CustInvoiceTrans)).addSortIndex(indexnum(CustInvoiceTrans, InvoiceIdx));
this.query().dataSourceTable(tablenum(CustInvoiceJour)).addSortIndex(indexnum(CustInvoiceJour, OrderAccountIdx));
}
this.query().dataSourceTable(tablenum(CustInvoiceTrans)).indexIsHint(true);
this.query().dataSourceTable(tablenum(CustInvoiceJour)).indexIsHint(true);
onlyCustAccount_CustInvoiceJour.value(queryValue(_salesTable.CustAccount));
onlyInvoiced_CustInvoiceJour.value('!\'\'');
onlyOrder_CustInvoiceJour.value(enum2Value(RefNum::SalesOrder));
ClearSortIndex - sortClear
qbdsSalesLine.clearSortIndex();
qbdsSalesLine.sortClear();
qbdsSalesLine.addSortField(fieldnum(SalesLine,ItemId));
qbdsSalesLine.addSortField(fieldnum(SalesLine,CustAccount));
Query with 2 DS
// relation(true), I don't need addLink
// relation(false), I need addLink. (The relation is in the second table)
QueryBuildDataSource qbds1,qbds2,qbds3;
qbds1 = this.query().dataSourceTable(tablenum(Address));
//
//addLink (ParentField, Field)
qbds2 = qbds1.addDataSource(tablenum(DirPartyAddressRelationshipMapping));
qbds2.addLink(fieldnum(Address,RecId),fieldnum(DirPartyAddressRelationshipMapping,AddressRecId));
qbds2.relations(true);
qbds2.joinMode(JoinMode::InnerJoin);
//
//addDynaLink (Field, record of ParentTable, ParentField) , use dynalink in form for joinMode = Delay.
qbds3 = qbds2.addDataSource(tablenum(DirPartyAddressRelationship));
qbds3.relations(true);
qbds3.joinMode(JoinMode::InnerJoin);
qbds3.addDynalink(fieldnum(DirPartyAddressRelationship,PartyId),entity,dirFieldId);
//another example of dynalink
queryDataSourceLink = this.query().dataSourceTable(tablenum(CustInvoiceJour)).addDataSource(tablenum(CustInvoiceSalesLink));
queryDataSourceLink.relations(true);
queryDataSourceLink.addDynalink(fieldnum(CustInvoiceSalesLink, OrigSalesId),
element.args().record(),
fieldnum(SalesTable, SalesId));
// JoinMode(JoinMode::InnerJoin)
// joinMode(JoinMode::ExistsJoin)
// joinMode(JoinMode::NoExistsJoin)
Link - dynalink
// Link is used for 2 tables with joinMode = inner join.
// Dynalink is used for 2 tables with joinMode = Delay.
// Because in a form with master details, the lines of details are retreive each time we change a line in the master.
//=============DS-Son.addLink(field-Father,field-Son)==============//
this.query().dataSourceTable(TableNum(InventSum)).addLink(fieldnum(SalesLine,InventDimId), fieldnum(InventSum,InventDimId));
//=============DS-Son.addDynalink(field-Son,common-father,field-Father)==============//
this.query().dataSourceName(this.name()).addDynalink(fieldnum(InventSum,InventDimId),SalesLine,fieldnum(SalesLine,InventDimId));
//link
this.query().dataSourceName(this.name()).clearLinks();
this.query().dataSourceName(this.name()).addLink(fieldnum(InventSum,ItemId), fieldnum(SalesLine,ItemId));
i = this.query().dataSourceName(this.name()).linkCount();
if(!qbds.linkCount())
qbds.addLink(fieldnum(PurchRFQCaseTable, rfqCaseId),fieldnum(PurchRFQVendLink, rfqCaseId));
//dynalink
this.query().dataSourceName(this.name()).clearDynalinks();
this.query().dataSourceName(this.name()).addDynalink(fieldnum(InventSum,ItemId), fieldnum(SalesLine,ItemId));
i = this.query().dataSourceName(this.name()).dynalinkCount(); //Number of dynamics link (from EDT) or from another form
}
Link - Count + delete ?
public void executeQuery()
{
QueryBuildDataSource SPLQbdsInventDim;
QueryBuildLink SPLQblInventDim; //LINK
int iLink;
Boolean SPLLinkExist;
str nodeText;
;
reqTransForm.reqPoDSExecuteQueryPre();
SPLQbdsInventDim = this.query().dataSourceTable(tableNum(InventDim));
SPLLinkExist = false;
for (iLink = 1; iLink "lesser equal" SPLQbdsInventDim.linkCount(); iLink++)
{
SPLQblInventDim = SPLQbdsInventDim.link(iLink);
nodeText = strfmt('%1.%2 == %3.%4', tableid2name(SPLQblInventDim.relatedTable()),
fieldid2name(SPLQblInventDim.relatedTable(),SPLQblInventDim.relatedField()),
tableid2name(SPLQblInventDim.table()),
fieldid2name(SPLQblInventDim.table(),SPLQblInventDim.field()));
info(nodeText);
if (fieldid2name(SPLQblInventDim.table(),SPLQblInventDim.field()) == "SPLPurchMRPBaseSizeId")
{
SPLLinkExist = true;
break;
}
}
SPLQbdsInventDim.clearLinks();
SPLQbdsInventDim.addLink(fieldnum(ReqPo,ConventDimId),fieldnum(InventDim,InventDimId));
if (SPLCtrlPurchReqBySize.checked())
{
if (!SPLLinkExist)
{
SPLQbdsInventDim.addLink(fieldnum(ReqPo,SPLPurchMRPBaseSizeId),fieldnum(InventDim,InventSizeId));
}
}
}
//I don't know if it is working test it ! : qbds.link(1).delete()
querybuilddatasource qbds ;
qbds.link(1).delete();
FetchMode
//Query with T1=T2=T3 we don't need fetchMode
//Query type T1=T2 and T1=T3 we need fetchMode::One2One, if not the T1=T3 not appears in the query
Query query;
QueryBuildDataSource qbds1, qbds2, qbds3;
QueryBuildRange qbr;
;
query = new Query();
qbds1 = query.addDataSource(tablenum(SalesLine));
//dataSource 2
qbds2 = qbds1.addDataSource(tablenum(InventTable));
qbds2.relations(true);
qbds2.joinMode(JoinMode::InnerJoin);
qbds2.fetchMode(QueryFetchMode::One2One); ---------------------
qbr =qbds2.addRange(fieldnum(InventTable, ItemType));
qbr.value(enum2str(ItemType::BOM));
//dataSource 3
qbds3 = qbds1.addDataSource(tablenum(InventDim));
qbds3.relations(true);
qbds3.joinMode(JoinMode::InnerJoin);
qbds3.fetchMode(QueryFetchMode::One2One); ---------------------
//qbr =qbds3.addRange(fieldnum(InventDim, InventSizeId));
//qbr.value("2");
Query with DS inner - outer
If there is several DS ex :
T1=T2 (innerjoin) T2=T3 (innerjoin) and T1=T4 (OuterJoin) -> T4=T5 (OuterJoin -- InnerJoin desn't work)
The rule is that if we have an outerJoin ex T1=T4, the join on T4=T5 must be outerjoin, if not the T1=T4 become an innerJoin in the Query.
Ex InventTable
inner Join EcosResProduct on InventTable.Product = EcosResProduct.Product
outer Join InventTable2 on InventTable2.ItemId = InventTable.alternateId
outer Join EcosResProduct on InventTable2.Product = EcosResProduct.Product // The inner join here doesn't work !
Enable - disable DS (= delete DS)
void init()
{
super();
...
myDS = this.query().dataSourceTable(tableNum(VendTrans)).addDataSource(tableNum(myTable));
myDS.joinMode(JoinMode::ExistsJoin);
myDS.addLink(fieldNum(VendTrans, DocumentNum), fieldNum(myTable, DocumentNum));
myQueryRange = myDS.addRange(fieldnum(myTable, anyField));
myDS.enabled(false); // Disabled until we need it
}
Query
public void init()
{
QueryBuildDataSource qbdsTxt;
;
super();
qbdsTxt = this.query().dataSourceTable(tableNum(PriceDiscTable)).addDataSource(tableNum(InventTxt));
qbdsTxt.joinMode(JoinMode::ExistsJoin);
qbdsTxt.addLink(fieldnum(PriceDiscTable,ItemRelation),fieldnum(InventTxt,ItemId));
qbdsTxt.relations(false);
qbdsTxt.addRange(fieldNum(InventTxt,LanguageId)).value("en-us");
qbdsTxt.addRange(fieldNum(InventTxt,Txt));
qbdsTxt.enabled(false);
}
public boolean modified()
{
boolean ret;
ret = super();
element.SPLAddJoinInventTxt(SPLTxtFilter.text());
priceDiscTable_ds.executeQuery();
return ret;
}
void SPLAddJoinInventTxt(Name _txtFilter)
{
QueryBuildDataSource qbdsTxt;
QueryBuildRange qbrTxt;
;
qbdsTxt = priceDiscTable_ds.query().dataSourceTable(tableNum(InventTxt));
if (_txtFilter)
{
qbdsTxt.enabled(true);
qbrTxt = SysQuery::findOrCreateRange(qbdsTxt,fieldNum(InventTxt,Txt));
qbrTxt.value(_txtFilter);
}
else
qbdsTxt.enabled(false);
}
Query AOT filter
Query with values &&
Query
Filter enum
Filter
Query AOT composite
Composite : If we use another query.
Query composite
Query str
void initQuery()
{
Query query;
;
query = new Query(querystr(ProdMultiSelectSchedulingJob));
queryrun = new SysQueryRun(query);
}
Notes
// EUSalesListTransfer : example run() + example progress Bar.
//WMSOrderTransGroup
Pb entre ExistJoin et notexistJoin -> AX se perd. Solution : Il faut changer le notExistJoin en existJoin
qbds1;
qbds2.joinMode(JoinMode::NotExistsJoin);
qbds3.joinMode(JoinMode::ExistsJoin);