Records Back.......LOJ

[Shin25]

Hi All

I have a Crystal report (xi rel2) which seems to be gathering data for over 2 hours.
The report consists of 2 tables. Table 'a' is linked to table 'b' with a Left outer join (LOJ).
I have run two sepearte tests on each table with the conditions applied in the main report to
the individual tables and table 'a' brings back 311645 records and table 'b' brings back 315376
records.

The report is currently running and is brining back more than 8 million records......and has been
running for over 2 hours.....I dont understand, I have tried everything I know...

Anyone any ideas?

Many Thanks

 

[woogoo]

I think you'll find that the final result will be in excess of 9Million, as the SQL used to do the join is 'bad'.

Can you post the SQL used?
--

woogoo

 

[Shin25]

The query is still running.....but how can it be more than 9 million....as table 'a' has a max record count of 311695? so in essence a loj should not exceed 311695? as soon as query finishes i will post sql....

 

[woogoo]

What appears to be happening is the SQL is creating a cartesian product where there is 315376 rows for every row in table a.

So 311645 * 315376 = 9.8285353520 ^ 10 so it's a lot more than 9M.

Of course I may very well be wrong, but having been a victim of this 'effect' myself, I always assume the worse.
--

woogoo

 

[Shin25]

thanks for the above woogoo....from your response...and the way I have linked my tables..I should not get more than

311645 * 315376 = 9.8285353520 ^ 10

otherwise it is cartesian product..?

many thanks