Left outer join not working

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

not knopwing your data ...
your join condition seems to conflict your WHERE condition with the rte_cde value
Also be aware at least in crystal XI that joins are not enforced until you set them as enforced or use a table values from both ends of the joins

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.

I noticed that you were using the same table with an alias.
perhaps your data set allows for the table to have multiple rows per ass_num with different values in the rte_cde field?
So you want every row from your table where the yer=2017 and teh cde=1 and you want to also inlcude the 'matched' lev_amt value where the cde=80 if it exists.
The SQL works fine oust side of a crystal command but does not work inside crystal.
You have no additional select criteria in crystal (via the select expert)?

Edited by DBlank - 26 Aug 2016 at 4:28am

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.