Is this Crystal 8 Limitation ?

[krist]

Hi All,
I am using Crystal 8 with MsSql2K.

I need a JOIN like this :

LEFT JOIN INNER JOIN
Salesman---------->Invoice---------->Dispatching

I use Left Join because I want the Salesman to printed whether OR Not he/she has sales/invoices.

BUT Crystal keep on displaying error :
"General SQL Server Error : Check Messages from sql server"

Here is the generated SQL Query :
(which indeed error if run in query analyzer)

SELECT
T05TTDH.Billing, T05TTDH.NoDocu, T05TTDH.TgDocu, T05TTDH.TgKembali, T05TTDH.Penerima, T05TTDH.Cole, T05TTDH.ClSts, T06TTDD.FKKdDocu, T06TTDD.FKTpDocu, T06TTDD.FKNoDocu, T06TTDD.Total, T03FKTH.TgDocu, T03FKTH.TgTempo
FROM
windys.dbo.T05TTDH T05TTDH,
windys.dbo.T06TTDD T06TTDD,
windys.dbo.T03FKTH T03FKTH
WHERE
T05TTDH.Billing *= T06TTDD.Billing AND
T05TTDH.NoDocu *= T06TTDD.NoDocu AND
T06TTDD.FKKdDocu = T03FKTH.KdDocu AND
T06TTDD.FKTpDocu = T03FKTH.TpDocu AND
T06TTDD.FKNoDocu = T03FKTH.NoDocu
ORDER BY
T05TTDH.NoDocu ASC

Is VIEW the only answer for this problem ?

Thanks in advance,
Krist

 

[IdoMillet]

Krist,

Before looking any deeper, once you start an OUTER JOIN all "downstream" joins should also be OUTER JOINS otherwise, the outer join will have no effect.

Change the Invoice--->Dispatching join to Outer Join as well.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

I'd guess that you're getting an ODBC driver error.

Ido: A LO is preserved *Upstream* providing there isn't any Where criteria in the *don\wnstream* tables specified.

-k

 

[IdoMillet]

S.V.,

I was referring to the "Downstream", not "Upstream" issue.
To clarify, here's the initial setup:

LEFT JOIN INNER JOIN
Salesman---------->Invoice---------->Dispatching

Consider what happens to a Salesman without an Invoice.
The Outer Join ensures that the Salesman "survives" that join, but then, since there are no dispatches for Null Invoices, the whole composite record gets chopped.
Hence, the need to apply Outer Join to the "downstream"
join between Invoice and Dispatch.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[krist]

Hi Ido & Synapse,

I am using Native connection from Crystal to Sql2k, so no ODBC.

I have tried :
LEFT OUTER LEFT OUTER
Salesman---------->Invoice---------->Dispatching

STill give same error :
"General SQL Server Error : Check Messages from sql server"

Any other idea,
Thanks