AX 2009 - .Net business connector with a SQL statement with several joins

Finance and Operations/AX Developer Forum
1

I am new to AX. I have done a few simple things using C# and the .Net business connector. Now I want to fetch some data from AX using a SQL statement with several joins. But I don’t know how to do this. ALl the examples I find only use one table. Not sure how to tell AX that I am using 5 tables. Here is what I have so far:

Axapta

ax = new Axapta();

AxaptaRecord axRecord;

string tableName = “InventTable”;

ax.Logon(

“”, “”, “”, “”);

string strSQL = @“SELECT DISTINCT ITEMNAME, PT.ATTRIBUTEDESC As PackageType, PS.ATTRIBUTEDESC As PackageSize, PD.Amount AS UnitPrice, UC.Factor AS CasesPerPallet FROM INVENTTABLE IT LEFT JOIN WFSPACKAGETYPE PT ON IT.WFSPACKAGETYPEID == PT.PACKAGETYPEID && IT.DATAAREAID == PT.DATAAREAID LEFT JOIN WFSPACKSIZE PS ON IT.WFSPACKSIZEID == PS.PACKSIZEID && IT.DATAAREAID == PS.DATAAREAID LEFT JOIN PRICEDISCTABLE PD ON IT.ITEMID == PD.ItemRelation && IT.DATAAREAID == PD.DATAAREAID LEFT JOIN UNITCONVERT UC ON IT.BOMUNITID == UC.FROMUNIT && IT.ITEMID == UC.ITEMID && IT.DATAAREAID == UC.DATAAREAID WHERE IT.DATAAREAID == '” + company + “’” + " && IT.ITEMID == ‘" + ddlItem.SelectedValue + "’" + " && PD.AccountRelation == ‘" + Convert.ToString(Session[“CustAcct”]) + "’";

DataRow drItem = dtItems.NewRow();

using (axRecord = ax.CreateAxaptaRecord(tableName))

{

axRecord.ExecuteStmt(strSQL);

DataRow drItem = dtItems.NewRow();

drItem[

“ItemName”] =

Convert.ToString(axRecord.get_Field(“ItemName”));

drItem[

“PackageSize”] =

Convert.ToString(axRecord.get_Field(“PackageSize”));

drItem[“PackageType”] = Convert.ToString(axRecord.get_Field(“PackageType”));

}

I have more fields but I simplified it. When I run this, I get an error on the ExecuteStmt. The error is:

Unable to compile

My first question is how do I specify more than one table? I specify my main table in a string variable like this:

string tableName = “InventTable”;

I then create the AX record using this line"

using (axRecord = ax.CreateAxaptaRecord(tableName))

But how do I define my other 4 tables that I am joining?

Can anyone give me an example that shows how to do this?

Thanks,

Bob

2

Use ax.ExecuteStmt(strSQL, axRecord1, axRecord2), for example.

But you fundamentally misunderstood what code you can use in ExecuteStmt() method, i.e. what you should have in your strSQL variable. ExecuteStmt() expects valid X++ statement, not T-SQL or anything else.

3

The problem is I don’t know X++. You helped me the other day with another SQL statement by telling me I needed the “&&” instead of “AND” and “==” instead of “=”. I thought I did this in my SQL statement?

Also, I’m confused about this:

ax.ExecuteStmt(strSql, axRecord1, axRecord2)

Can you provide an example or send me a link of where this is used? I can’t seem to get this to work.

4

Again, ExecuteStmt expects X++ code, it won’t work with SQL. It just looks similar. If you thought you can avoid learning X++ in this way, you were wrong. The best thing you can do is to write as much as possible in X++ and just call proper methods from Business Connector, but at least write the code in AX before using it in BC. Business Connector doesn’t offer anything to debug X++ code.

This is an example how to use joined tables:

Axapta ax = new Axapta();
ax.Logon(null, null, null, null);

using (AxaptaRecord users = ax.CreateAxaptaRecord("UserInfo"),
    groups = ax.CreateAxaptaRecord("UserGroupList"))
{
    ax.ExecuteStmt("select * from %1 join %2 where %1.id == %2.userId", users, groups);

    while (users.Found)
    {
        Console.WriteLine("{0} ({1})", users.get_Field("id"), groups.get_Field("groupId")); 
        users.Next();
    }
}

ExecuteStmt() method is documented on MSDN - just ignore the example there, it’s wrong.

5

Ok, I will take your advice and build some mthods in AX using X++.

Thanks

6

Ok, I will take your advice and build some methods in AX using X++. Thanks