Using the same table under different aliases

[Bertrum]

Hello,

I'm having problems with CR 8.5 when using the same table twice in the same report. Example:

On our property database (Interbase 5.6, accessed via INTERSOLV ODBC Driver), all our streets are held in the same reference table (STREET). For each property (LPI), there are two Street ID's (one for dependant thoroughfares, another for the main road) which link onto the street_id on the street reference file. When linking each Street ID to a copy of the STREET table (one with alias STREET, the other is D_STREET), the report will not bring data back for either street. I'm using left-outer joins (either field can contain a null value) so it's not a problem with data matching. But either one will work fine on it's own. I've had the same problem using tables this way before, and it very rarely works correctly. Another situation, where the key fields cannot contain null values, caused the same data to be retrieved from both reference files - even though the key codes were different. It seems to be down to luck whether it works or not.

Is this a known problem, and if so does anyone know how to get round it? Just one of a number of flaws I've been finding in this software lately. Sorry for he poor wording, but it's a weird thing to try and explain

 

[lbass]

So you have your links set up like the following?

LPI STREET
LPI.MainID---------left join-->StreetID
LPI.DepID
\left join
\ STREET_1
-> StreetID

-LB

 

[Bertrum]

Yes, that's basically it.