How write a dynamic select statement in X++

(0) Share

Report

Posted on by Community Member

Hi All,

I have a requirement to write my select statement based on one table. The table is as below.

Customer	Customer Name	Where clause	Table	Field
13441	TMU	SalesId	SalesTable	CustOrderNumber
11996	TRU	SalesId	SalesTable	EDI_ShipToLocation
123456	ADT	LineNum	SalesLine	LineNum

So if the customer is "13441" I need to use the table as "SalesTable and Field as "CustOrderNumber and in where clause need to use"SalesId".

So my select statement will change based on customer account every time.

Is there anyway I can achieve it and can build my select statement ?

Regards

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Hi,

Could you explain what does field mean in your table, is it the fieldlist you are referring to. You can check code below and let me know if anything is missing.

SalesTable salesTable; SalesLine salesLine; Query query; QueryRun queryRun; Querybuilddatasource qbds, qbdsLine; QuerybuildRange qbr; AccountNum c1; c1 = "13441"; Query = new Query(); qbds = query.addDatasource(tablenum(SalesTable)); qbdsLine = qbds.addDataSource(tablenum(SalesLine)); qbdsLine.relations(true); if (c1 == "13441") { qbr = SysQuery::findOrCreateRange(qbds, (fieldnum(SalesTable, SalesId))); qbr.value(queryvalue("XYZ1")); } else if (c1 == "11996") { qbr = SysQuery::findOrCreateRange(qbds, (fieldnum(SalesTable, SalesId))); qbr.value(queryvalue("XYZ2")); } else if (c1 == "123456") { qbr = SysQuery::findOrCreateRange(qbdsLine, (fieldnum(SalesLine, LineNum))); qbr.value(queryvalue("XYZ3")); } QueryRun = new QueryRun(query); while (queryRun.next()) { salesTable = queryRun.get(tablenum(SalesTable)); salesLine = queryRun.get(tablenum(SalesLine)); info(strfmt("SalesId: %1", SalesTable.SalesId)); info(strfmt("LotId: %1", salesLine.InventTransId)); }

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Sukrut,

I am sorry if I was not able to explain it properly.

Ok, so the requirement is, in above table (which is basically a configuration table) we have all the customer Id, name, table name id field name.

I will get the customer Id from external source and will match from this table, if customer Id exists, I need to create a select statement where I need to use the above table and fetch the data from the "SalesTable"; in this case for customer 13441. But my It team can change the value of Table "SalesTable" to "xyzTable" so basically I need to use run time / dynamic select statement.

Was this reply helpful? Yes No
Dauvis 170 on at

Like (0)

Report

If I am understanding that requirement correctly, you should be able to create a query using QueryBuildDataSource with just the table you need and your where clause field would specify the range you add to it (studying Chaitanya's example will teach you what you need to know). To get the field from the record that the QueryRun returns, you will have figure out what the field Id is. Once you have the field Id, you can get its value like this.

int fieldId = ... // your field Id

Common tableRecord = ... // the record you get from the QueryRun

anytype value; // your value

value = tableRecord.(fieldId);

Was this reply helpful? Yes No
Suggested answer

Andrew Xu 3,877 on at

Like (0)

Report

Mukti,

Here's 1 solution should work.

1. Build a T-SQL statement based on your table.

2. Execute that statement with class Statement.

https://msdn.microsoft.com/en-us/library/statement.executequery.aspx

3. Iterate the result set to get your data.

Was this reply helpful? Yes No