Hi,
We have an aggregate that is using 2 entities and E1"with or without" E2 join and some records of E1 does not have records on E2 so values on aggregate results are null. However if I'm adding a filter E2.date Attr= NullDate() no records return.
What am I missing on this scenario?
You need to change the filter to "E2.date Attr= NullDate() OR E2.Id = NullIdentifier()".
Hey,
What records were you expecting to see? Have you tried moving your condition to the JOIN?
Hi, just adding to Tiago's answer.
The problem is that when you use something like AttDate = NullDate(), the SQL generated will be like this:
(ENEntity.[ATTDATE] = (convert(datetime, '1900-01-01')))
As you can see, in the database, any NULL date in the right part of the Left Join will fail this condition.
That's the reason why you need to use an OR Entity.Id = NullIdentifier() in the filter to take into account the inexisting records.
Cheers.
Thank Tiago Gomes Correia that works!
Afonso Carvalho I've expected to see records from EN1 that doesn't have related records on EN2 or if the have they should have E2.dateAttr = NullDate()
probably problem is in the generated SQL
...
AND ((EN2[DELETEDON] = (convert(datetime, '1900-01-01'))) OR (EN2.[ID] IS NULL))
and if there is no EN2 record this filter should be generated as
AND ((EN2[DELETEDON] = IS NULL))
... ...
Eduardo Jauch
I would expect ServiceStudio add "OR (EN2.[ID] IS NULL)" automatically on generated query if join condition in "with or without" ...or at least show a warning that there might be a conflict in join condition and filters.
Mykola (Nick) Tkachenko wrote:
Here there is a problem.
As the platform does not insert NULL, but on a Left Join the NULL may be there, the platform can't really identify (I think), if you are trying to fetch records where the right side of the join was not found (no matches), or records that exist, but the data was not defined (NULL DATE).
Not sure if there is really a solution.
But for sure, when using NullSomething() in a LEFT JOIN, the platform could at least issue a warning with the workaround.
Cheers
Eduardo Jauch agree. because it is a bit misleading as aggregate results preview is actually showing 01/01/1900 00:00:00 for E2.dateAttr while there is no records. It should show null or other message but 01/01/1900 00:00:00 is actually misleading and incorrect result.
Also I was wondering if this also applicable to Boolean attributes. meaning if there is filter for "False" would it filter out null values same as null date in discussed example or not.
Personally, I would add all filters as join conditions in case of a With or Without. So the "E2.date Attr = NullDate()" goes together with the join condition (with an AND).
Kilian Hekhuis good point! thank you.
Is there any benefit in terms of performance or error prone?
Hi Mykola,
I don't think so, it might even be slighly faster. Personally I prefer it that way, as you won't experience the problems you encountered.