Microsoft Dynamics AX (Archived)

Can we join View with temp table?

(0) Share

Report

Posted on by Khurshid Wali

928

Hi everyone,

I have a lookup form where I want to add range to the the querybuilddatasource.

this.queryBuildDataSource().addRange(fieldNum(ABCHRMEmployeeLookup,PositionId)).
value(HcmPosition::find(hcmPositionHierarchy.Position).PositionId);

this line is working fine with few Records but with 996 records it give this error.

4645.Capture11.JPG

Then I decided to keep the 996 records in the tempDB table and try to create join with my data source

which is a view of the query.

I wrote this code to join with my current datasource.

0841.123.JPG

Then I got this Error

Please help me how I can solve the first and second problem.

P1: How to set multiple ranges without buffer overflow?

P2: How to Join Temp table with this data source? (datasource is AOT View of AOT query)

For Problem 2 I tried this suggestion. But I can't be able to use 'this. query().'

community.dynamics.com/.../the-data-source-is-not-embedded-within-a-parent-data-source

*This post is locked for comments

I have the same question (0)

All responses (16)

Answers (3)

Verified answer

feelyd 260 on at

Like (0)

Report

Your first issue I have come across before , it relates to to the maximum buffer size set in the database tuning tab of the server configuration utility.

This buffer is used for binding of input and output parameters for SQL statements.

You can try setting this to a larger size.

Was this reply helpful? Yes No
Verified answer

Mea_ 60,284 on at

Like (0)

Report

for second error create new variable:

QueryBuildDataSource qbds = this.queryBuildDatasource().addDatasource()tableNum...)

qbds.joinMode...

qbds.addLink...

Was this reply helpful? Yes No
Khurshid Wali 928 on at

Like (0)

Report

Thank you Feely and Miroshnikov for your valueable reply,

Feely I don't have access to Server configuration on my client server.

Ievgen this method worked for regular table but not working on temp table.

Any suggestions?

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

Try to use setRecord method as it is shown here www.axaptapedia.com/Temporary_tables

Was this reply helpful? Yes No
Khurshid Wali 928 on at

Like (0)

Report

What is the function of setRecord and where to write this code?

I am little bit confused!

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

It has an example with explanation there, here it is this.queryRun().setRecord(tmpTableClass::populateTmpData()); where tmpTableClass::populateTmpData() returns temp table

Was this reply helpful? Yes No
Khurshid Wali 928 on at

Like (0)

Report

but there is no tmpTableClass in my AX version?

AX 2012 R3 6.3.3000

Was this reply helpful? Yes No
faiz7049 2,382 on at

Like (0)

Report

Hi Khurshid,

Please go through below link.

mafsarkhan.blogspot.com/.../example-for-using-tempdb-table-in-query.html

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

yes, it's just an example, there should be your table, please look at blog post provided by faiz7049

Was this reply helpful? Yes No
Khurshid Wali 928 on at

Like (0)

Report

Using SetRecord is not showing any Result

qbds=this.querybuilddatasource().addDataSource(tableNum(AbcPositionHierarchy));
qbds.joinMode(JoinMode::ExistsJoin);
qbds.addLink(fieldNum(AbcHRMEmployeeLookup,HcmWorker),fieldNum(AbcPositionHierarchy,workerRecId),this.queryBuildDataSource().name());
this.setRecord(abcPositionHierarchy);

what is wrong with this code? especially this.setRecord line

Was this reply helpful? Yes No