Crystal 10 outer joins

[deathstarvulcan]

Hi,

I'm new to Crystal reports (version 10) and hope that some clever person can assist. My problem is that I have found that I cannot use outer joins when setting up a report. I can set the join property but once the report is generated i get an error. Crystal works fine when only using inner joins.

The error message get is :

Failed to open rowset, details:23000:you have an error in your SQL syntax; check that the corresponds to your MySQL server version for the right syntax to use

A copy of the SQL generated is below if this helps....


SELECT `detailTbl`.`debtor_no`, `detailTbl`.`clnt_no`, `detailTbl`.`last_pmt_date`, `detailTbl`.`return_date`, `detailTbl`.`return_code`, `detailTbl`.`current_balance`, `acctudef039Tbl`.`Int_CO_Dt`, `cltrTbl`.`co_code`
FROM {oj ((`crsdata`.`detailTbl` `detailTbl` LEFT OUTER JOIN `crsdata`.`acctudef039Tbl` `acctudef039Tbl` ON (`detailTbl`.`debtor_no`=`acctudef039Tbl`.`debtor_no`) AND (`detailTbl`.`dd_loc`=`acctudef039Tbl`.`dd_loc`)) INNER JOIN `crsdata`.`masterTbl` `masterTbl` ON (`detailTbl`.`debtor_no`=`masterTbl`.`debtor_no`) AND (`detailTbl`.`dm_loc`=`masterTbl`.`dm_loc`)) INNER JOIN `crsdata`.`cltrTbl` `cltrTbl` ON `masterTbl`.`coll_unit`=`cltrTbl`.`coll_unit`}

As always any help provided i will be eternally grateful for.

Merry Christmas

 

[Turkbear]

Hi,
To debug, run that query directly in MySql and see what error message you get.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Madawc]

If you can run SQL, consider putting the data together in stages, and within SQL. You seem to be pushing the limits of what Crystal can do, with a single select. In SQL attached to Crystal as an SQL Command, you can do a series of Select statements and use temporary files.

If you don't have access to SQL as such, then consider simplifying the links and putting some of the functions in a subreport at detail level. Inefficient, but it may be your only option.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[deathstarvulcan]

hi,

thanks for your help. I dont have access to SQL and very little IT related help.

I think that I may be missing the point, the tables and links are quite simplistic, easily replicated in something simple such as MS Access. Even if i only had two tables and tries an single link right outer join i would still get the same message, so it;s something within crystal.

I have read about a bug fix with this version of crystal that involes editing the registry key, which i have done which has slightly improve the links i'm able to make but clearly has not resolve the probllem.

Any ideas?

As always huge thanks