We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

enhanced query execution: update column with NULL

Knight
New Contributor
New Contributor

Hi,
Getting following error on updating NULL value using Enhanced query execution job.

Query:

select concat(Name,'-Removed',' ','on-',CURRENT_TIMESTAMP) AS accounts__name,'SUSPENDED FROM IMPORT SERVICE' as accounts__status,NULL as accounts__REFERENCED_ACCOUNTKEY, accountkey as accounts__primarykey from accounts where endpointkey=3 and STATUS in ('Manually Suspended','2','Inactive') and NAME not like '%Removed on%';
 
Error:
Error while updating table: ACCOUNTS : Incorrect integer value: '' for column 'REFERENCED_ACCOUNTKEY' at row 1
4 REPLIES 4

Dhruv_S
Saviynt Employee
Saviynt Employee

Hi @Knight 

I tried with a sample query to test, and it was able to update null with similar syntax.

SELECT NULL AS ENDPOINTS__CUSTOMPROPERTY2 , ep.endpointkey as ENDPOINTS__PRIMARYKEY FROM ENDPOINTS ep ep.endpointkey=1;

Testing1.PNG

Can you please check and confirm if it is giving expected result in Preview before running the job.

Regards,

Dhruv Sharma

CR
Regular Contributor III
Regular Contributor III

 Hi @Knight ,

REFERENCED_ACCOUNTKEY is big integer it wont be allow null update, try without refrencekey it will work.


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

jbirkmeier
New Contributor
New Contributor

Hi Raghu and Dhruv,

Is it possible to force null into REFERENCED_ACCOUNTKEY if needed?  Schema in Data Analyzer says that it accepts a null value.  Thanks!

jbirkmeier_0-1701358975634.png

 

SELECT Concat(NAME, '-Removed', ' ', 'on-', CURRENT_TIMESTAMP) AS accounts__name
       ,
       'SUSPENDED FROM IMPORT SERVICE'                         AS
       accounts__status,
       'NULL'                                                    AS
       accounts__REFERENCED_ACCOUNTKEY,
       accountkey                                              AS
       accounts__primarykey
FROM   accounts
WHERE  endpointkey = 3
       AND status IN ( 'Manually Suspended', '2', 'Inactive' )
       AND NAME NOT LIKE '%Removed on%'; 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.