Written by

Senior Developer at Greater Manchester Mental Health Services

Question Andy Stobirski · Nov 30, 2022

Left outer join on tables returns no matches

#Caché #SQL

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
        leftouterjoin GMMHTIE_PCMIS_Table_Local.vwPCMISNewCasesCount l
            on l.created = r.createdate
orderby r.createdate desc

The view GMMHTIE_PCMIS_Table_PCMIS.vwPCMISNewCasesCount references the table accessed via the SQL gateway connection, and contains the following:

selectconvert(date, CreateDate) CreateDate, count(*) cnt
from    GMMHTIE_PCMIS_Table_PCMIS.CasesAll
groupbyconvert(date, CreateDate)

The view GMMHTIE_PCMIS_Table_Local.vwPCMISNewCasesCount is a local table and contains the following:

selectconvert(date, Created) Created
        , count(*) cnt
from    GMMHTIE_PCMIS_Table_Local.PCMISNewCases
groupbyconvert(date, Created)

Can anyone help?

Product version: IRIS 2021.1

Discussion (5)3

Add reply

Comments

Cristiano Silva · Nov 30, 2022

Hi @Andy Stobirski

The data type and format of the columns are the same in the both tables?

0 0

Andy Stobirski Nov 30, 2022 to Cristiano Silva

In both tables, they are the same - they are down a %TimeStamp in the underlying classes.

0 0

Timothy Leavitt · Nov 30, 2022

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.)

0 0

Vitaliy Serdtsev · Dec 1, 2022

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:

0 0

Andy Stobirski Dec 1, 2022 to Vitaliy Serdtsev

Hi Vitaliy - I'm using IRIS 2021.1 - I made a mistake in my original post.

0 0