Question
· Nov 30, 2022

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

It is not entirely clear that you are using Caché or IRIS 2021.1:

#Caché

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: