Null Value in integer field of data schema | Community
Skip to main content
N
nitesh_anwani
Level 2
January 13, 2022
Solved

Null Value in integer field of data schema

  • January 13, 2022
  • 2 replies
  • 6694 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Tobias_Lohmann

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-reference/elements-attributes/attribute.html?lang=en)

 

I hope that gives you some hints.

Best regards, Tobias

 

2 replies

isahore
Community Advisor
isahoreCommunity Advisor
Community Advisor
January 13, 2022

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

N
nitesh_anwaniAuthor
Level 2
January 13, 2022

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

Jyoti_Yadav
Jyoti_Yadav
Level 8
January 13, 2022

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

    N
    nitesh_anwaniAuthor
    Level 2
    January 13, 2022

    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.

    N
    nitesh_anwaniAuthor
    Level 2
    January 24, 2022

    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-reference/elements-attributes/attribute.html?lang=en)

     

    I hope that gives you some hints.

    Best regards, Tobias

     


    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

    Terms & ConditionsAccessibility statement

    Loading footer...