Failed to open a rowset? error CRv10

[ScottWood]

Ive searched on the forums and found that a lot of people are getting this error, but i seem to be getting a slight variation, the full error message is:

Failed to open a rowset.
Details: 4200:[OpenRetail]Syntax error in SQL statement(Near'--'>):
It then shows the two tables that are linked that are causing the problem.

Obviously it looks like there is an error in the SQL statement, but the report runs fine in CR v8.5, and if i change one of the links from a left outer join to equal it works.

This has been driving me mad, so if anyone could help it would be greatly appreciated.

Thanks

 

[dialworld]

What database are you reporting off from? Can you paste the SQL you get through the SQL window in CR10?

"Failed to open a rowset" litterally means "you made an error in your SQL, check it out!"

 

[ScottWood]

Hi,

Here is the SQL that the report uses, also i dont think the SQL is incorrect as it runs fine under crystal v8.5.

SELECT OR_DATE.EXT_PERIOD, OR_PORDER_PRODUCT.DUE_DATE, OR_PORDER.A0, OR_PORDER_PRODUCT.NET_LINE, OR_STOCK.RETAIL_PRICE, OR_PORDER_PRODUCT.ORDER_LINE_COMPLETED, OR_PORDER_PRODUCT.PRODUCT_CODE, OR_PORDER_PRODUCT.QTY, OR_PORDER_PRODUCT.UNIT_INVOICE, OR_PORDER.PENDING_STATUS, OR_PORDER_PRODUCT.TOTAL_DISCOUNT, OR_STOCK.CATEGORY, OR_PORDER_PRODUCT.QTY_RECVD
FROM dbases.OR_STOCK OR_STOCK, (dbases.OR_PORDER_PRODUCT OR_PORDER_PRODUCT LEFT OUTER JOIN dbases.OR_DATE OR_DATE ON OR_PORDER_PRODUCT.DUE_DATE=OR_DATE.INTERNAL_DATE), dbases.OR_PORDER OR_PORDER
WHERE (OR_STOCK.A0=OR_PORDER_PRODUCT.PRODUCT_CODE) AND (OR_PORDER_PRODUCT.A0=OR_PORDER.A0) AND OR_STOCK.CATEGORY<>'FREEBIES' AND OR_PORDER_PRODUCT.ORDER_LINE_COMPLETED IS NULL
ORDER BY OR_DATE.EXT_PERIOD, OR_PORDER_PRODUCT.PRODUCT_CODE, OR_PORDER.A0

Thanks

 

[ScottWood]

Ive just looked at the SQL if i open it up in 8.5 and i get this:

SELECT
OR_STOCK.RETAIL_PRICE, OR_STOCK.CATEGORY,
OR_PORDER_PRODUCT.PRODUCT_CODE, OR_PORDER_PRODUCT.QTY, OR_PORDER_PRODUCT.DUE_DATE, OR_PORDER_PRODUCT.QTY_RECVD, OR_PORDER_PRODUCT.NET_LINE, OR_PORDER_PRODUCT.UNIT_INVOICE, OR_PORDER_PRODUCT.ORDER_LINE_COMPLETED, OR_PORDER_PRODUCT.TOTAL_DISCOUNT,
OR_DATE.EXT_PERIOD,
OR_PORDER.A0, OR_PORDER.PENDING_STATUS
FROM
dbases.OR_STOCK OR_STOCK,
dbases.OR_PORDER OR_PORDER,
{ oj dbases.OR_PORDER_PRODUCT OR_PORDER_PRODUCT LEFT OUTER JOIN dbases.OR_DATE OR_DATE ON
OR_PORDER_PRODUCT.DUE_DATE = OR_DATE.INTERNAL_DATE}
WHERE
OR_STOCK.A0 = OR_PORDER_PRODUCT.PRODUCT_CODE AND
OR_PORDER_PRODUCT.A0 = OR_PORDER.A0 AND
OR_STOCK.CATEGORY <> 'FREEBIES' AND
OR_PORDER_PRODUCT.ORDER_LINE_COMPLETED IS NULL
ORDER BY
OR_DATE.EXT_PERIOD ASC,
OR_PORDER_PRODUCT.PRODUCT_CODE ASC,
OR_PORDER.A0 ASC

Which is slightly different, i also put this into to an app to see if there was anything wrong and it falls over on this line:

{ oj dbases.OR_PORDER_PRODUCT OR_PORDER_PRODUCT LEFT OUTER JOIN dbases.OR_DATE OR_DATE ON
OR_PORDER_PRODUCT.DUE_DATE = OR_DATE.INTERNAL_DATE}

I dont really know much about SQL at all, so i dont know what the problem is at all really?

Thanks

 

[dialworld]

This could be a driver issue... What kind of databse is that reporting off from? The line you extracted does look weird, I don't know the database that would use this kind of structure...