Skip to main content
Solved

Updating values in IFS cloud


Forum|alt.badge.img+8
  • Sidekick (Customer)

Hello,

 

How can I update data in one column without using UPDATE statement? In this column I have some date values in format for example 01-DEC-24, or 03-JAN-23 but also some NULL values and I want to change those values to null with using SELECT statement, but I am not sure how can I change the data in existing column. My SQL command works but I am not sure if I can update existing column in entity configuration through + icon. Thanks

Best answer by Marcel.Ausan

@Ed22 OK, so basically you wanna do a mass update on your VALID_TO column and set the value null. You cannot do that by adding a custom attribute. This will have no impact on the already existing values in that column. 

1st question:

  • is that field updatable in the application? Can it be manually set to null for one record? If yes then Migration Job would work.

 

You could easily do a mass update with a Migration Job. You would need to create a Migration Job → Migrate Source Data on view  PURCHASE_QUANTITY_PRICE and set VALID_UNTIL = null

 

Besides P and K fields keep only VALID_UNTIL. The rest you can delete. This should work fine I guess.

 

View original
Did this topic help you find an answer to your question?

Marcel.Ausan
Superhero (Partner)
Forum|alt.badge.img+20
  • Superhero (Partner)
  • September 6, 2024

@Ed22 normally you can’t update values in the DB with a SELECT statement.

How do you plan on executing the SELECT statement? Are trying to create a custom event? If that’s the case you could use the PL/SQL API.Modify() function to do the update.

If you could share more details on what you’re trying to accomplish maybe we could better support.


Forum|alt.badge.img+8
  • Sidekick (Customer)
  • September 6, 2024

For some Vendor_NO i want to update column valid_until to null values, original view is in 1 and improved in 2. This is improved SQL query: “SELECT VENDOR_NO, MIN_QTY, VALID_FROM, NULL AS VALID_UNTIL,
FROM PURCHASE_QUANTITY_PRICE 
WHERE VENDOR_NO = 'some_number' AND VALID_UNTIL = '01-DEC-24' “ i want add this SQL in Custom Field in existing column 


Marcel.Ausan
Superhero (Partner)
Forum|alt.badge.img+20
  • Superhero (Partner)
  • September 6, 2024

@Ed22 OK, so basically you wanna do a mass update on your VALID_TO column and set the value null. You cannot do that by adding a custom attribute. This will have no impact on the already existing values in that column. 

1st question:

  • is that field updatable in the application? Can it be manually set to null for one record? If yes then Migration Job would work.

 

You could easily do a mass update with a Migration Job. You would need to create a Migration Job → Migrate Source Data on view  PURCHASE_QUANTITY_PRICE and set VALID_UNTIL = null

 

Besides P and K fields keep only VALID_UNTIL. The rest you can delete. This should work fine I guess.

 


Forum|alt.badge.img+8
  • Sidekick (Customer)
  • September 6, 2024

Thanks, I will try it. So, always when I want modify data in columns in some entity, should i go through Migration Job?


Marcel.Ausan
Superhero (Partner)
Forum|alt.badge.img+20
  • Superhero (Partner)
  • September 6, 2024

@Ed22 if you want to do mass updates for existing records then mig jobs are the way to go.


Mathias Dahl
Superhero (Employee)
Forum|alt.badge.img+32
  • Superhero (Employee)
  • September 8, 2024

I would argue that a custom event action can also be used as long as the code needs little input.


Forum|alt.badge.img+8
  • Sidekick (Customer)
  • September 11, 2024

So, in database information should I input actual values or values, which I want to change and in source mapping desired value? In this case, actual values is: “SELECT * FROM PURCHASE_QUANTITY_PRICE
WHERE VENDOR_NO = '187' AND MIN_QTY = 1131 AND VALID_FROM = '4-JAN-24' AND VALID_UNTIL = ‘01-DEC-24’ AND QUOTE_PRICE = 1”,          desired: “SELECT * FROM PURCHASE_QUANTITY_PRICE
WHERE VENDOR_NO = '187' AND MIN_QTY = 1131 AND VALID_FROM = '4-JAN-24' AND VALID_UNTIL IS NULL AND QUOTE_PRICE = 1”                 In Source Mapping I have no data. 


Forum|alt.badge.img+8
  • Sidekick (Customer)
  • September 30, 2024
Marcel.Ausan wrote:

@Ed22 OK, so basically you wanna do a mass update on your VALID_TO column and set the value null. You cannot do that by adding a custom attribute. This will have no impact on the already existing values in that column. 

1st question:

  • is that field updatable in the application? Can it be manually set to null for one record? If yes then Migration Job would work.

 

You could easily do a mass update with a Migration Job. You would need to create a Migration Job → Migrate Source Data on view  PURCHASE_QUANTITY_PRICE and set VALID_UNTIL = null

 

Besides P and K fields keep only VALID_UNTIL. The rest you can delete. This should work fine I guess.

 

I click on Execute Job, but when I was searching for this row, nothing changed, the valid_until = 01-DEC-24 is still there. What else should I do?


Forum|alt.badge.img+4
  • Sidekick (Customer)
  • October 1, 2024

Found this issue and we have managed to get it to work. We had a need to reset custom columns to NULL. This is how we set up the Migration job:

 

Hope this helps!

 

 


Forum|alt.badge.img+8
  • Sidekick (Customer)
  • November 21, 2024

Hello,

 

I have same problem with this, when I execute job, it says that I have error: ORA-20124: Error.NULLVALUE: Field [CONTRACT] is a required and required value for Purchase Quantity Price. After that I add there company in ‘ ’, but immediately i have another error which says that Field [PART_NO] is a required and required value for Purchase Quantity Price. I am not sure how can I fix it, because I want to set all valid_until = 01-DEC-24 to null where supplier is 187 - there is no need to adding some additional part_no


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings