Left Join syntax error

[pen81]

I get a "Failed to open rowset" error because the following SQL code is incorrect:

 SELECT `account`.`division`, `groupTopLevelBS`.`division`, `groupNameBS`.`division`, `groupAccountBS`.`division`, `account`.`month`, `account`.`year`, `groupTopLevelBS`.`groupid`, `groupTopLevelBS`.`name`, `groupNameBS`.`groupid`, `groupNameBS`.`name`, `account`.`balancesheet`, `account`.`bs_cumm`, `account`.`title`, `tempBS`.`under`
 FROM   {oj (((`development-accounts`.`groupTopLevelBS` `groupTopLevelBS` INNER JOIN `development-accounts`.`groupNameBS` `groupNameBS` ON `groupTopLevelBS`.`groupid`=`groupNameBS`.`tlgroupid`) INNER JOIN `development-accounts`.`groupAccountBS` `groupAccountBS` ON (`groupNameBS`.`tlgroupid`=`groupAccountBS`.`tlgroupid`) AND (`groupNameBS`.`groupid`=`groupAccountBS`.`groupid`)) INNER JOIN `development-accounts`.`account` `account` ON `groupAccountBS`.`accountNumber`=`account`.`account`) LEFT OUTER JOIN `development-accounts`.`tempBS` `tempBS` ON `account`.`account`=`tempBS`.`account`}
 WHERE  `account`.`division`='L' AND `groupTopLevelBS`.`division`='L' AND `groupNameBS`.`division`='L' AND `groupAccountBS`.`division`='L' AND `account`.`month`=9 AND `account`.`year`=2005
 ORDER BY `groupTopLevelBS`.`groupid`, `groupNameBS`.`groupid`

This is caused by the {oj .... } around the FROM clause. When I copy the SQL into my SQL client and remove the {oj ... } the query works and returns exactly what I need.

I am using CR10 and MySQL 3.23.43, and MySQL ODBC 3.51 driver.

I assume you cannot change the SQL query within Crystal but cannot see another way round this problem.

 

[MisterMo]

If you want to place a query in CR10 you must use the add command option which is available at the beginning of the creation of the report.

Mo