Finance | Project Operations, Human Resources, ...

Using insert recordset in temporary table

(0) Share

Report

Posted on by Community Member

I have a Form based where I am using Insertrecord set to insert the data in to the temporary table,

But it when debugger comes to execution of this statement, AX doesn't respond and have to restart. What could be the problem...?

    InventSum                           inventSum;
    InventDim                           inventdim;
    InventSerial                        inventSerial;
    z_RentalOrderLine                   rentalLine;
    maRentalSiteReconciliation          reconciliation;
    maItemsWithNoOpenRentalContracts    temptable;

    delete_from reconciliation;
    delete_from temptable;

    insert_recordset reconciliation (ItemId,InventSizeId,InventColorId,InventStyleID,InventLocationId,WMSLocationId,InventSerialId,z_ManufacturersSerial,z_NBV)
    select ItemID from inventSum
    join InventSizeId,InventColorId,InventStyleID,InventLocationId,WMSLocationId from inventdim
    join InventSerialId,z_ManufacturersSerial,z_NBV from inventSerial
    where inventSum.InventDimId         == InventDim.inventDimId
       && inventSerial.InventSerialId   == inventdim.inventSerialId
       && inventSerial.ItemId           == inventSum.ItemId
       && inventSum.Closed              == NoYes::No
       &&(inventSum.PostedQty   inventSum.Received - inventSum.Deducted   inventSum.Registered - inventSum.Picked) - inventSum.ReservPhysical > 0
       && inventdim.wMSLocationId       == "On Rent";

I have the same question (0)

All responses (5)

Answers (0)

ergun sahin 8,826 Moderator on at

Like (0)

Report

I can't see a problem in the code. It's probably taking too long because you're processing inside the select (These tables contain too many records).

It may take less time if you open the fields in your temp table and perform operations after the records are created (with filtered records). It may sound silly, but it might be faster to just insert the records and delete the redundant ones.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Ergun Sahin

can you please share me the sample code for the below statement.

"It may take less time if you open the fields in your temp table and perform operations after the records are created (with filtered records)"

Was this reply helpful? Yes No
ergun sahin 8,826 Moderator on at

Like (0)

Report
It's actually simple but I didn't explain it well. Do not do the following calculation.

&&(inventSum.PostedQty inventSum.Received - inventSum.Deducted inventSum.Registered - inventSum.Picked) - inventSum.ReservPhysical > 0

Open these fields to the temp table and fill in the values. When the records are created, redundant records will come as we remove one of the filtering conditions. Delete the redundant records (with newly opened fields) in temp table

Alternatively, you can review the select clause;
The order of the tables (you should keep the sample set as small as possible),
The order of the fields (indexes).

The order of where conditions

Run tests with smaller data, it can be written better than now.

Was this reply helpful? Yes No
Suggested answer

Hossein.K 6,648 on at

Like (0)

Report

Hi mahiims,

I remember some year ago , I had the same issue about temporary tables and after run the form Ax Client hanged.

I solved it via set the same connection for all the tables which were used in insert_recordset queries(temporary and regular)

RegularTable.setConnection(this.parmUserConnection());

TempTable.setConnection(this.parmUserConnection());

Was this reply helpful? Yes No
Suggested answer

Hossein.K 6,648 on at

Like (0)

Report

Some times this issue related to the table in AX is not synchronize or cache DB or ...

First do this.

Was this reply helpful? Yes No