How to convert fBQL or BQL to SQL statement while debugging in Visual Studio

Thank you @MoulaliShaik79, that documentation helped me come up with the required code.

So here’s the updated code that I have used and can see the strSQL while debugging

var strSQL = 
	SelectFrom<CSAnswers>
	.InnerJoin<InventoryItem>
		.On<InventoryItem.noteID.IsEqual<CSAnswers.refNoteID>>
	.Where<CSAnswers.attributeID.IsEqual<Use<slots>.AsString>
		.And<InventoryItem.inventoryID.IsEqual<@P.AsInt>>>
	.View.GetCommand().GetQuery(Base).SQLQuery(Base.SqlDialect.GetConnection());  

While I was debugging using the above technique/code, I noticed that the query doesn’t includes the filter for CompanyID in Where, Join and Group By clauses. Any ideas how I can make Acumatica give me the SQL query after it adds the CompanyID to the SQL Query Tree?

Hi @kashif78,

The Company ID column is used for Multi-Tenet support purposes. We can not find it in our DAC Fields.

Please check the below link to get some information about the Company ID column.

https://help-2021r2.acumatica.com/(W(16))/Help?ScreenId=ShowWiki&pageid=d0945e20-1949-40b1-bd0f-92c7c432aa24

Note: Till now I didn’t use the Company ID column in FBQL or BQL queries.

Thanks,

Moulali Shaik.

Yes, I’m aware that we don’t add a CmpanyID field in our DAC. But Acumatica does add the filters for CmpanyID when running a query against the SQL DB. I wanted to see what all places in the query it adds the CompanyID filter

PS: My dev environment is a multi-tenant one having around 5 companies. 

Hi All

Recent versions of Acumatica add an option under tools to trace SQL statements:

When you stop the logging a zip file is generated for downloading

You can then see all the add-ons Acumatica includes in your simple (F)BQL query.

Also you can use MS SQL Profiler if you want to see it coming as it happens (Enjoy the cache as not all the time those queries are triggered)

Another option that you have is you can wire up a SQL profiler as to see the end result of what gets sent to SQL server.

Leonardo Justiniano wrote:

Hi All

Recent versions of Acumatica add an option under tools to trace SQL statements:

 

 

When you stop the logging a zip file is generated for downloading

 

You can then see all the add-ons Acumatica includes in your simple (F)BQL query.

Also you can use MS SQL Profiler if you want to see it coming as it happens (Enjoy the cache as not all the time those queries are triggered)

Thanks for the tip @Leonardo Justiniano