SQL State

Dynamics 365 for Finance and Operations/AX Developer Forum
#1

Hi. I have a method in data source.

public void executeQuery()
{
Query purchLineQuery;
QueryBuildDataSource myDataSource;

;
super();
purchLineQuery = PurchTable_ds.query();
purchLineQuery.dataSourcetable(tableNum(PurchTable));
purchLineQuery.addDataSource(tableNum(purchLine));
myDataSource = purchLineQuery.dataSourceTable(tableNum(PurchTable));
myDataSource.addDataSource(tablenum(PurchLine));

myDataSource.joinMode(joinMode::ExistsJoin);

myDataSource.addDynaLink(fieldnum(PurchLine, PurchId),purchtable,fieldnum(PurchLine,PurchId));

info(myDataSource.toString());

}

the info print

SELECT FIRSTFAST * FROM PurchTable WHERE PurchTable.PurchId=PurchTable.PurchId AND PurchTable.PurchId=PurchTable.PurchId JOIN FIRSTFAST * FROM PurchLine JOIN FIRSTFAST * FROM PurchLine

I want to something else.

I want that the sql state will be

SELECT * FROM PurchTable,PurchLine WHERE PurchTabel.PurchId=PurchLine.PurchId AND WHERE PurchLine.ItemId =“00022”.

How can I modify the x++ code ??

#2

Your X++ code doesn’t resemble your expected result. You added PurchLine twice, didn’t add any filter for ItemId and so on.

This is how the code should look like - just adjust it for your specific situation:

Query query = new Query();
QueryBuildDataSource header = query.addDataSource(tableNum(PurchTable));
QueryBuildDataSource line = header.addDataSource(tableNum(PurchLine));
line.relations(true);
line.addRange(fieldNum(PurchLine, ItemId))
        .value(queryValue('00022'));
    
info(header.toString());
#3

Thanks Martin,

I modify a litle yout code, and in info show my what i want.

public void executeQuery()
{

Query query = purchtable_ds.query();
QueryBuildDataSource header = query.addDataSource(tableNum(PurchTable));
QueryBuildDataSource lineee = header.addDataSource(tableNum(PurchLine));

lineee.relations(true);
lineee.addRange(fieldNum(PurchLine, ItemId)).value(queryValue(‘00022’));
info(header.toString());

}

NOw i want to put the result to the grid. How can i do it??

#4

Why do you add a new PurchTable data source to purchTable_ds? Do you really want two data sources for PurchTable? Wait, not only two - a new one would be added every time when the query is executed.

If you want to display data in grid, I suppose it’s all about a form. Then you can add all data sources to the Data Source node. Set the following properties on PurchLine data source:

  • JoinSource: PurchTable
  • LinkType: InnerJoin (if you want the query as you described above)

The only code you need is the filter for ItemId, nevertheless be careful not adding it multiple times.

It would help if you described what you’re trying to do from a more general perspective. Fine-tuning a piece of code that shouldn’t have been written is just waste of time.

#5

Hi, I almost done.

I have this code in form methods:

public void executeQuery()
{

QueryRun qr;

Query query = purchtable_ds.query();

QueryBuildDataSource header = query.addDataSource(tableNum(PurchTable));
QueryBuildDataSource lineee = header.addDataSource(tableNum(PurchLine));
lineee.relations(true);
lineee.addRange(fieldNum(PurchLine, ItemId)).value(“00026”);

query.clearBaseQueries();
qr = new QueryRun(query);

while(qr.next())
{
purchTable = qr.get(tablenum(purchtable));
info(purchTable.PurchId);
}

info(header.toString()); // <-------------HERE 1
super();
info(this.query().dataSourceNo(1).toString()); <-------------HERE 2

}

And it’s ok, but my base query don’t change.

WHERE IS “HERE 1” the query is OK, but where is “HERE 2” my info log back to the base query.

How can I change it??