Setting a column to NULL using Enhanced Query

[Wills]

Hello,

I am trying to run this simple query in Enhanced Query to set the firstremindermailsent column of a campaign to NULL. But, it is failing with the following details:

Input Query	select NULL AS campaign__firstremindermailsent,ID as campaign__PRIMARYKEY from campaign where ID = 71;
Prepared Query	UPDATE CAMPAIGN SET FIRSTREMINDERMAILSENT =:firstremindermailsent WHERE id =:id

Following is the query:

select NULL AS campaign__firstremindermailsent, ID as campaign__PRIMARYKEY from campaign where ID = 71;

Could you please help?

Thank you!

[rushikeshvartak]

select '' AS campaign__firstremindermailsent,ID as campaign__PRIMARYKEY from campaign where ID = 71;

[Wills]

Hello Rushikesh,

It is still failing:

Input Query	select '' AS campaign__firstremindermailsent,ID as campaign__PRIMARYKEY from campaign where ID = 71;
Prepared Query	UPDATE CAMPAIGN SET FIRSTREMINDERMAILSENT =:firstremindermailsent WHERE id =:id

Could you please help?

Thank you!

[rushikeshvartak]

can't you update same from UI?

[Wills]

I am not aware of amy UI that gives access those fields of the Campaign table.

Is there a way to do it through a UI?

[rushikeshvartak]

You can’t NULL value using enhanced query 

[dgandhi]

What is the error that you are getting? Can you please paste that here?

try this also once:

select null AS campaign__firstremindermailsent,ID as campaign__PRIMARYKEY from campaign where ID = 71;

[Dhruv_S]

Hi @Wills 

Please refer to the below post where I had responded to similar requirement to null the value. 

Re: enhanced query execution: update column with N... - Saviynt Forums - 62104

Regards,

Dhruv Sharma

[CR]

try below 

select 'null' AS campaign__firstremindermailsent,ID as campaign__PRIMARYKEY from campaign where ID = 71;

[Wills]

Hello,

Thank you for all your responses. I have tried all the NULL combinations possible before coming to the forum and non of them worked [ "NULL", 'Null', NULL, "", '', "''", ....]. From what I see on the built query, Enhanced Query does not build the query correctly when there is a null or blank value being used.

[CR]

Thanks for confirmation.

[Wills]

Hello, I still do not have a solution. Please assist.

Thank you!

[rushikeshvartak]

Currently its may not supported raise idea ticket

[CR]

it is issue, need to raise ticket for accepting null value FIRSTREMINDERMAILSENT  column in respected table @Wills 

[Wills]

Hello,

I have reaised a ticket. Thank you for all the help!

[CR]

Thanks for confirmation please close the thread and accept solution.

[rushikeshvartak]

Use below query 

select 0 AS campaign__firstremindermailsent,ID as campaign__PRIMARYKEY from campaign where ID = 559;

Before

After

[Wills]

Hello Rushikesh,

I know that settting the value to 0 works. It is setting it to NULL that does not work. And based on the document, if you want to resend a reminder, you need to set the value to NULL and not 0.

Thank you!