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 descThe 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
Hi @Andy Stobirski
The data type and format of the columns are the same in the both tables?
In both tables, they are the same - they are down a %TimeStamp in the underlying classes.
My first thought for "there should be results but there aren't" is an index that hasn't been built. Try calling %BuildIndices(,1,1) on the local table and see if that makes a difference. (Note - that purges existing indices and locks the extent, so use with caution if there are transactional updates happening to the table.)
It is not entirely clear that you are using Caché or IRIS 2021.1:
The presence/absence of restrictions on the execution of heterogeneous queries depends on this:
Hi Vitaliy - I'm using IRIS 2021.1 - I made a mistake in my original post.