As mentioned in the title, i have a join in an aggregate, and i want to filter by MyDate = NullDate(), we've been using the NullDate function so I know there are no actual Null values but the typical 1900-01-01 in the database, plus, the type of join also returns 1900-01-01 values but still when i filter by either NullDate() or #1900-01-01 00:00:00# it still returns no records. Is this a bug?


Thanks!

Hi!

Can you show the configuration you have in the Aggregate (joins, filter, etc)?

Cheers.

I guess i can try to elaborate a bit more. As sources I have 2 tables, lets call them Left and Right table

My join is LeftTable With or Without Right table, 

One of my filters is RightTable.ADate = NullDate()


So RightTable when there are no matches with left should return 1900-01-01 00:00:00, and it does, but the filter doesn't seem to match it. I know this particular filter is the issue because when i remove it I get the records I'm supposed to be getting. 

Hi OsCaR. 

In your filter, if you use #1900-01-01 00:00:00# you still does not get the correct results? 

Also, could you put here the executed SQL, so we can see what the aggregate is sending to the database? 

Cheers

In the database, the date will be a date time, but I am not sure the NullDate() returns a datetime when used in an aggregate. If so, this filter would aways return false. 

So yeah, After looking at the executed sql in became kind of obvious what is going on, so when executed the join returns actual NULL values for those without match, therefore you cannot filter by NullDate, guess i'll report this as a bug


SELECT *

FROM ([OSPLATFORMDB].DBO.[LeftTable] a

Left JOIN [OSPLATFORMDB].DBO.[RightTable] b

(a.[id] = b.[id]))

WHERE ((a.[STATUS] = N'A') OR (a.[STATUS] = N'S'))

AND ((b.[UPDATEDON] < (dateadd(day, (-1), (convert(datetime, @qepCurrdatetime, 120))))) OR (b.[UPDATEDON] = (convert(datetime, '1900-01-01'))))

ORDER BY a.[id] ASC

Solution

lol 

Yeah LEFT Join... During execution, if there is no match, the attributes will be NULL...

You have a workaround, though.

Change your filter to:

RightTable.ADate = NullDate() OR RightTable.Id = NullIdentifier()

The NullIdentifier is correctly translated to NULL, If I am not mistaken lol

Solution

Eduardo Jauch wrote:

lol 

Yeah LEFT Join... During execution, if there is no match, the attributes will be NULL...

You have a workaround, though.

Change your filter to:

RightTable.ADate = NullDate() OR RightTable.Id = NullIdentifier()

The NullIdentifier is correctly translated to NULL, If I am not mistaken lol

The workaround works, thanks for the tip and thank you for your help!