LEFT JOIN DOESN'T work ??

[krist]

Hi All,

I am using Crystal Report 8.0 with sql2k, Native Connection, I found that It
Doesn't support LEFT JOIN (very well).

Here is the case :

OrderHeader->LeftJoin->OrderDetail->(Works)

OrderHeader->LeftJoin->OrderDetail->LeftJoin
->Produk --> DOESN'T WORKS

OrderHeader->LeftJoin->OrderDetail->InnerJoin
->Product --> DOESN'T WORKS

So that I have to create VIEW for this and use the VIEW from Crystal Report.
I want to avoid this everhead of creating view For All Transaction Table.

Has anybody solved this problem ?

TIA,
Krist

Crystal Generated Query :
SELECT
T01QUOH.KdRegs, T01QUOH.KdDocu, T01QUOH.NoDocu, T01QUOH.StDoc1,
T02QUOD.Agency, T02QUOD.SparePart, T02QUOD.Kuantum, T02QUOD.Price,
M51AGCY.Nama
FROM
AIRINDONEW.dbo.T01QUOH T01QUOH,
AIRINDONEW.dbo.T02QUOD T02QUOD,
AIRINDONEW.dbo.M51AGCY M51AGCY
WHERE
T01QUOH.KdRegs *= T02QUOD.KdRegs AND
T01QUOH.KdDocu *= T02QUOD.KdDocu AND
T01QUOH.TpDocu *= T02QUOD.TpDocu AND
T01QUOH.NoDocu *= T02QUOD.NoDocu AND
T02QUOD.Agency *= M51AGCY.Agency

 

[synapsevampire]

Try the CR ODBC connectivity if this proves true. I've used CR 8 with SQL Server and can't recall this scenario.

Generally when people state that a LO isn't working, it's because they don't understand that by adding a filter to the child side of a Left Outer they in essence nullify the LO unless they also use a NULL check.

I suggest that all developers (CR or otherwise) use Views anyway, as they provide an insulation layer between the database in case there are underlying structure changes. If a column type changes, etc., correct the View and the
reports and applications based on the View will work without modifying them.

-k

 

[pjgross]

I don't know "sql2k, Native Connection" but
Your 'Where' clause looks suspect. I whould think that you would have a left join from your header to your detail not the other way. When you have two left joins from the same record (not to the same record) in Cyrstal problems happen. You should be able to
YOur Code:
WHERE
T01QUOH.KdRegs *= T02QUOD.KdRegs AND
T01QUOH.KdDocu *= T02QUOD.KdDocu AND
T01QUOH.TpDocu *= T02QUOD.TpDocu AND
T01QUOH.NoDocu *= T02QUOD.NoDocu AND
T02QUOD.Agency *= M51AGCY.Agency

TRY: (equal and right join)
T01QUOH.KdRegs = T02QUOD.KdRegs AND
T01QUOH.KdDocu = T02QUOD.KdDocu AND
T01QUOH.TpDocu = T02QUOD.TpDocu AND
T01QUOH.NoDocu = T02QUOD.NoDocu AND
T02QUOD.Agency =* M51AGCY.Agency
OR: (right join)
T01QUOH.KdRegs =* T02QUOD.KdRegs AND
T01QUOH.KdDocu =* T02QUOD.KdDocu AND
T01QUOH.TpDocu =* T02QUOD.TpDocu AND
T01QUOH.NoDocu =* T02QUOD.NoDocu AND
T02QUOD.Agency =* M51AGCY.Agency

I usually code like:
header =* detail
detail =* product

PERRY