Andy Stobirski · Nov 30, 2022

Left outer join on tables returns no matches


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
0 239
Discussion (5)3
Log in or sign up to continue

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:


I have two tables: one a local table using cache and the other is via an SQL gateway..

Product version: 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.