Update statement in job

Hi

I have SQL updates which has to be written in X++ .

–Update Inventory Item Name ------------------------------------------------------------------

Update #P0000 set #P0000.Txt = Name

from #P0000, InventTable, EcoResProductTranslation ERPT where

InventTable.Product = ERPT.Product and

#P0000.EmplItemId = InventTable.ItemId and InventTable.DataAreaId = @Entity and

ProjTransType = 4

–Update Employee Name ------------------------------------------------------------------------

Update #P0000 set #P0000.Txt = HWCD.Name, EmplItemId = PersonnelNumber

from #P0000, HcmWorkerCubeDimension HWCD where

#P0000.Worker = HWCD.RecId and --HWCD.DataAreaId = @Entity and

ProjTransType = 2

I wrote a job as follows:

while select * from _ecoresproductranslation join product from _inventtable

join EmplItemId from caeprojtransposting

where _ecoresproductranslation.Product == _inventtable.Product && _inventtable.ItemId ==

caeprojtransposting.EmplItemId && caeprojtransposting.ProjTransType == 4

{

while select forupdate caeprojtransposting join ItemId from _inventtable where _inventtable.ItemId ==

caeprojtransposting.EmplItemId && caeprojtransposting.ProjTransType == 4 && caeprojtransposting.Txt ==’’

{

ttsBegin;

caeprojtransposting.Txt = _ecoresproductranslation.Name;

caeprojtransposting.update();

ttsCommit;

}

}

//Update Employee Name

ttsBegin;

while select forupdate caeprojtransposting join RecId from _HcmWorkerCubeDimension where _HcmWorkerCubeDimension.RecId ==

caeprojtransposting.Worker && caeprojtransposting.Txt==’’ && caeprojtransposting.Worker != 0 && caeprojtransposting.ProjTransType == 2

{

caeprojtransposting.Txt = _HcmWorkerCubeDimension.Name;

caeprojtransposting.EmplItemId = _HcmWorkerCubeDimension.PersonnelNumber;

caeprojtransposting.update();

}

ttsCommit

}

First update works but the second update doesn’t works. I am not sure whether the above code is correct.;

Thanks

Update #P0000 set #P0000.Txt = Name

from #P0000, InventTable, EcoResProductTranslation ERPT where

InventTable.Product = ERPT.Product and

#P0000.EmplItemId = InventTable.ItemId and InventTable.DataAreaId = @Entity and

ProjTransType = 4

There can be multiple translations for product depending on the language. (I am just typing the code here). You can convert into a update_recordset.

while select forupdate #P0000 // replace #P0000 with table name

where #P0000.ProjTransType == ProjTransType::Item

join ItemId from inventTable where inventTable.ItemId == #P0000.EmplItemId

join Name from EcoResProductTranslation where EcoResProductTranslation.Product == InventTable.Product

&& EcoResProductTranslation.LanguageId == ‘en-us’ // replace it with the required language

{

#P0000.Txt = EcoResProductTranslation.Name.

#P0000.update();

}

Your second statement looks fine. Try debugging.

Use ProjTransType::Hour instead of directly using the value 2.

Thanks Kranthi

I have changed and written the second statement but it doesn’t update.

while select forupdate caeprojtransposting where caeprojtransposting.ProjTransType == ProjTransType::Hour join RecId from _HcmWorkerCubeDimension

where _HcmWorkerCubeDimension.RecId == caeprojtransposting.Worker &&

caeprojtransposting.Txt==’’ && caeprojtransposting.Worker != 0

{

ttsBegin;

caeprojtransposting.Txt = _HcmWorkerCubeDimension.Name;

caeprojtransposting.EmplItemId = _HcmWorkerCubeDimension.PersonnelNumber;

caeprojtransposting.update();

ttsCommit;

}

the value in HcmWorkerCubeDimension → recid takes as empty. But when I query it SQL ,it has the value.

Can we query from HcmWorkerCubeDimension or its not allowed for x++

Please use Insert > Insert Code to paste source code. It keeps indentation and doesn’t add extra spaces between lines, which makes the result much more readable.
Here is your code after I manually added all the lost indentation:

while select forupdate caeprojtransposting
    where caeprojtransposting.ProjTransType == ProjTransType::Hour
    
    join RecId from _HcmWorkerCubeDimension
        where _HcmWorkerCubeDimension.RecId == caeprojtransposting.Worker
          && caeprojtransposting.Txt == ''
          && caeprojtransposting.Worker != 0
{
    ttsBegin;
    caeprojtransposting.Txt = _HcmWorkerCubeDimension.Name;
    caeprojtransposting.EmplItemId = _HcmWorkerCubeDimension.PersonnelNumber;
    caeprojtransposting.update();
    ttsCommit;
}

But I would refactor it to something like this:

while select forUpdate caeProjTransPosting
    where caeProjTransPosting.ProjTransType == ProjTransType::Hour
       && caeProjTransPosting.Txt == ''
       && caeProjTransPosting.Worker != 0
    
    join RecId from hcmWorkerCubeDimension
        where hcmWorkerCubeDimension.RecId == caeProjTransPosting.Worker
           
{
    ttsBegin;
    caeProjTransPosting.Txt = hcmWorkerCubeDimension.Name;
    caeProjTransPosting.EmplItemId = hcmWorkerCubeDimension.PersonnelNumber;
    caeProjTransPosting.update();
    ttsCommit;
}

If I look at the code, it’s clear that you’re trying to use values of Name and PersonnelNumber fields, which you never select from database. That’s clearly a bug. Let’s change the code to this:

while select forUpdate caeProjTransPosting
    where caeProjTransPosting.ProjTransType == ProjTransType::Hour
       && caeProjTransPosting.Txt == ''
       && caeProjTransPosting.Worker != 0
    
    join Name, PersonnelNumber from hcmWorkerCubeDimension
        where hcmWorkerCubeDimension.RecId == caeProjTransPosting.Worker
           
{
    ttsBegin;
    caeProjTransPosting.Txt = hcmWorkerCubeDimension.Name;
    caeProjTransPosting.EmplItemId = hcmWorkerCubeDimension.PersonnelNumber;
    caeProjTransPosting.update();
    ttsCommit;
}

Give it a try and don’t forget to use the debugger if something doesn’t work as expected.

Thanks a lot Martin.

I did not notice that and thought I have added Name in the query.

Now it’s working