Dynamic queries

 

Sample Dynamic queries methods

static void Con_Dynamics(Args _args) 

{ 

Query q; 

QueryRun qr; 

QueryBuildDataSource qbd; 

CustTable custTable; 

 

q= new Query(); 

qbd=q.addDataSource(tablednum(custTable)); 

qbd=addSortfield(fieldnum(CustTable,AccountNum),SortOrder::Ascending) 

qr=new QueryRun(q) 

while(qr.next()) 

{ 

custTable=qr.get(Tablenum(custTable)); 

info(strfmt("%",custTable.AccountNum)); 

} 

} 

 

 

static Void Con_Dynamics(Args _args) 

{ 

 

Query q; 

QueryBuildDataSource qbd; 

QueryRun qr; 

CustTable custTable; 

q=new Query(); 

qbd=q.addDatasource(tablenum(custTable); 

qbd=addsortfield(fieldnum(CustTable,Accountnum),Sortorder::Descending) 

qr=new QueryRun(q) 

while (qr.next()) 

{ 

 

custTable=qr.get(tablenum(custTable)); 

info("%1",custTable.Accountnum); 

} 

} 

 

=== To select customers Between Range ==== 

 

static void Con_betweenRange(Args _args) 

{ 

Query q; 

QueryRun qr; 

QueryBuildDataSource qbd; 

QueryBulidRange qbr; 

CustTable custTable; 

 

q=new Query(); 

qbd=q.addDatasource(tablenum(custTable)); 

qbd-=addSortfield(Fieldnum(custTable,AccountNum),Sortorder::Descending) 

qbr=qbd.addRange(fieldnum(custTable,AccountNum) 

qbr.value(QueryRange("01","12")); 

qr=new Query(r); 

while (qr.next()) 

{ 

custTable=q.get(tablenum(custTable)) 

info("%1",custTable.AccountNum) 

} 

 

 

Static Void Con_BetweenRangeAsc(Args _args) 

{ 

Query q; 

QueryBuildDataSource qbd; 

QueryRun qr; 

QueryBuildRange qbr; 

CustTable custTable; 

 

q=new Query(); 

qbd=q.addDataSource(Tablenum(custTable)); 

qbd.addSortField(fieldnum(custTable,Accountnum),Sortoder::Ascending) 

qbr=addRange(Fieldnum(custTable,AccountNum); 

qbr.value(QueryRange("01","12"); 

qr=new Query(q); 

while(qr.next()) 

{ 

custTable=qr.get(tablenum(custTable); 

 

info("%1",custTable.Accountnum) 

} 

 

} 

 

===Where Condition=== 

 

Static Void Wherecon(Args _args) 

{ 

Query q; 

QueryRun qr; 

QueryBuildDataSource qbd; 

QueryBuildRange qbr; 

CustTable custTable; 

q=new Query(); 

qbd=q.addDataSource(Tablenum(custTable)) 

qbd.addsortField(fieldnum(custTable, CustGroup),Sortorder::Ascending); 

qbr=qbd.addRange(fieldnum(custTable,CustGrop); 

qbr.valuer(QueryRange("01","10"); 

qr=new Query(q); 

while (qr.next()) 

{ 

custTable=qr.get(tablenum(custTable)) 

info("%1",custTable.CustGroup); 

 

} 

} 

 

==Cross Company=== 

 

Static Void CrossCompany(Args _Args) 

{ 

Query q; 

QueryBuildDataSource qbd; 

QueryRun qr; 

CustTable custTable; 

q=new Query(); 

q.allowcrossCompany(true); 

qbd=q.addDataSource(tablenum(CustTable)); 

qr= new QueryRun(q); 

while(qr.next()) 

{ 

custTable=qr.get(tablenum(custTable)); 

info("%1-%2",custTable.AccountNum,custTable.AreaId); 

} 

} 

==Aggregate Functions== 

sum 

avg 

count 

min 

max 

 

Static Void AggregatFun(Args _args) 

{ 

Query q; 

QueryBuildDataSource Qbd; 

QueryRun qr; 

CustTable custTable; 

VendTable vendtable; 

 

q= new Query(); 

qbd=q.addDataSource(tablenum(vendTable)); 

qbd.addselectionField(fieldnum(vendTable, PurchAmt),Selectionfield::sum) 

qbd.addSelectionfield(fieldnum(vendTable,Recid),SelectionField::Count)  

qr=new QueryRun(q); 

While(qr.next()) 

{ 

vendTable= qr.get(tablenum(vendTable)) 

info(strfmt("%1---%2",vendTable.purchAmt,vendtable.RectId); 

} 

 

} 

 

max,min 

 

Static Void AggregatFun(Args _agrs) 

{ 

Query q; 

QueryBuildDatasource qbd; 

QueryRun qr; 

CustTable custTable; 

q=new Query(); 

qbd=q.addDataSoruce(tablenum(custTable)) 

qbd.addSelectionField(fieldnum(custTable,AccoustMst),SelectionField::Avg); 

qbd.addSelectionField(fieldnum(custTable.Rectid),SelectionField::min); 

qr= new QueryRun(q); 

while(qr.next()) 

{ 

custTable=qr.get(tablenum(custTable)) 

inf("%1 %2",CustTable.Accounstmst,CustTable.RectId) 

}} 

 

===Firstonly,FistFast=== 

 

Static void Firstonly(Args _args) 

{ 

Query q; 

QueryBuildDataSource qbd; 

QueryRun qr; 

CustTable custTable; 

q= new Query(); 

qbd=q.addDatsource(Tablenum(custTable)); 

qbd.addSortfield(fieldnum(custTable.Accountnum),sortorder::Ascending); 

qbd.firstonly(true); 

qr= new QueryRun(q); 

while (qr.next()) 

{ 

custTable=qr.get(tablenum(custTable)); 

info(strfmt("%1",CustTable.AccountNum)); 

 

} 

 

} 

---firstfast-- 

 

Static Void FirstFast(Args _args); 

{ 

 

Query q; 

QueryBuildDatasource qbd; 

QueryRun qr; 

CustTable custTable; 

} 

q = new Query(); 

qbd=q.addDataSource(Tablenum(custTable)) 

qbd.addsortfield(fieldnum(custTable,Accountnum),Sortorder::ascending); 

qbd.firstfast(true); 

qr= new QueryRun(q); 

while(qr.next()) 

{ 

custTable=qr.get(tablenum(custTable)); 

info(strfmt("%1",custTable.accountnum)); 

} 

} 

====ForUpdate==== 

 

Static Void ForUpdate(Args _args) 

{ 

Query q; 

QueryBuildDatasource qbd; 

QueryRun qr; 

Con_Cal obj; 

q= new Query(); 

qbd= q.addDataSource(tablenum(con_cal)) 

qbd.addSortfield(fieldnum(con_cal,name) 

qr= new QueryRun(q); 

while (qr.next()) 

{ 

obj=qr.get(tablenum(con_cal); 

obj.selectForupdate(true); 

ttsbegin; 

 

if(obj.name="siva") 

{ 

obj.name="sivaramkrishna"; 

obj.update(); 

} 

 

ttscommit; 

info(strfmt("%1",obj.name)); 

} 

 

} 

 

===Group by==== 

Static void GroupBy(Args _args) 

{ 

Query q; 

QueryBuildDataSource qbd; 

QueryRun qr 

CustTable custTable; 

 

q= new Query(); 

qbd=q.addDatasource(tablenum(custTable); 

qbd.addSelectionField(fieldnum(custTable ,Amountmst),SelectionField::max) 

qbd.addGroupbyField(fieldnum(custTable,Accountnum),orderMode::Groupby); 

qr= new QueryRun(q); 

while (qr.next()) 

{ 

 

custTable=qr.get(tablenum(custTable)); 

info(strfmt("%1--%2", custTable.Accountnum,custTable.AmounMst)); 

} 

} 

 

=== joins=== 

 

Static Void joinMode(Args _args) 

{ 

Query q; 

QuerybuildDataSource bdscust,bdstran; 

QueryRun qr; 

 

CustTable car; 

CustTran rent; 

q=new Query(); 

qbdcust=q.addDataSource(tablenum(custTable)); 

qbdtran=q.addDataSource(tablenum(custTran)); 

qbdtran.relation(flase); 

qbdtran.joinmode(Joinmode::innerjoin)\ 

qbdtran.addlink(fieldnum(con_car.cardId),fieldnum(con_rent.CarId)) 

qr =new QueryRun(q); 

while(qr.next()) 

{ 

car=qr.get(tablenum(custTable); 

rent=qr.get(tablenum(custTran); 

info(strfmt("%1--%2",car.carid,Rent.Carid)); 

 

} 

 

}