cancel
Showing results for 
Search instead for 
Did you mean: 

Is it possible to edit SQL in Crystal Reports XI?

Former Member
0 Kudos

Hi,

Iu2019m VERY new to Crystal Reports and I have a few questions, any assistance would be greatly appreciated.

I am converting reports to Crystal Reports XI from C.R. 7.5 and running into some trouble

When I open a report in C.R.-XI- I get the following error: ORA-25156: old style outer join () cannot be used with ANSI joins. I solved most of the join problems by setting all of the join types to equal in C.R. 7.5, then changing them to left outer joins in C.R. XI, but one or two usually remain. So, Is there a way to edit the SQL statement in Crystal Reports XI? When I paste the SLQ (as is) into SQL navigator it gives me the error, but if I change the old outer join (), it works. That is why I was wondering if it is possible to edit the SQL in Crystal XL.

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hey Dirk,

The quick answer is "Yes" , it's possble to edit sql in Crystal with the SQL Formulas and

the "where" clause by using the Select Expert...but I don't think that's what you mean.

So, are you putting the query into a "Command" table, or are the tables actually being

brought in from the Database Expert. I think you're bringing in the tables from the Database

Expert and redrawing the join lines.

What do you mean by " then changing them to left outer joins in C.R. XI, but

one or two usually remain."

The Panda

Former Member
0 Kudos

Panda,

Thanks for responding.

Iu2019ll try to explain a little better.

When I run a report from Crystal 7.5 it works fine, but if I save it from design mode, then open it in Crystal XI, and try to run it I get that error about old style outer joins. If I view the SQL I see something like this:

Where customer.address_id = addresses.address_id(+); (just an example not actual sql)

So, I did this:

In Crystal Reports 7.5,I go into the visual linking expert, and then change all of the left outer joins to equal joins.

Then in Crystal XI, I go into the database expert, click the u2018Linksu2019 tab, and it shows the tables, I then go to link options and change it to a left outer join.

Then when I view the SQL I see something like this (again not actual sql):

From customer LEFT OUTER JOIN addresses

on customer.address_id = addresses.address_id;

By changing the join types in the database expert it automatically rewrites the left joins,

But for some reason it leaves an old style join in the where clause like this:

AND "AUDITS_V"."ATTRIBUTE_NAME" (+)= 'reasonCode' (actual sql)

Thanks, I know I'm asking the question in a confusing way and it makes it difficult to answer. I appreciate you taing the time to help.

Former Member
0 Kudos

Hey Dirk,

You're explaining it just fine. Crystal is not doing the conversion and I don't know why.

One of these other guys will have to come up with the technical stuff on that one.

In the meantime, take the report in CR 7 where you changed the joins to "normal" joins,

open it in CR 11, don't run it, just save it again back to CR 11. Then open it again and

try to run it.

Also, in CR 11, try going to Database/SetDataSourceLocation. Go to Properties/Use DSN Default Properties and set it to False.

Last thing is: Copy the query from "Show SQL Query" window, make the join changes and

then copy this new query into a "Command" table for your report in CR 11. Go to Set Database Location and Update the Data Source to the new Command table. This should work.

Sorry, that's all I know to try.

The Panda

Answers (0)