Hi,

I'm using CR 12.7.1.2131 connecting to an Informix DB.

I wish to use a left join however it does not work.  I initially used the GUI and changed the link options but that did not work.

I have instead created a command as follows:

SELECT aurttran.rte_cde, aurttran.rte_yer, aurttran.ass_num, aurttran.lev_amt, aurttran_1.rte_cde, aurttran_1.rte_yer, aurttran_1.lev_amt
FROM authority:auth.aurttran aurttran  LEFT JOIN authority:auth.aurttran aurttran_1
ON aurttran.ass_num=aurttran_1.ass_num AND aurttran_1.rte_cde=80 AND aurttran_1.rte_yer=2017
WHERE aurttran.rte_yer=2017 AND aurttran.rte_cde=1
ORDER BY aurttran.ass_num

The results however appear as if it is an inner join.  If I copy and paste the above statement into another sql program (i.e. QTODBC), it works perfectly.

I thought it may have something to do with nulls so I ticked the Convert Database NULL values to default box to no effect.

Does someone know what may be causing the results to appear incorrect?

thanks

jc

Thanks for the reply DBlank.

You'll note that I am only using one table, referencing itself.  It may have looked confusing with the table names I had before.

SELECT firsttbl.rte_cde, firsttbl.rte_yer, firsttbl.ass_num, firsttbl.lev_amt, secondtbl.rte_cde, secondtbl.rte_yer, secondtbl.lev_amt
FROM aurttran firsttbl  LEFT JOIN aurttran secondtbl
  ON firsttbl.ass_num = secondtbl.ass_num AND secondtbl.rte_cde = 80 AND secondtbl.rte_yer = 2017
WHERE firsttbl.rte_yer = 2017 AND firsttbl.rte_cde = 1

I have used all combinations of Enforced Join in the Link Options to no effect.

Ha!  I noted there was an update to my version of CR bringing it to 12.7.3.2302.  After applying this it works!

I should have checked that first thing.  I can't find any release notes to say this was a specific issue that was fixed or whether it just kicked something into gear.

Sorry for wasting your time DBlank and thanks for your responses.