CR 8.5 to 9 Failed to open rowset. Outer join...

[traceytr]

Hello.

We use SQL Server 7.0.
I've been scouring these threads for a solution to our problem. Several of our reports won't run due to a "Failed to open rowset" error. The msg states, "Outer join operators cannot be specified in a query containing a joined tables." The original query in 8.5 did contain outer joins, but after opening in CR9 the sql is changed and is not correct. It does not contain any outer joins in CR9. As an experiment, I went into the Database Expert Links tab and reset the joins making them equal joins. Still, I get this message about "Outer join operators". I tried using the Add Command object, but I have not had any success getting this to work. When I try to replace the main table with a Command object in Set DataSource Location, I paste in the SQL, click OK, and nothing has changed. This report has a main body and two subreports -- My knowledge of Crystal is limited, so forgive my lack of correct term usage. When we went from 8.0 to 8.5, we didn't have these types of issues.

I read some threads about changing the registry to fix this. Is this the only way?

Thanks,
Tracey

 

[pelajhia]

When I try to replace the main table with a Command object in Set DataSource Location, I paste in the SQL, click OK, and nothing has changed." -
I am pretty sure you can not do that, unfortunately.

In crystal reports, go to database>show sql query; This shows what is really being passed to the db. If you can't see anything wrong, paste it here so others can check it out.

 

[traceytr]

Thank you for your response.

In Crystal 9, the Show SQL Query window is no longer editable.

Our solution, which I cannot claim credit for, was to open the report in CR 8.5, modify the SQL in the Show SQL Query window (it is editable in 8.5), save and close the report, then open in CR 9.

This SQL did not work:
select a bunch of fields
FROM
mp.dbo.Report_74 Report_74,
mp.dbo.report_74_IB report_74_IB
WHERE Report_74.COLOR_NO *= report_74_IB.IBNO
and Report_74.style *= report_74_IB.style
and Report_74.session_id *= report_74_IB.session_id


This changed SQL worked:
select a bunch of fields
FROM
mp.dbo.Report_74 Report_74 left outer join
mp.dbo.report_74_IB report_74_IB on
Report_74.COLOR_NO = report_74_IB.IBNO
and Report_74.style = report_74_IB.style
and Report_74.session_id = report_74_IB.session_id

From what I've been reading so far, the upgrade to CR 8.5 to 9 is not going to be as smooth as going from 8.0 to 8.5.

Tracey

 

[synapsevampire]

The upgrade from CR 8.5 to CR 9 was substatial, impactful among the changes is real SQL.

You shouldn't try to replace the SQL for the main table by using Add Command, it should be replacing the SQL for a report (perhaps you meant the main report?).

Not sure why you'd get an error stating something about outer joins if there aren't any, doesn't make sense to me either.

I would guess that the SQL might have been manually edited in CR 8.5, which might make CR 9 hiccup.

As suggested, you should paste the SQL in to this post (they weren't suggesting that you edit it).

You might try copying and pasting the SQL from the CR 8.5 query to the CR 9 Add Command.

Anyway, you have a temporary fix, but I always try to learn why something doesn't work.

-k

 

[pelajhia]

these two joins might have been the problem:
WHERE Report_74.COLOR_NO *= report_74_IB.IBNO
and Report_74.style *= report_74_IB.style

However by removing them you will be getting a different record set.
With
Report_74.COLOR_NO *= report_74_IB.IBNO
you were requesting all rows from report_74_IB regardless of whether there was a match found in report_74.
Removing the asterisk will result in only rows from report_74_IB being returned if there is a match in report_74.
So while this got rid of your error, I don't think it is a good solution.

Synapsevampire is usually right on, however, so maybe I am off base here...?

While you may be able to get around this using a SQL Command instead of grabbing tables and having Crystal build your query, this would be laborious for multiple reports, and it really doesn't explain why you are getting the error to begin with.

Maybe crystal would prefer:
Where report_74_IB.IBNO=Report_74.COLOR_NO(*)
and report_74_IB.style = Report_74.style(*)
- NOT sure of the SQL syntax here for your db; I am used to Oracle.

 

[traceytr]

>Removing the asterisk will result in only rows from
>report_74_IB being returned if there is a match in
>report_74.
>So while this got rid of your error, I don't think it is a
>good solution.

The solution above returns the correct records. Note the left outer join syntax in the FROM clause.

>(perhaps you meant the main report?).

Yes, the main report. Oops.


>As suggested, you should paste the SQL in to this post
>(they weren't suggesting that you edit it).

Right. Oops again

>My knowledge of Crystal is limited, so forgive my lack of
>correct term usage.

(See?)

>I would guess that the SQL might have been manually edited
>in CR 8.5, which might make CR 9 hiccup.

That's probably true. There was a time when we freely edited the SQL, but we found that it wasn't a good practice so we stopped. This report is so old -- probably was manually edited.

>...I always try to
>learn why something doesn't work.

I agree, however I'm up against a very tight deadline, and there's just not time now. If, as suggested, Crystal was confused because the SQL was manually edited, we will probably run into this again soon.

Thank you VERY much for your help.

I'm developing my first "from scratch" report in CR9, and I haven't encountered any problems yet. I can see where the repository will be very useful.

Tracey