While select forupdate

(0) Share

Report

Posted on by Community Member

Hi I have this while select. What I want is to check if JournalNum from NCIntegrationJournalTable exists in NCIntegrationJournalTrans. And if just 1 of the selected records in NCIntegrationJournalTrans has something in its ErrorLog field, then we must not do anything, else if there are nothing in ErrorLog, the journal will be posted.

As it is now, if just 1 of the records has ErrorLog=="", then it will post all of the records as if they are all ok, even if the others has error. And thats not correct.

    while select forupdate NCIntegrationJournalTable
        exists join ncIntegrationsJournalTrans

        where ncIntegrationsJournalTrans.JournalNum == NCIntegrationJournalTable.JournalNum &&
        ncIntegrationsJournalTrans.ErrorLog == ""
    {
        ttsBegin;
           NCIntegrationJournalTable.Posted = true;
           NCIntegrationJournalTable.update();
        ttsCommit;
    }

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (2)

Verified answer

Community Member on at

Like (1)

Report

RE: While select forupdate

Change it to notExists join where ErrorLog != "". So you only update those where no errors are found.

On another note you should change this to update_recordset.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: While select forupdate

Hi Jens

Thanks for your response.

I dont update anything in ncIntegrationsJournalTrans, where Errorlog is. I use ErrorLog as my criteria. I only update 1 row in NCIntegrationJournalTable, based on the criteria I have set. I just want my where clause to check, if just 1 row in ncIntegrationsJournalTrans, where errorlog != "", then it should not update the row in NCIntegrationJournalTable.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: While select forupdate

Yes, so as I said you have to use a not exists join with exactly the clause I mentioned.

Then only table records where no trans record with ErrorLog exists will be updated.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: While select forupdate

Jens

I have tried to demonstrate it in the attached image.

Correction:

The JournalNum in the NcIntegrationTable must be 546 and NOT 456.

Was this reply helpful? Yes No
duy.dang 65 on at

Like (0)

Report

RE: While select forupdate

According to me, it will be easy with below logic:

Select NCIntegrationJournalTable on your condition.

After that, with receiving result, you go to check condition in ncIntegrationsJournalTrans.

if it 's equal you condition, you just update on selected NCIntegrationJournalTable.

Finally, if it runs as you want, you may write a complex statement like you said.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

RE: While select forupdate

What is the issue with my solution? It should work. Please share your code with my implemented suggestion. I don't understand.

Was this reply helpful? Yes No