Temperoray Table Join Issue

(0) Share

Report

Posted on by TU1506

147

Hi,

While trying to update temp table by join temporary table with Normal Table, not able to update the same.

Without Join (Hard Coding of Values), able to update the temp table.

Below is the code: It is written on Form Extension >> New Button >> Clicked Event

Any advice on this!!!

[FormControlEventHandler(formControlStr(AccountingSourceExplorer, FormButtonControl1), FormControlEventType::Clicked)]
public static void FormButtonControl1_OnClicked(FormControl sender, FormControlEventArgs e)
{
CustTrans custTrans;
CustTable custTable;
DirPartyTable dirPartyTable;
AccountingSourceExplorerTmp accountingSourceExplorerTmpLocal;
FormDataSource AccountingSourceExplorerTmp_ds;

Info("Form Clicked");

FormRun formRun = sender.formRun() as formRun;

AccountingSourceExplorerTmp_ds = sender.formRun().dataSource(formDataSourceStr(AccountingSourceExplorer, AccountingSourceExplorerTmp)) as FormDataSource;

accountingSourceExplorerTmpLocal = AccountingSourceExplorerTmp_ds.cursor();

update_recordSet accountingSourceExplorerTmpLocal
setting InvoiceDescription = custTrans.InvoiceDescription
where accountingSourceExplorerTmpLocal.InvoiceDescription == ''
join custTrans
where accountingSourceExplorerTmpLocal.SubledgerVoucher == custTrans.Voucher;
join custTable
where custTable.AccountNum == custTrans.AccountNum;

AccountingSourceExplorerTmp_ds.research();

Info(strFmt("CustTransVoucher%1,Description%2",custTrans.Voucher,accountingSourceExplorerTmpLocal.InvoiceDescription));
}

I have the same question (0)

All responses (12)

Answers (0)

Martin Dráb 238,769 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
First if all, please tell us what kind of temporary table is it (InMemory or TempDB.

Then tell us what you mean by "not able to update".

By the way, please always use Insert > Code (in the rich formatting view) to paste source code this this forum. Let me also fix the bug at the line with where accountingSourceExplorerTmpLocal.SubledgerVoucher, remove the unused variables and do other things to make your code easier to understand:

[FormControlEventHandler(formControlStr(AccountingSourceExplorer, FormButtonControl1), FormControlEventType::Clicked)] public static void FormButtonControl1_OnClicked(FormControl sender, FormControlEventArgs e) { FormRun formRun = sender.formRun() as formRun; FormDataSource accountingSourceExplorerTmp_ds = sender.formRun().dataSource(formDataSourceStr(AccountingSourceExplorer, AccountingSourceExplorerTmp)) as FormDataSource; AccountingSourceExplorerTmp accountingSourceExplorerTmpLocal = AccountingSourceExplorerTmp_ds.cursor(); CustTrans custTrans; CustTable custTable; update_recordSet accountingSourceExplorerTmpLocal setting InvoiceDescription = custTrans.InvoiceDescription where accountingSourceExplorerTmpLocal.InvoiceDescription == '' join custTrans where custTrans.Voucher == accountingSourceExplorerTmpLocal.SubledgerVoucher join custTable where custTable.AccountNum == custTrans.AccountNum; AccountingSourceExplorerTmp_ds.research(); info(strFmt("CustTransVoucher%1, Description%2",custTrans.Voucher, accountingSourceExplorerTmpLocal.InvoiceDescription)); }

I see you research the data source, but you show InvoiceDescription from accountingSourceExplorerTmpLocal, which is something else.

The join with CustTable looks useless - you never use it for anything.

Was this reply helpful? Yes No
TU1506 147 on at

Like (0)

Report
Copy link

Link copied!
Hi Martin,

Many Thx for your prompt reply.

1. It is a TempDB standard table.

2. Invoice Description is a blank value field. It needs to be updated.

3. Even after update statement it is still blank.

4. Invoice Description is a customized field in both AccountingSourceExplorerTmp & Cust Trans Table. Objective is to update Invoice Description field of AccountingSourceExplorerTmp table from Invoice Description field of CustTrans Table based on this condition.

custTrans.Voucher == accountingSourceExplorerTmpLocal.SubledgerVoucher

5. Agree Cust Table is of no use. But without that also updation is not happening.

6. Info statement is also of no use.

7. Bug found by you, was a typo mistake at forum level. In my machine it was correct.

8. I made changes as you suggested still temp table not updating.

New Code

[FormControlEventHandler(formControlStr(AccountingSourceExplorer, FormButtonControl1), FormControlEventType::Clicked)] public static void FormButtonControl1_OnClicked(FormControl sender, FormControlEventArgs e) { AccountingSourceExplorerTmp accountingSourceExplorerTmpLocal; FormDataSource AccountingSourceExplorerTmp_ds; Info("Form Clicked"); FormRun formRun = sender.formRun() as formRun; AccountingSourceExplorerTmp_ds = sender.formRun().dataSource(formDataSourceStr(AccountingSourceExplorer, AccountingSourceExplorerTmp)) as FormDataSource; accountingSourceExplorerTmpLocal = AccountingSourceExplorerTmp_ds.cursor(); update_recordSet accountingSourceExplorerTmpLocal setting InvoiceDescription = custTrans.InvoiceDescription where accountingSourceExplorerTmpLocal.InvoiceDescription == '' join custTrans where accountingSourceExplorerTmpLocal.SubledgerVoucher == custTrans.Voucher; AccountingSourceExplorerTmp_ds.research(); }

Was this reply helpful? Yes No
Martin Dráb 238,769 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Let me format your code correctly, so it easier for me (and everyone else) to read:

[FormControlEventHandler(formControlStr(AccountingSourceExplorer, FormButtonControl1), FormControlEventType::Clicked)] public static void FormButtonControl1_OnClicked(FormControl sender, FormControlEventArgs e) { FormRun formRun = sender.formRun() as formRun; FormDataSource accountingSourceExplorerTmp_ds = sender.formRun().dataSource(formDataSourceStr(AccountingSourceExplorer, AccountingSourceExplorerTmp)); AccountingSourceExplorerTmp accountingSourceExplorerTmpLocal = accountingSourceExplorerTmp_ds.cursor(); update_recordSet accountingSourceExplorerTmpLocal setting InvoiceDescription = custTrans.InvoiceDescription where accountingSourceExplorerTmpLocal.InvoiceDescription == '' join custTrans where custTrans.Voucher == accountingSourceExplorerTmpLocal.SubledgerVoucher; accountingSourceExplorerTmp_ds.research(); }

When your code doesn't work as expected, a good idea is splitting the work to smaller pieces and debugging them one by one.

In this case, let's use a while select statement to fetch the data and update() to update the field. The main focus for now is the query. If you don't get into the body of the while select statement, it means that your query doesn't find anything to update, and we must focus on the query and its inputs.

[FormControlEventHandler(formControlStr(AccountingSourceExplorer, FormButtonControl1), FormControlEventType::Clicked)] public static void FormButtonControl1_OnClicked(FormControl sender, FormControlEventArgs e) { FormRun formRun = sender.formRun() as formRun; FormDataSource accountingSourceExplorerTmp_ds = sender.formRun().dataSource(formDataSourceStr(AccountingSourceExplorer, AccountingSourceExplorerTmp)); AccountingSourceExplorerTmp accountingSourceExplorerTmpLocal = accountingSourceExplorerTmp_ds.cursor(); ttsbegin; while select forUpdate accountingSourceExplorerTmpLocal where accountingSourceExplorerTmpLocal.InvoiceDescription == '' join custTrans where custTrans.Voucher == accountingSourceExplorerTmpLocal.SubledgerVoucher { accountingSourceExplorerTmpLocal.InvoiceDescription = custTrans.InvoiceDescription accountingSourceExplorerTmpLocal.update(); } ttscommit; accountingSourceExplorerTmp_ds.research(); }

Was this reply helpful? Yes No
TU1506 147 on at

Like (0)

Report
Copy link

Link copied!
Hi Martin,

There is some issue with Join Cust Trans. What i am still clueless. Body is executing to number of records available in Temp Table, but in cust trans buffer i am not getting anything while debugging.

Just for testing purpose i removed Join & executed below statement & it is working fine. But this is a bad way of coding especially when record sizes will increase.

Below is the code:

ttsbegin; while select forUpdate accountingSourceExplorerTmpLocal where accountingSourceExplorerTmpLocal.InvoiceDescription == '' { select forupdate custTrans where custTrans.Voucher == accountingSourceExplorerTmpLocal.SubledgerVoucher; info(strFmt('CustVoucher:%1',custTrans.Voucher)); if (CustTrans) { accountingSourceExplorerTmpLocal.InvoiceDescription = custTrans.InvoiceDescription; accountingSourceExplorerTmpLocal.update(); } } ttscommit;

Was this reply helpful? Yes No
Martin Dráb 238,769 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Are you saying that you're getting the right records when using the join with CustTrans, just the custTrans variable doesn't have field values populated?

Was this reply helpful? Yes No
TU1506 147 on at

Like (0)

Report
Copy link

Link copied!

Yes, in case of Join, Cust Trans doesn't have field values.

Was this reply helpful? Yes No
Martin Dráb 238,769 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Can you confirm that this is the exact query you're using? Don't you have a field list there, for example, or a different type of join?

while select forUpdate accountingSourceExplorerTmpLocal where accountingSourceExplorerTmpLocal.InvoiceDescription == '' join custTrans where custTrans.Voucher == accountingSourceExplorerTmpLocal.SubledgerVoucher

If it's the same, please replace it with the following command and share the output with us.

select generateOnly forceLiteral accountingSourceExplorerTmpLocal where accountingSourceExplorerTmpLocal.InvoiceDescription == '' join custTrans where custTrans.Voucher == accountingSourceExplorerTmpLocal.SubledgerVoucher; info(accountingSourceExplorerTmpLocal.getSQLStatement());

I don't think there is any problem in the query; I suspect that you either have a different code or you aren't checking the result correctly.

Was this reply helpful? Yes No
TU1506 147 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin.

Yes , executing the above written code only.

Out put of second statement as you asked

SELECT T1.ACCOUNTINGCURRENCYAMOUNT,T1.ACCOUNTINGDATE,T1.ACCOUNTINGDISTRIBUTIONRECID,T1.DESTINATIONCOMPANY,T1.DOCUMENTDATE,T1.DOCUMENTDESCRIPTION,T1.DOCUMENTNUMBER,T1.DOCUMENTTEXT,T1.GENERALJOURNALACCOUNTENTRY,T1.ISSDRILINE,T1.JOURNALCATEGORY,T1.JOURNALNUMBER,T1.LEDGERACCOUNT,T1.LINEDATE,T1.LINEDESCRIPTION,T1.LINEDOCUMENTREFERENCE,T1.LINENUMBER,T1.LINETEXT,T1.LOCATION,T1.MAINACCOUNTID,T1.MAINACCOUNTNAME,T1.MONETARYAMOUNT,T1.PARTYNAME,T1.PARTYNUMBER,T1.POSTINGTYPE,T1.PRODUCTNUMBER,T1.PROJECTACTIVITYNUMBER,T1.PROJECTCATEGORYID,T1.PROJECTID,T1.PROJECTLINEPROPERTYID,T1.QUANTITY,T1.SIDE,T1.SOURCEDOCUMENTRECID,T1.SOURCERELATIONTYPE,T1.SUBLEDGERVOUCHER,T1.TEXT,T1.TRANSACTIONCURRENCYAMOUNT,T1.TRANSACTIONCURRENCYCODE,T1.TYPEENUMNAME,T1.UNIT,T1.DIMENSIONVALUES,T1.DIMENSIONVALUES2_,T1.DIMENSIONVALUES3_,T1.DIMENSIONVALUES4_,T1.DIMENSIONVALUES5_,T1.DIMENSIONVALUES6_,T1.DIMENSIONVALUES7_,T1.DIMENSIONVALUES8_,T1.DIMENSIONVALUES9_,T1.DIMENSIONVALUES10_,T1.DIMENSIONVALUES11_,T1.PRIMARYFOCUS,T1.LEDGERDIMENSION,T1.PRIMARYFOCUSDESCRIPTION,T1.ISCORRECTION,T1.REPORTINGCURRENCYAMOUNT,T1.SUBLEDGERJOURNALACCOUNTENTRYRECID,T1.INVOICEDESCRIPTION,T1.RECVERSION,T1.PARTITION,T1.RECID,T2.POSTINGPROFILECLOSE,T2.ACCOUNTINGEVENT,T2.ACCOUNTNUM,T2.AMOUNTCUR,T2.AMOUNTMST,T2.APPROVED,T2.APPROVER,T2.BANKCENTRALBANKPURPOSECODE,T2.BANKCENTRALBANKPURPOSETEXT,T2.BANKLCEXPORTLINE,T2.CANCELLEDPAYMENT,T2.CASHDISCCODE,T2.CASHDISCBASEDATE,T2.CASHPAYMENT,T2.CLOSED,T2.COLLECTIONLETTER,T2.COLLECTIONLETTERCODE,T2.COMPANYBANKACCOUNTID,T2.CONTROLNUM,T2.CORRECT,T2.CURRENCYCODE,T2.CUSTBILLINGCLASSIFICATION,T2.CUSTEXCHADJUSTMENTREALIZED,T2.CUSTEXCHADJUSTMENTUNREALIZED,T2.DEFAULTDIMENSION,T2.DELIVERYMODE,T2.DIRECTDEBITMANDATE,T2.DOCUMENTDATE,T2.DOCUMENTNUM,T2.DUEDATE,T2.EUROTRIANGULATION,T2.EXCHADJUSTMENT,T2.EXCHADJUSTMENTREPORTING,T2.EXCHRATE,T2.EXCHRATESECOND,T2.FIXEDEXCHRATE,T2.INTEREST,T2.INVOICE,T2.INVOICEPROJECT,T2.LASTEXCHADJ,T2.LASTEXCHADJRATE,T2.LASTEXCHADJRATEREPORTING,T2.LASTEXCHADJVOUCHER,T2.LASTSETTLEACCOUNTNUM,T2.LASTSETTLECOMPANY,T2.LASTSETTLEDATE,T2.LASTSETTLEVOUCHER,T2.OFFSETRECID,T2.ORDERACCOUNT,T2.PAYMID,T2.PAYMMETHOD,T2.PAYMMODE,T2.PAYMREFERENCE,T2.PAYMSPEC,T2.POSTINGPROFILE,T2.PREPAYMENT,T2.REASONREFRECID,T2.REPORTINGCURRENCYAMOUNT,T2.REPORTINGCURRENCYEXCHRATE,T2.REPORTINGCURRENCYEXCHRATESECONDARY,T2.REPORTINGCURRENCYCROSSRATE,T2.REPORTINGEXCHADJUSTMENTREALIZED,T2.REPORTINGEXCHADJUSTMENTUNREALIZED,T2.SETTLEAMOUNTCUR,T2.SETTLEAMOUNTMST,T2.SETTLEAMOUNTREPORTING,T2.SETTLEMENT,T2.TAXINVOICESALESID,T2.THIRDPARTYBANKACCOUNTID,T2.TRANSDATE,T2.TRANSTYPE,T2.TXT,T2.VOUCHER,T2.PAYMSCHEDID,T2.PAYMTERMID,T2.CUSTAUTOMATIONEXCLUDE,T2.CUSTAUTOMATIONPREDUNNINGSENT,T2.CUSTAUTOMATIONPREDICTIONSENT,T2.SETTLEAMOUNT_MX,T2.INVOICETYPE_IT,T2.CREDMANEXCLUDEFROMCREDITCONTROL,T2.INVOICEDESCRIPTION,T2.MODIFIEDDATETIME,T2.MODIFIEDBY,T2.MODIFIEDTRANSACTIONID,T2.CREATEDDATETIME,T2.CREATEDBY,T2.CREATEDTRANSACTIONID,T2.RECVERSION,T2.PARTITION,T2.RECID FROM tempdb."DBO".t883IISdvfonedev2_519496_AA80D94310D746989D2E5C61407C2AE6 T1 CROSS JOIN CUSTTRANS T2 WHERE ((T1.PARTITION=5637144576) AND (T1.INVOICEDESCRIPTION='')) AND (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'ogrp')) AND (T2.VOUCHER=T1.SUBLEDGERVOUCHER)) ORDER BY T1.SUBLEDGERVOUCHER,T1.ACCOUNTINGDATE OPTION(FAST 9)

Was this reply helpful? Yes No
Martin Dráb 238,769 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

The query does fetch all fields from CustTrans table - there is no problem to fix.

Where and how are you checking field values? Can you give us a screenshot of the problem?

Was this reply helpful? Yes No
TU1506 147 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

Pl. find file having code & output of it. Here Temp table buffer returning the values but not CustTrans buffer.

Was this reply helpful? Yes No