CR 8.5 SL Query Edit

[thendrickson]

I am trying to create a report using Crystal 8.5. I have a fairly simple SQL statement to execute against an Oracle database, but I cannot seem to find a way to paste it into Crystal.

I am not permitted to create a stored procedure in our Oracle database and Version 8.5 has no command object available.

I have no help menu available to me and it has been some time since I dealt with Crystal 8.5 so I am a bit at a loss on this one.

I seem to recall that it was an easy paste into the "show sql query" window, but when I try that Crystal "takes over" and shows all the fields from all the tables.

I cannot seem to find a way to use the sql statement as a datasource.

Any help would be appreciated

 

[lbass]

Why do you need to paste a query in? Is there some reason you must do that instead of using the Crystal GUI?

If you have a compelling reason for doing this, you can use the Show SQL Query area by setting up a report with the same number of fields with the same datatypes as in the query you want to use, and then run the "container" report and in the Show SQL Query area, add a where clause:

where 1 = 0

This will nullify the first part of the query. Then add your query like this:

union all

select <your fields corresponding in datatype and order to the fields identified in the first part of the query>
from etc
where etc

The fields will take on the name of the corresponding fields in the first part of the query.

-LB

 

[thendrickson]

Thanks
But a Union Query must have the same number of fields

The reason as to why I need to use something other than Crystal GUI is that there are 5 tables joined, each with 20 or so fields, of which the report only needs to use a handful

I cannot use a stored procedure or view with the Oracle database as I would do with SQL Server databases due to 'policy'. No command object in 8.5

So, perhaps I should ask How would I set up a report in Crystal 8.5 where I join 5 tables and only return the handful of fields I need instead of 100 + fields (and 3000 records)? Without using a view or a stored procedure.

I have always used stored procedures and /or views in the past, but this time I am forced to do it all in Crystal.

I may be overlooking something very simple perhaps

 

[lbass]

Yes, you have to use the same number of fields, and if you really needed to use the Show SQL Query area here, you would set up a fake report, placing the fields you need in the detail section so that you would generate the right number of fields. However, from what you say, this is not necessary.

Add the five tables to the report, and then link them in the linking expert. Then use the field explorer to find the fields you want to add to the report. Try this first and then see if you need anything more sophisticated to handle your situation.

-LB

 

[thendrickson]

Again, thanks for the attempt to help.

If I must use Crystal GUI and the linking expert, I guess I have no choice.

I have worked with reports (several different report writers) quite heavily, almost always using stored procedures or views although I have used the command object in Crystal 11 when strongly persuaded not to use stored procedures.

I find stored procedures much simpler and cleaner.