@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.

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 

@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.

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

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

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

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. 

@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?

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!

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 FPART_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