Easy way to write queries/select statements – Editor scripts

Editor scripts help us to perform frequent tasks more quickly in the X++ code editor. For example, there are scripts to create templates for certain types of code constructs and methods, to convert lines of code into comments.

This post will help the developers to quickly write the joins based on the relations between table. Tired of writing code or new to Joins, please follow the code below

To use a script:

Place your cursor in the code editor window, and then press Alt+M.

Press Alt+M, and then Queries > InnerJoin. [Note : In this post , I have added only 4 joins, Many more to come]

Overview :

Select the inner join or any join as per your need.

Once you select InnerJoin from the editorscripts, a dialog will be opened and will request you add the master and childTable. For example , provide SalesTable and salesLine as shown below

Click on Ok button in the dialog
Below is the output.. Isn’t easy ??? 🙂

Well, now lets look at the code. Add the below five methods to your “EditorScripts” class. This class is available in your AOT >> Classes >> EditorScripts


Method1
_______
public void Queries_ExistsJoin(Editor e)
{
Dialog dialog = new Dialog(“Query helper”);
DialogField dfMasterField, dfChilField;
;

dfMasterField = dialog.addField(Types::String, “Parent table”);
dfChilField = dialog.addField(Types::String, “Child table”);

if (dialog.run())
{
e.insertLines(EditorScripts::buildQueryStatement(‘exists join’, dfMasterField.value(), dfChilField.value()));
}
}

Method2
_______
public void Queries_InnerJoin(Editor e)
{
Dialog dialog = new Dialog(“Query helper”);
DialogField dfMasterField, dfChilField;
;

dfMasterField = dialog.addField(Types::String, “Parent table”);
dfChilField = dialog.addField(Types::String, “Child table”);

if (dialog.run())
{

e.insertLines(EditorScripts::buildQueryStatement(‘join’, dfMasterField.value(), dfChilField.value()));
}
}

Method3
_______
public void Queries_NonExistsJoin(Editor e)
{
Dialog dialog = new Dialog(“Query helper”);
DialogField dfMasterField, dfChilField;
;

dfMasterField = dialog.addField(Types::String, “Parent table”);
dfChilField = dialog.addField(Types::String, “Child table”);

if (dialog.run())
{

e.insertLines(EditorScripts::buildQueryStatement(‘notexists join’, dfMasterField.value(), dfChilField.value()));
}
}

Method4
_______
public void Queries_OuterJoin(Editor e)
{
Dialog dialog = new Dialog(“Query helper”);
DialogField dfMasterField, dfChilField;
;

dfMasterField = dialog.addField(Types::String, “Parent table”);
dfChilField = dialog.addField(Types::String, “Child table”);

if (dialog.run())
{

e.insertLines(EditorScripts::buildQueryStatement(‘outer join’, dfMasterField.value(), dfChilField.value()));
}
}

Method5
_______

static str buildQueryStatement(str _joinType, TableName _masterTable, TableName _childTable)
{
DictRelation dictRelation;
Counter i;
str selectStatement;
DictField CMainDictField,CLineDictField;
str 1 firstAlphabet;
sqlDictionary s1, s2;

;

select * from s1 where s1.TabId == TableName2id(_masterTable);
select * from s2 where s2.TabId == TableName2id(_childTable);

if ((!s1) || (!s2))
{
throw error (“Table names are invalid.”);
}

if (_masterTable == _childTable)
{
throw error (“Master and child tables cannot be identical”);
}

firstAlphabet = strlwr(substr(_masterTable, 1,1));
_masterTable = strdel(_masterTable,1,1);
_masterTable = firstAlphabet + _masterTable;

firstAlphabet = ”;

firstAlphabet = strlwr(substr(_childTable, 1,1));
_childTable = strdel(_childTable,1,1);
_childTable = firstAlphabet + _childTable;

dictRelation = new DictRelation(tablename2id(_childTable));

dictRelation.loadTableRelation(tablename2id(_masterTable));

if (dictRelation.lines())
{
selectStatement += ‘ ‘+ str2capital(_masterTable) + ‘ ‘ + _masterTable +’; \n’;
selectStatement += ‘ ‘+ str2capital(_childTable) + ‘ ‘ + _childTable + ‘; \n’;

selectStatement += ‘ ;\n’;
selectStatement += ‘ while select * from ‘ + _masterTable + ‘ ‘+_joinType +’ ‘+ _childTable + ‘ where ‘;
for (i = 1; i <= dictRelation.lines(); i++)
{
CMainDictField = new DictField(tablename2id(_masterTable), dictRelation.lineExternTableValue(i));
CLineDictField = new DictField(tablename2id(_childTable), dictRelation.lineTableValue(i));
selectStatement += _masterTable + ‘.’ + CMainDictField.name() + ‘ == ‘ + _childTable +’.’+ CLineDictField.name() + ‘\n && ‘;
}
}
else
throw error(strfmt("@SYS59540",_masterTable, (_childTable)));

selectstatement = strdel(selectstatement, strlen(selectStatement), -3 )+'\n';
selectstatement += ' {\n';
selectstatement += @' //' + 'TODO::Business logic goes here\n';
selectstatement += ' }';

return selectstatement;
}

Happy Dax’ng 🙂

Advertisements

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: