CR8.5 MySQL: ODBC driver does not support the requested properties

[PCSAARON]

I have crystal reports version 8.5, MySQL 4.1.9-standard, and I am trying to get a left outer join to work. The report works great with this select statement:
SELECT * FROM employees

Now, I go into the report and click on the "Verify Database" option from the menu and change the select recordset sql to read:

SELECT * FROM employees AS e left outer join employee_payroll_types as ept on (e.EmployeeNumber = ept.employee_id AND ept.payroll_type_id = 1)

When I click OK, it gives me this error message:

Logon Failed
Microsoft OLE DB Provider for ODBC Drivers:
ODBC driver does not support the requested properties

Does anyone know what the problem could be? I read a few posts about 8.5 needing a registry fix for outer joins, which I applied these:

[HKEY_CURRENT_USER\Software\Seagate Software\Crystal Reports\DatabaseOptions\OuterJoin]
"NoOuterJoinEscSeq"="MYODBC3"
"OJSyntax"="MYODBC3"

It still gives me the same problem, so I am assuming I am doing something wrong. I tried the build option and it shows the query without any problems. Can anyone help shed some light on this??? Thanks in advance.

 

[synapsevampire]

Who told you that you could edit this that way?

CR 8.5 is old and allows for limited editing of SQL manually.

Just use the Crystal Database Expert GUI, add in the other table, select a left outer join, then go into the Report->Edit Selection Formula->Record and place:

{ept.payroll_type_id} = 1

You'll then see the SQL reflected in the Database->Show SQL.

It's generally a bad idea to edit it in the Show SQL, and you'd be best served to create a View on MySQL, if they support them now, it's definitely not a ready for prime time database.

As an aside, Oracle now has a freebie 4GB database called XE.

-k