Microsoft Dynamics AX (Archived)

writing pure SQL query in AQT table browser

(0) Share

Report

Posted on by maram fraij

Is there a way to write pure SQL query in AQT table browser not X++ to retrieve data from table ?

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (1)

Verified answer

Martin Dráb 237,878 Most Valuable Professional on at

Like (0)

Report

No, it's interpreted as X++. If you want a pure SQL query, use SQL Server Management Studio.

Was this reply helpful? Yes No
Suggested answer

Tommy Skaue Moderator on at

Like (1)

Report

Martins answer is correct. The query box in the table browser is cumbersome to use as it does not provide any help like intellisense or error handling. If you mess up, it will just fail.

If you are a fluent SQL programmer, you might want to try understand how X++ queries are represented and how they relate to the actual runned SQL. In the long run you are much better off. :-)

Was this reply helpful? Yes No
Gertjan 1,200 on at

Like (2)

Report

The options of the query field in the AOT table browser are limited and as far as I know it is not possible to write pure SQL queries. However, you can add count, sum and other SQL comparable statements. A good example is using 'select count(recid) from table to get a total table count. For what reason do you need to write pure SQL queries?

Was this reply helpful? Yes No
Daniel Weichsel on at

Like (0)

Report

The table browser query window is still powerful because you can declare additional buffers to join with the primary buffer. The utility is pretty much limited to exists and notexists joins though, since you only get the primary table on the grid, but it is still possible to create complex selections.

You also can execute almost any X++ code entered there as well -- use with care! :)

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

No pure SQL query is not allowed on table browser form.

X++ SELECT statement will work.

Was this reply helpful? Yes No
Muhammad Yousaf 1,339 on at

Like (0)

Report

OK, then i am in table browser inventtransfertable and wanted to change a warehouse to a different warehouse... if i am not able to do in table browser then what are the steps....

Was this reply helpful? Yes No
UmesH@ 810 on at

Like (0)

Report
A_PO po; select generateonly forceliterals po where po.PO_ID=="PO_001"; info(po.getSQLStatement());

Try This You Will Get Query But Not Pure SqlQuery.
Might be Help This.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Muhammad,

I'm not sure if this is what you're trying to do or not, but if you're already looking at your data in the table browser "grid", you can change values directly in the grid, a lot like a spreadsheet. No need to write update or insert statements.

Was this reply helpful? Yes No
Daniele Ferraretto on at

Like (0)

Report

Hi Muhammad, your goal I supposed isn't only a select but update the field of a specific inventtransfertable record, isn't it?

If I supposed correctly you could try to do this with a X++ job. like

[...]

select forupdate inventtransfertable where inventtransfertable.transferid == '<your searchvalue>

if(inventtransfertable.recid)

{

inventtransfertable.selectforupdate(true);

inventtransfertable.inventlocationto = '<your value>';

ttsbegin;

inventtransfertable.update();

ttscommit;

}

[...]

pay attention to check that the transfer order line and relative transaction are related to the new warehouse.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Daniele,

Your response here actually closely answers a very basic need I'm trying to accomplish...

Does this look correct to change those lines which match the criteria to bom.linenum = 15, and what does the line

"if(bom.recid)" (yours was "if(inventtransfertable.recid)") do?

Thanks in advance.

[...]

select forupdate bom where bom.itemid like "LBL*" && bom.linenum == 1.0000000000

if(bom.recid)

{

bom.selectforupdate(true);

bom.linenum = '15.0000000000';

ttsbegin;

bom.update();

ttscommit;

}

[...]

Was this reply helpful? Yes No