Left outer join on tables returns no matches
Hi
I have two tables: one a local table using cache and the other is via an SQL gateway connection to an MSSQL Server via a 64bit ODBC driver (ODBC Driver 17 for SQL Server).
When I try to join them on a date field I don't get any matches, and I know that there are matches, e.g. running both views separately returns resuls for 30/11/2022.
The following statement returns rows for the first table r, but nothing for the table l
select r.*, l.*
from GMMHTIE_PCMIS_Table_PCMIS.vwPCMISNewCasesCount r
left outer join GMMHTIE_PCMIS_Table_Local.vwPCMISNewCasesCount l
on l.created = r.createdate
order by r.createdate desc
The view GMMHTIE_PCMIS_Table_PCMIS.vwPCMISNewCasesCount references the table accessed via the SQL gateway connection, and contains the following:
select convert(date, CreateDate) CreateDate, count(*) cnt
from GMMHTIE_PCMIS_Table_PCMIS.CasesAll
group by convert(date, CreateDate)
The view GMMHTIE_PCMIS_Table_Local.vwPCMISNewCasesCount is a local table and contains the following:
select convert(date, Created) Created
, count(*) cnt
from GMMHTIE_PCMIS_Table_Local.PCMISNewCases
group by convert(date, Created)
Can anyone help?
Product version: IRIS 2021.1