Recurring Entity - Null vs 0 ParentId

Recurring Entity - Null vs 0 ParentId

  

Hi,

I'm busy building a few POC's on the Outsystems cloud platform (version 10) and ran into a bit of a snag with regards to the Primary Keys not being treated as Null's.

I basically import a hierarchy from excel into a recurring entity 


ID, Number, NodeName, ParentId

1, 1, RootNode1, Null

2, 1.1, ChildNode1, 1

3, 1.2, ChildNode2, 1

4, 1.2.1, GrandChildNode1, 3


Because of the numbering standards we use (1, 1.1, 1.1.1.1, etc), I'm using an Advanced Query to load the records in a properly sorted fashion. With the first run, when using "ParentId is Null" as part of Advanced Query, the RootNode elements are returned as expected. I then update the "Number" field with new numbers, but keeping the value for ParentId set to NullIdentifier() as passed to the server function.

When stepping through the function a second time, the advanced query now returns Zero results. When I test by searching for ParentId = 0 it still returns Zero Results, even though when I print the ParentId to the screen, it says "0"


Any assistance will be greatly appreciated

Rudi

Hi Rude,

There are no actual Nulls in OutSystems, so you can't store a null in an OutSystems Entity. (its possible only on external imported tables)

For numeric attributes, NullIdentifier() is 0 and this is the value you have found in your entity.

For Text null is an empty String, and for Date is #1900-01-01#


Hi Rudi,

Identifiers and hence foreign keys are stored with a NULL in the database, but since the Platform itself doesn't know NULL values, they are converted to NullIdentifier() for (Long) Integer Ids and NullTextIdentifier() for Text Ids, which amounts to 0 and the empty string ("") respectively.

This means that ParentId = 0 doesn't work in SQL as the database actually holds NULL values, but ParentId = IntegerToIdentifier(0) in an Aggregate does work, as that's the same as comparing with NullIdentifier().

I'm not sure why you don't get any results when filtering on ParentId IS NULL. If you don't filter on that, what is the value of ParentId returned?

Fantato wrote:

Hi Rude,

There are no actual Nulls in OutSystems, so you can't store a null in an OutSystems Entity. (its possible only on external imported tables)

For numeric attributes, NullIdentifier() is 0 and this is the value you have found in your entity.

For Text null is an empty String, and for Date is #1900-01-01#


Hi Fantato,

Thanks for the reply. 

Yes, from all the articles that is what I have gathered... but maybe I was a little unclear in my question... the first time when I search the Entity using "is null" as part of an advanced query, it works to get the Root Nodes back... but the 2nd time, not that NOR "= 0" is working, even though the result shows as Zero that I output to check what it is. 


When using a normal Aggregate though and checking for NullIdentifier(), I do get the correct data back, but then I cannot implement my sorting as shown below, which gives me a sorted list for string values according to the hierarchy.

SyntaxEditor Code Snippet

order by CAST('/'+REPLACE({ProductFunction}.[FunctionNumber],'.','/')+'/' as hierarchyID);"



Kilian Hekhuis wrote:

Hi Rudi,

Identifiers and hence foreign keys are stored with a NULL in the database, but since the Platform itself doesn't know NULL values, they are converted to NullIdentifier() for (Long) Integer Ids and NullTextIdentifier() for Text Ids, which amounts to 0 and the empty string ("") respectively.

This means that ParentId = 0 doesn't work in SQL as the database actually holds NULL values, but ParentId = IntegerToIdentifier(0) in an Aggregate does work, as that's the same as comparing with NullIdentifier().

I'm not sure why you don't get any results when filtering on ParentId IS NULL. If you don't filter on that, what is the value of ParentId returned?

Hi Killian,

Thanks for the reply.

You're right that it works fine in the Aggregate when using NullIdentifier(). When I remove the ParentId check and order by the parentId to get the nulls to the top at least, the Advanced Query cuts the test data off after 100 records. When showing the field as part of my app, I obviously see the "0" as expected via Outsystems.

What is weird is that the first iteration works fine with "IS NULL" check, it's only after executing an UpdateEntity action on each record and trying to iterate through the Advanced Query again that the "IS NULL" does not work anymore. 


Hi Rudi,

If the Aggregate works as expected using NullIdentifier(), the IS NULL in SQL should also work. You can even check the Aggregate SQL output to see what it is doing.

As for the sorting, you can add a calculated Attribute to the Aggregate that does the replace as you do in the SQL, and sort on that. Much better than using SQL just for that.

Kilian Hekhuis wrote:

Hi Rudi,

If the Aggregate works as expected using NullIdentifier(), the IS NULL in SQL should also work. You can even check the Aggregate SQL output to see what it is doing.

As for the sorting, you can add a calculated Attribute to the Aggregate that does the replace as you do in the SQL, and sort on that. Much better than using SQL just for that.

Hi Killian

To be honest, I would prefer using an aggregate in any case and I tried the calculated field approach... but remember that the hierarchyId common field in MS SQL is handled as an actual hierarchy, so I was struggling hectically to get the calculated field in the aggregate to sort correctly. I still just end up sorting 1, 10, 2, 3, 4 etc.

The fact that the IS NULL is not working might be a bug of sorts, should I log that somewhere as well?


Hi Rudi,

I've never seen "IS NULL" not working, so I suspect an error in the SQL. Note you can check the actual SQL sent to the database with both Aggregates and SQL, so you can compare what they do.

As for the heirarchy, I missed the CAST. But it might work if you add the "CAST('/'+REPLACE(ProductFunction.FunctionNumber,'.','/')+'/' as hierarchyID)" as a Dynamic Sort to the Aggregate.

Kilian Hekhuis wrote:

Hi Rudi,

I've never seen "IS NULL" not working, so I suspect an error in the SQL. Note you can check the actual SQL sent to the database with both Aggregates and SQL, so you can compare what they do.

As for the heirarchy, I missed the CAST. But it might work if you add the "CAST('/'+REPLACE(ProductFunction.FunctionNumber,'.','/')+'/' as hierarchyID)" as a Dynamic Sort to the Aggregate.

Hi Killian,

Might be the SQL which I'll check if I encounter this again, because you know what? Your solution of adding that as a dynamic sort worked perfectly, so now I can just use the aggregate as per normal.

It's weird that it worked, because I haven't updated my data in any way, so the SQL should work in the advanced query.

Anyway, thank you so much.

Rudi


Kilian Hekhuis wrote:

Hi Rudi,

I've never seen "IS NULL" not working, so I suspect an error in the SQL. Note you can check the actual SQL sent to the database with both Aggregates and SQL, so you can compare what they do.

As for the heirarchy, I missed the CAST. But it might work if you add the "CAST('/'+REPLACE(ProductFunction.FunctionNumber,'.','/')+'/' as hierarchyID)" as a Dynamic Sort to the Aggregate.

Hi Killian,

I was so excited to see the solution working within the Outsystems IDE that I haven't compiled and tested it. The aggregate preview works perfectly, but once I publish and try to access the screen using the sort, I get a message stating there was an internal error which was logged. Inspecting the log in ServiceCenter, I found this: "Invalid syntax in 'Order By' parameter."

I think the fact that the Dynamic Sort is not previewed as the normal sort had an influence in my assumption that it worked.


Hi Rudi,

To test Dynamic Sort in the IDE, you need to add the result to the respective Test Values input. A bit weird, but that's the way it currently works.

As for the "invalid syntax", it seems the Platform engine does some checking on validity of input, unfortunately (the error doesn't seem to come from the actual database), so my "solution" indeed doesn't work. I'm afraid you'll have to resort to SQL for this after all.

Kilian Hekhuis wrote:

Hi Rudi,

To test Dynamic Sort in the IDE, you need to add the result to the respective Test Values input. A bit weird, but that's the way it currently works.

As for the "invalid syntax", it seems the Platform engine does some checking on validity of input, unfortunately (the error doesn't seem to come from the actual database), so my "solution" indeed doesn't work. I'm afraid you'll have to resort to SQL for this after all.

Hi Killian,

Within SQL I still have the issue that the "is null" only works the first time. This is definitely not right, but I'm resorting to a change in my requirement, because that numbering really is not used anywhere as part of my solution, it was just nice to have that as part of a hierarchical display of data.

Thanks anyway for the assistance

Rudi


You're welcome! And like I said, the "IS NULL" should work, but it's difficult to know what's going on without access to the database...

Kilian Hekhuis wrote:

You're welcome! And like I said, the "IS NULL" should work, but it's difficult to know what's going on without access to the database...

One question, how do I see the SQL being generated by the Advanced SQL Query widget?


It's on the Executed SQL tab: