Crystal Reports/ Progress - Error w/ 3 tables thread

[JPPowers]

Hi

I am using CR9 and OpenLink ODBC driver to connect to a Progress 8.2B DB. If I use more than 2 tables in a report, then the SQL generated by Crystal fails with the error "Failed to open a Rowset" and "Unable to understand after declare". This is not the left outer join problem but the fact that Progress 8.2 does not like the parentheses that Crystal generates in the SELECT statement when the 3rd table is added. Testing the Crystal generated SQL in a native Progress enviorment fails in the same manner, but runs properly if all the parentheses are removed from the SQL statement. ANY help would be GREATLY appriciated.

Joe P

 

[Lee Curzon]

Another knowledgebase entry.

NB: it does not specify your configuration, so proceed with caution. In particular those registry codes entered look a little but too obscure to me...

In other related KB entries, Progress suggests contacting Crystal Reports Tech Support.

http://tinyurl.com/ewdqj

KB P53286
Title: "Error 247 when reading a multiple (3+) table JOIN in Crystal Reports 9 via SQL-89"

Status: Unverified

FACT(s) (Environment):

Progress 8.3E

FACT(s) (Environment):

Progress 9.1D

FACT(s) (Environment):

MERANT 3.70 32-BIT Progress SQL-89

FACT(s) (Environment):

MERANT 4.0 32-BIT Progress SQL-89

FACT(s) (Environment):

SEQUELINK 4.0 32-BIT Progress SQL-89

SYMPTOM(s):

Error 247 when reading a multiple (3+) table JOIN in Crystal Reports 9
via SQL-89

** Unable to understand after -- "DECLARE". (247)

Querying 2 or less tables works properly

CAUSE:

JOIN syntax generated by Crystal Reports 9 is not handled by the ODBC
driver

FIX:

The following key must be created in the Registry.

Please take a backup of your Registry before making any changes.

1) Create a text file and change the extension to .reg

2) Open the file in NotePad and copy and paste the BOLD text into it:

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Crystal Decisions\9.0\Crystal
Reports\Database\QueryBuilder\JoinBuilder]

"StarEqualJoinBuilder"="IVSLK13, IVPRO16, IVPRO17"

3) Save the file and run it

 

[JPPowers]

Thank you Lee. The "StarEqualJoinBuilder" reg entry was the fix. The funny thing is I had checked the KB before coming here but my search did not find the KB article you directed me to. After making this reg entry CR generates SQL that works. Specificaly:

This SQL after the reg entry works:

SELECT customer1.name, ro_inv.roinv#, cunit1.lic#
FROM SSM3.ro-inv ro_inv, SSM3.customer customer1, SSM3.cunit cunit1 WHERE (ro_inv.cust#=customer1.cust#) AND ((ro_inv.unit#=cunit1.unit#) AND (customer1.cust#=cunit1.cust#))


Before the reg entry this was the SQL, and did not work:


SELECT customer1.name, ro_inv.roinv#, cunit1.lic#
FROM (SSM3.ro-inv ro_inv INNER JOIN SSM3.customer customer1 ON ro_inv.cust#=customer1.cust#) INNER JOIN SSM3.cunit cunit1 ON (ro_inv.unit#=cunit1.unit#) AND (customer1.cust#=cunit1.cust#)

Thank you very much

Joe Powers