difference between nullidentifier and nulltextidentifier

difference between nullidentifier and nulltextidentifier

  

please explain the difference between nullidentifier and nulltextidentifier

The default null values are:

NullIdentifier = 0 use it with numbers

NullTextIdentifier = "" use it with strings

NullDate = #1900-01-01# use it with dates


Nuno Reis wrote:

The default null values are:

NullIdentifier = 0 use it with numbers

NullTextIdentifier = "" use it with strings

NullDate = #1900-01-01# use it with dates


is we have null in outsystem?


Lovish Goyal wrote:

Nuno Reis wrote:

The default null values are:

NullIdentifier = 0 use it with numbers

NullTextIdentifier = "" use it with strings

NullDate = #1900-01-01# use it with dates


is we have null in outsystem?


Hi :)

We don't and there is an "idea" related to that.

Type
Null Value
Text
"" (empty string)
Integer
0
Decimal
0
Boolean
False
Date Time
#1900-01-01 00:00:00#
Date
#1900-01-01# or NullDate function
Time
#00:00:00#
Phone Number
"" (empty string)
Binary Data
Byte array with no elements
Object
NullObject function
Currency
0
Record
Record which attributes follows these rules.
Record List
Empty list.


Cheers


Thanks Miguel and Reis

Lovish Goyal wrote:

please explain the difference between nullidentifier and nulltextidentifier

Think of these as the abstract implementation of the NULL concept (lack of value for reference parameters/foreign keys) in OutSystems... Entity Identifiers can be either numbers (Integer/Long Integer) or Text, so depending on the Entity Identifier data type you will use the respective Null*Identifier() function.

Although there is documentation on what is the actual return value of these functions, the idea is you use them to abstract that value, making your applications safer and more resilient to changes (and they also take care of type conversion).

Just to confirm understanding of these null values, is there any need to test for BOTH these values in an If block?

If(MyText="" or MyText=NullTextIdentifier(), 1, 0)

Also, if creating a SQL query (not aggregate), would I ever need to test for BOTH a null value and 0 value passed in as a parameter from OutSystems within in the SQL?

...WHERE (e.CategoryId = (CASE WHEN ((@FilterCategory IS NULL) OR (@FilterCategory <= 0)) THEN e.CategoryId ELSE @FilterCategory END))

David Austin wrote:

Just to confirm understanding of these null values, is there any need to test for BOTH these values in an If block?

If(MyText="" or MyText=NullTextIdentifier(), 1, 0)

Also, if creating a SQL query (not aggregate), would I ever need to test for BOTH a null value and 0 value passed in as a parameter from OutSystems within in the SQL?

...WHERE (e.CategoryId = (CASE WHEN ((@FilterCategory IS NULL) OR (@FilterCategory <= 0)) THEN e.CategoryId ELSE @FilterCategory END))

From my understanding of the system (i am new to Outsystems) there is no need to test for both values in an If block.


Also, there is no NULL value so, it will be replaced with the default value, which are the ones Miguel posted. So no need to test both values in SQL query.


I hope this can help you and if i said anything wrong, please feel free to correct me.


Abílio Matos


Hi,

Unfortunately, when using Advanced SQL you must face the difference between Outsystems's NullIdentifier() (that is 0) and the database's NULL that is... NULL. In fact, NullIdentifier is stored as NULL. 0 is not a legal value for a foreign key. When using Aggregate this defference is maintained by OS platform but when you use Advanced SQL you get as pure SQL as is possible.

When I want to filter some table by some identifier that is not mandatory (i.e. can be NULL in the database) and I don't know if I'm looking for some given vale or for a NULL value (i.e. @Identifier in the below example can be NullIdentifier() or not)) I use a syntax like this:

... IsNull(<column>, 0) = @Identifier ....

But: you don't need to worry about the returned value of <column>: after the query is executed the resulting values are maintained by OS and NULLs are replaced  by 0s.

But: when you want to test the value of @Identifier (e.g. to decide whether do some filtering or not) you must test it against 0 (WHERE .... (@Identifier = 0 OR <column> = @Identifier) ....). Note that I haven't used the IsNull function because I know that I'm not looking for NULL values of <column>.

I haven't checked but I'm pretty sure that the same concerns text identifiers (.... IsNull(<column>, '') = @Identifier).

Regards

Tomasz

Also is OrderId = NullIdentifier() and OrderId = IntegerToIdentifier(0) the same when checking for no specified value?  I think both NullIdentifier() and IntegerToIdentifier(0) evaluate to 0 and a Null OrderId would be 0 too.

Answers to all the questions depend of the context. Outside data sources (i.e. Aggregates and Advanced SQLs): yes. Inside data source: no.

Do the following experiment:

  • create an Aggregate
  • add a filter: <entity's id> = NullIdentifier (or: ...NullTextIdentifier() when this Entity has a text identifier)
  • check  the "Executed SQL" property

You will see: ENEntity.[ID] IS NULL (in both cases).

But when the filter is of a form: <entity's id> = IntegerToIdentifier(0) you will see: ENEntity.[ID] = convert(bigint, 0))

When Entity has a text identifier and the filter is: <entit's id> = TextToIdentifier(""), you will see: ENEntity.[ID] = (N'')

BTW: it is always very helpful and informative to check the "Executed SQL" attribute of Aggregates / tab of AdvancedSQLs.

Regards

Tomasz


NullIdentifier() and IntegerToIdentifier(0) are exactly the same thing. Likewise, NullTextIdentifier() and TextToIdentifier("") are exactly the same thing.

leonardo.fernandes wrote:

NullIdentifier() and IntegerToIdentifier(0) are exactly the same thing. Likewise, NullTextIdentifier() and TextToIdentifier("") are exactly the same thing.

I'm sorry, but - no. Check, how OS translates it to an SQL query. "ENEntity.[ID] IS NULL" is not the same as
"ENEntity.[ID] = convert(bigint, 0))".

Regards

Tomasz


Hello Tomasz, you are absolutely right.

There is definitely some magic when NullIdentifier() and NullTextIdentifier() are used inside aggregates. This time, I've done some testings to verify in which circumstances they would be considered to be the NULL database value.


It seems that they are translated into NULL only when they are used as a left-hand or right-hand side of an equality/inequality operator. This will make it work as expected on joins and filters that compare foreign keys.

If you convert it to an integer like IdentifierToInteger(NullIdentifier()), then the code generated falls back to being an explicit 0 instead of NULL. Again, this will make it work if - for whatever incredibly bad reason - you need to do arithmetic with foreign key values.