Crystal 8.5 and left joins

[Jedak]

Hello all,

I have a question regarding Crystal 8.5 and left joins. I am using Crystal against a MS SQL 2000 Database using the native MSSQL OLEDB driver (I think, it's under the More Data Source section when setting the Database location). Notes: the dates are parameters

When I show the SQL query I get this

SELECT
vw_1.ID,
vw_1.DN,
vw_1.DR,
vw_1.DP,
vw_1.DE,
vw_1.RDE,
vw_1.CA,
Table1.NF,
Table1.NL,
Lookup.LookupValue,
Table2.NameFirst,
Table2.NameLast
FROM
DBXX.dbo.vw_1 vw_1,
DBXX.dbo.Table1 Table1,
DBXX.dbo.Lookup Lookup,
DBXX.dbo.Table2 Table2
WHERE
vw_1.Table1ID *= Table1.Table1ID AND
vw_1.LookupID = Lookup.LookupID AND
vw_1.Table2ID = Table2.Table2ID AND
vw_1.DR >= "Jun 4 2004 00:00:00AM" AND
vw_1.DR < "Jun 5 2004 00:00:00AM"

I get a error when I try to view the report in Crystal. If I change to an inner join everthing works, but the results may not be the desired results. If I take this query and run it in a query tool against the database I get an error as well. If I rewrite the query in SQL-92 like this

SELECT
vw_1.ID,
vw_1.DN,
vw_1.DR,
vw_1.DP,
vw_1.DE,
vw_1.RDE,
vw_1.CA,
Table1.NF,
Table1.NL,
Lookup.LookupValue,
Table2.NameFirst,
Table2.NameLast
FROM DBXX.dbo.vw_1 vw_1
LEFT OUTER JOIN DBXX.dbo.Table1 Table1 ON vw_1.Table1ID = Table1.Table1ID
LEFT OUTER JOIN DBXX.dbo.Lookup Lookup ON vw_1.LookupID = Lookup.LookupID
LEFT OUTER JOIN DBXX.dbo.Table2 Table2 ON vw_1.Table2ID = Table2.Table2ID
WHERE
vw_1.DR >= "Jun 4 2004 00:00:00AM" AND
vw_1.DR < "Jun 5 2004 00:00:00AM"

It works fine in the query tool. So I'm wondering if Crystal is screwing up the syntax and if their is a patch for Crystal 8.5 so it will write in SQL-92?

Thanks for any help.

 

[Jedak]

Sorry,

The second SQL statement should be

SELECT
vw_1.ID,
vw_1.DN,
vw_1.DR,
vw_1.DP,
vw_1.DE,
vw_1.RDE,
vw_1.CA,
Table1.NF,
Table1.NL,
Lookup.LookupValue,
Table2.NameFirst,
Table2.NameLast
FROM DBXX.dbo.vw_1 vw_1
LEFT OUTER JOIN DBXX.dbo.Table1 Table1 ON vw_1.Table1ID = Table1.Table1ID
INNER JOIN DBXX.dbo.Lookup Lookup ON vw_1.LookupID = Lookup.LookupID
INNER JOIN DBXX.dbo.Table2 Table2 ON vw_1.Table2ID = Table2.Table2ID
WHERE
vw_1.DR >= "Jun 4 2004 00:00:00AM" AND
vw_1.DR < "Jun 5 2004 00:00:00AM

 

[synapsevampire]

Please post any errors, not "it said something" and didn't work, it's prett hard to diagnose otherwise and would be the first thing any tech support would ask you.

You could try 2 different approaches:

Change the connectivity to ODBC

or

Copy and paste the good SQL into an ADO connection (Under More Data Sources)

You might also get around this by applying the service pack, worth a shot:

http://support.businessobjects.com/fix/hot/si75/?ref=default.asp_selectlist

-k

 

[Jedak]

The error in Crystal is "General SQL Server error: Check messages from the SQL Server". Followed by "Error detected by database DLL"

The error in the query tool (which is MS SQL Query Analyzer) is "Server: Msg 4424, Level 16, State 1, Line 1
Joined tables cannot be specified in a query containing outer join operators. View or function 'DBXX.dbo.vw_1' contains joined tables."

The error from the the query tool leads you to believe you can't join on views that have joins in them. If I'm reading it correctly. However, when using the SQL-92 syntax it works fine.

Unfortunately I don't believe ODBC is an option. I tried the ado and it works, but I could just as easily change the whole thing to a single view instead.

I'd like to install the service pack, but my boss is not eager to try it. ~shrug~

Also, I did run across this

http://support.businessobjects.com/communityCS/TechnicalPapers/cr85_left_outer_join.pdf

on the Crystal support site. Anyone have experience with trying this?