Home > Uncategorized > AX Queries – Passing query range as parameter

AX Queries – Passing query range as parameter

Following is code for paramterized query. ie. query range passed as set of container values:

static FreeText
FindEngineerByTradeArea(NoYes    _skillRestriction = NoYes::No,
MxmServTradeId   _tradeCriteria = “”,
NoYes
_areaRestriction = NoYes::No,

MxmServAreaId     _areaCriteria = “”,
MxmServProficiency     _tradeProficiency =
MxmServProficiency::Primary,
MxmServPreference      _areaPreference   =
MxmServPreference::Primary
)

{
MxmServEngineerTrade
localtrade;
MxmServEngineerArea
localArea;
MxmServEngineer
engineer;

 
Query
qrTrade,qrArea, qrEngineer;
QueryBuildDatasource
qbdsTrade,qbdsArea,qbdsEngineer;
Queryrun
qrnTrade,qrnArea,qrnEngineer;
QueryBuildRange
qbrTrade,qbrEngineer,qbrArea,qbrTradeProficiency,qbrAreaPreference;
container
conEngid;
int
i=1;
FreeText
EngList;

;

//Trades  — Get requested trade
from Trade

 

if (_skillRestriction == NoYes::Yes
&& _areaRestriction == NoYes::Yes && _tradeProficiency
&& _areaPreference )   // If both restriction  i.e.Trade
and Area

{

qrTrade = new
Query();

qbdsTrade =
qrTrade.addDataSource(tableNum(MxmServEngineerTrade));
qbrTrade   =
qbdsTrade.addRange(fieldNum(MxmServEngineerTrade,TradeId));
qbrTrade.value(queryValue(_tradeCriteria));

//Adding
Preference for Proficiency
qbrTradeProficiency =
qbdsTrade.addRange(fieldNum(MxmServEngineerTrade,Proficiency));
qbrTradeProficiency.value(queryValue(_tradeProficiency));

 

qrnTrade = new
QueryRun(qrTrade);

 
while(qrnTrade.next())

{
localtrade = qrnTrade.get(tablenum(MxmServEngineerTrade));

 
if(localTrade.EngId)
{
if(!confind(conEngId,localTrade.EngId))
{
conEngid = conins(conEngid,conlen(conEngId)+1,localTrade.EngId);
}
}

}

 

for (i=1;
i<=conlen(conEngId); i++)

{
EngList +=  conpeek(conEngid,i)+ ‘,’;

}

 

EngList =
substr(EngList,1,strlen(EngList)-1);

conEngId =
conNull();

 

qrArea = new
Query();

qbdsArea =
qrArea.addDataSource(tableNum(MxmServEngineerArea));

 

if(EngList)

{
qbrEngineer = qbdsArea.addRange(fieldNum(MxmServEngineerArea,EngId));
qbrEngineer.value(EngList);

 
if(_areaCriteria)
{
qbrArea = qbdsArea.addRange(fieldNum(MxmServEngineerArea,AreaId));
qbrArea.value(_areaCriteria);
}
//Adding preference for Area
if(_areaPreference)
{
qbrAreaPreference = qbdsArea.addRange(fieldNum(MxmServEngineerArea,Preference));
qbrAreaPreference.value(queryValue(_areaPreference));
}

 
qrnArea = new QueryRun(qrArea);

 
while(qrnArea.next())
{
localArea = qrnArea.get(tableNum(MxmServEngineerArea));
conEngId    =
conins(conEngId,conlen(conEngId)+1,localArea.EngId);
}

}

else
conEngId = conNull();

}

else if(_skillRestriction == NoYes::Yes
&& _areaRestriction == NoYes::No &&
_areaPreference)   // If Only Trade restriction

{

qrTrade = new
Query();

qbdsTrade =
qrTrade.addDataSource(tableNum(MxmServEngineerTrade));

 

qbrTrade=
qbdsTrade.addRange(fieldnum(MxmServEngineerTrade,TradeId));
qbrTrade.value(queryValue(_tradeCriteria));

 

//Adding
Preference for Proficiency
qbrTradeProficiency =
qbdsTrade.addRange(fieldNum(MxmServEngineerTrade,Proficiency));
qbrTradeProficiency.value(queryValue(_tradeProficiency));

 

qrnTrade = new
QueryRun(qrTrade);

 
while(qrnTrade.next())

{
localTrade = qrnTrade.get(tablenum(MxmServEngineerTrade));
conEngId = conins(conEngId,conlen(conEngId)+1,localTrade.EngId);

}

}

else if(_skillRestriction == NoYes::No
&& _areaRestriction == NoYes::Yes)   // If Only area
restrction

{

qrArea = new
Query();

qbdsArea =
qrArea.addDataSource(tableNum(MxmServEngineerArea));

 

qbrArea =
qbdsArea.addRange(fieldNUm(MxmServEngineerArea,AreaId));
qbrArea.value(_areaCriteria);

//Adding preference for
Area
if(_areaPreference)

{
qbrAreaPreference = qbdsArea.addRange(fieldNum(MxmServEngineerArea,Preference));
qbrAreaPreference.value(queryValue(_areaPreference));

}

qrnArea = new
QueryRun(qrArea);

 
while(qrnArea.next())

{
localArea = qrnArea.get(tablenum(MxmServEngineerArea));
conEngId = conins(conEngId,conlen(conEngId)+1,localArea.EngId);

}

}

else if(_skillRestriction == NoYes::No
&& _areaRestriction ==
NoYes::No)         // If NO restriction

{

qrEngineer = new
Query();

 

qbdsEngineer =
qrEngineer.addDataSource(tableNum(MxmServEngineer));

 

qrnEngineer = new
QueryRun(qrEngineer);

 
while(qrnEngineer.next())

{
engineer = qrnEngineer.get(tablenum(MxmServEngineer));
conEngId = conins(conEngId,conlen(conEngId)+1,engineer.EngineerId);

}

}

 

EngList = ”;

if(conEngId)

{
for(i=1;i<=conlen(ConEngId);i++)

{
EngList +=  conpeek(conEngid,i)+ ‘,’;

}

EngList = substr(EngList,1,strlen(EngList)-1);

}

 

return EngList;

 

}

 

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: