Null Value in integer field of data schema

[nitesh_anwani]

Hi Folks,

I have requirement from a client where in they want to store the NULL value in the integer field of data schema. For the same, I tried multiple things like dropping the constraint of DEFAULT and NOT NULL from the table field. However, I have observered that the UPDATE actvitiy in the workflow uses the COALESCE function while inserting the data. I got to know this by enabling workflow SQL logs. 

I have also tried the option store empty in desitnation field in the advance parameter of update activity.

Anything else other than mentioned things to achive this requirement. Any documentation or suggestion is highliy appreciated.

Regards,

Nitesh Anwani

[isahore]

Hi @nitesh_anwani ,

A field of type Integer will not let you store a NULL value in the database table. However, depending on your use case, you could create an input form for data visualization and code it in a way to display nothing in case the value stored is 0.

P.S. This workaround is suitable only if the data to be stored in the table does not have any "0" value records.

Alternatively, you can change the field type from Integer to a String to work it out. It will come with some downsides, such as query responses based on this field, etc.

Thanks,

Ishan

[nitesh_anwani]

hi @isahore ,

Thanks for your response.

At DB level it is possible to store the NULL in the integer field. I tried it by inserting the record, and not mapping the integer field in the Update activity mapping. Though, it was showing in the 0 in data schema, data section. But, NULL value was stored in table. Also, before inserting the record into the table, I removed the NOT NULL and DEFAULT 0 constraint from the integer field. Hence, we can say that at DB level it is possible, and The main concern is the update activity which is applying the COALESCE function while inserting the data.

Regards,

Nitesh Anwani

[nitesh_anwani]

Also, the input form won't resolve my case. Because our requirement is store the transaction data into the data, where 0 and NULL has different meanings and purpose.

[Jyoti_Yadav]

Hi @nitesh_anwani ,

In update activity, use iff condition, i.e 

Iff( @age=NULL,0,@age)

This will help you to bypass Null data values.

Thanks,

Jyoti

[nitesh_anwani]

Hi @Jyoti_Yadav ,

Thanks for your response.

I don't want to bypass the NULL values. If you check my initial post, I want to store the NULL values in the integer field.

[Tobias_Lohmann · Accepted solution]

Hi @nitesh_anwani ,

I think that should be possible, but maybe it won't lead to the result you need . I tested this on my local:

1. created a new schema with an integer that can be NULL ('notNull="false"') and is NULL by default ('sqlDefault="NULL"'). Normally integer fields are intialized defaulting to 0.

 2. pushed two records to the dataset, one without the integer value:

 3. Looked at the data in Campaign. "0" and "NULL" will be shown as "0", so you won't be able to differentiate looking at the fields (and I believe in input forms you cannot send "NULL" using an input field). But if you analyze the data with code, you can indeed differentiate.

column 4 has

Iif(@integerValue = NULL, 'NULL', 'NOTNULL')

 

When working with NULL and integer in Campaign there are some rules that need to be taken into account:

• notNull (boolean): lets you redefine Adobe Campaign’s behavior regarding the management of NULL records in the database. By default, numeric fields are not null and string and date type fields can be null.

• sqlDefault (string): this attribute enables you to define the default value taken into account for updating the database if the @notNull attribute is activated. If this attribute is added after the attribute creation, the schema behavior will not change even for the new records. To change the schema and update the value for new records, you need to delete and create again the attribute.

(https://experienceleague.adobe.com/docs/campaign-classic/using/configuring-campaign-classic/schema-r...)

I hope that gives you some hints.

Best regards, Tobias

[nitesh_anwani]

Hi @Tobias_Lohmann ,

Thanks for your response.

This solves my problem. As the data, I'm storing is for the automation campaign execution. 

Hence, It doesn't make any difference, if the value is getting displayed as 0 or NULL. As data directly consumed in workflows basis on the actual value conditions. If the actual value is correct, then I'm getting correct data.

Regards,

Nitesh Anwani