problem with 8.5 to 9.0 outer joings

[thomasc72]

Failed to Return Rowset” Details 42000:[Thoroughbred Software][TS ODBC DataServer]Expected lexical element not found.

the above is a statement i get when trying to extract data from an odbc database.
the query done in crystal reports 8.5 works but does not in crystal report 9.0

from what i've read at crystal's site is that the outjoin syntax is not being accepted by the odbc driver that is being used.

i found a post that explained the exact type of problem that i'm having but no solution was added. I'm hoping someone knows of a solution now though.

8.5 generates
SELECT
POCPOHF."PO_NUMB", POCPOHF."VENDOR_CODE", POCPOHF."DATE_ORD",
POCPODF."PO_LINE_NUMB", POCPODF."ITEM_CODE", POCPODF."QTY_ORD",
PODPOIH."APPROV_NAME", PODPOIH."FOB_CODE"
FROM
{ oj ("POCPOHF" POCPOHF INNER JOIN "PODPOIH" PODPOIH ON
POCPOHF."PO_NUMB" = PODPOIH."PO_NUMB")
INNER JOIN "POCPODF" POCPODF ON
POCPOHF."PO_NUMB" = POCPODF."PO_NUMB"}

9.0 generates
SELECT "POCPOHF"."PO_NUMB", "POCPOHF"."VENDOR_CODE", "POCPOHF"."DATE_ORD", "POCPODF"."PO_LINE_NUMB", "POCPODF"."ITEM_CODE", "POCPODF"."QTY_ORD", "PODPOIH"."APPROV_NAME", "PODPOIH"."FOB_CODE"
FROM ("POCPOHF" "POCPOHF" INNER JOIN "POCPODF"
"POCPODF" ON "POCPOHF"."PO_NUMB"="POCPODF"."PO_NUMB") INNER JOIN "PODPOIH" "PODPOIH" ON "POCPOHF"."PO_NUMB"="PODPOIH"."PO_NUMB"

i've tried modifying the link's to have outjoin right and out join left etc but non of the combinations work

thanks in advance

 

[synapsevampire]

You might use the Add Command to create your own data source and paste in your CR 8.5 query (might need a few tweaks).

-k

 

[fredp1]

I've never tried this, but there is a way to get CR to change the syntax that it generates.
There is a document called cr85_left_outer_join.pdf on the BO support web site. Also cr9_left_outer_join.pdf is there.

IF you search the BO web site they will refer you to this document.
Fred

 

[thomasc72]

i've tried changing to each of the supplied types and none of them seem to work. the odbc driver was created with the Dr DeeBee odbc kit by syware. it says it is min+ SQL grammer but not sure what that means

 

[synapsevampire]

Did you try pasting in the CR 8.5 SQL as I suggested?

-k

 

[thomasc72]

if i use the code that was generated by 8.5 it works in 9.0
but i don't want to have to go back to 8.5 everytime 9.0 doesn't build a query right

 

[fredp1]

Which database are you trying to connect to?

 

[thomasc72]

its a buisness basic database
Thoroughbred idol-iv

 

[synapsevampire]

I see, I didn't understand that this was an ongoing concern.

Perhaps there's a more recent ODBC driver that might help, otherwise I'm stumped.

There is a SP 3 service Pack for CR 9, perhaps it will help:

http://support.businessobjects.com/fix/hot/si75/form.asp?file=cr90win_en_sp3.exe

-k

 

[thomasc72]

i've loaded the service pack for CR9 and it didn't help
i've contacted thoroughbred and have the latest driver they supply. i've contacted syware and they point me back to thoroughbred since they developed the driver i'm using.
thoroughbred wants to charge to fix it so i'm seeing what they want to charge and if anyone else might have a clue.

 

[fredp1]

Try and isolate the problem.
Have you tried removing CR from the path
Send the SQL to the db and see if it works.

I've heard sometimes there are some odbc drivers that do not support outer joins.
If the sql works on the db, then you need to see what CR is pasing to the db. You may want to turn on logging on the driver or some sort of util to catch the sql sent to the db.
Fred

 

[fredp1]

I've just played around in CR 9 to get the sql generated to use the 8.5 without any luck.

There must be a reg value that will cause 9 to generate the old code.

You may also want to try the other Outer joins syntax that are available. It may work with your driver.
Note: I thing the cr85_left_outer_join.pdf document may have a error in it, it refers to
[HKEY_CURRENT_USER\Software\Seagate Software\Crystal
Reports\Database Options\Outerjoin]
I Think It should be
[HKEY_CURRENT_USER\Software\Seagate Software\Crystal
Reports\DatabaseOptions\Outerjoin]
(No space in DatabaseOptions)

Also in the Cr9_left_outer_join.pdf type in the quotes "" around the string.
if you cut and paste from the document, the key will not be created correctly in the registry as the "" are a difernt char in the PDF doco.

Good Luck
Fred