Unable to re-loop the Temporary Table records of table type TempDB or InMemory? And Your loop works for 1st time and not working for 2nd time with same buffer. Use setTmpData() – for Inmemory & linkPhysicalTableInstance() – for TempDB.

Frustrated with Temporary table and unable to loop the table or the inner loop gets terminated? Probably you are facing the same problem.

Eg: TmpTaxWorkTrans table is InMemory table.

If you are trying to loop as below it will work for 1st time, and if you are trying to loop for 2nd time will not work .

Solution: take an existing copy of the tempDB/InMemory in 2nd buffer to loop again.

Eg: if you want to loop the tmpTaxWorkTrans

purchTotals = PurchTotals::newParmTable(vendInvoiceInfoTable, PurchUpdate::All, AccountOrder::None, vendInvoiceInfoTable.ParmId, ”, vendInvoiceInfoTable.Ordering);

purchTotals.calc();

taxRegulation = TaxRegulation::newTaxRegulation(purchTotals.tax());

tmpTaxRegulation  = taxRegulation.tmpTaxRegulation();

tmpTaxWorkTrans = taxRegulation.tmpTaxWorkTrans();

while tmpTaxWorkTrans

{

// The loop works for 1st time. Do some calc. assigned to a variable.

// taxCode = tmpTaxWorkTrans.TaxCode; // your tax code

}

You want to loop the tmpTaxWorkTrans again for 2nd time. it will not work properly.

While tmpTaxWorkTrans

where tmpTaxWorkTrans.TaxCode == taxCode

{

//Info() or some business logic – this will not run like regular table works, it will terminate

}

Solution:

For InMemory: Use setTmpData() to have a copy of table buffer for looping 2nd time

For TempDB: use linkPhysicalTableInstance() to have a copy of table buffer for looping 2nd time

Eg:

tmpTaxWorkTrans tmpTaxWorkTrans, tmpTaxWorkTransBuf;

tmpTaxWorkTrans = taxRegulation.tmpTaxWorkTrans();

//set the data 1st in tmpTaxWorkTransBuf – a copy to for 2nd loop.

tmpTaxWorkTransBuf.setTmpData(tmpTaxWorkTrans); // as it is InMemory

// if the table is of type Temp Db use //tmpTaxWorkTransBuf.linkPhysicalTableInstance(tmpTaxWorkTrans);

// 1st loop

while tmpTaxWorkTrans

{

// The loop works for 1st time. Do some calc. assigned to a variable.

// taxCode = tmpTaxWorkTrans.TaxCode; // your tax code

}

2nd loop

while tmpTaxWorkTransBuf

{

// business logic – this loop will work.

}

SQL – Get all the Default dimensions by SQL Server

DimensionAttributeValueSet and DimensionAttributeValueCombination are the tables used to get the all default dimensions. If we include the DimensionAttributeValueSetItem then we get all the dimensions combinations(as DimensionAttributeValueSet holds two or more records in DimensionAttributeValueSetItem)

SQL Query:

select DIMENSIONATTRIBUTEVALUECOMBINATION.recid as DefaultDimension
–DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE,
–DimensionAttribute.name,
–DimensionAttributeValueSetItem.DisplayValue,
–DimensionAttribute.VIEWNAME,
–DIMENSIONATTRIBUTEVALUECOMBINATION.MAINACCOUNT
from DIMENSIONATTRIBUTEVALUECOMBINATION
join DimensionAttributeValueSet on DimensionAttributeValueSet.RecId = DIMENSIONATTRIBUTEVALUECOMBINATION.RECID
–group by DIMENSIONATTRIBUTEVALUECOMBINATION.recid