Crystal SQL Designer

[SimonPetherick]

Hi,
I have developed a SQL statement using Crystal SQL Designer. I need to use this new query within an existing report, hence joining the new SQL table to existing tables (using the same table headings). However when I try to do this Crystal crashes. Is this possible to achieve or am I wasting my time?
Thanx.
SP

 

[rhinok]

I'm assuming you're not using CR 9. Unfortunately, you can't add a SQL Query as a data source to an existing report. You must base a new report on the query itself. Once you've done that, you won't be able to use any other data sources.

You should be able to accomplish everything you need in the query, through SQL, but it would be simpler to use a View that returns the same records as the query. A View can be added to an existing report. If you're using CR 9 then you can simply create a SQL Command as a data source within the report.

~Kurt

 

[SimonPetherick]

Your right - I'm still only using CR 8.5....
What is a "View"? How do I use a view that returns the same records as the query?
Cheers.

 

[rhinok]

A View is a database object. You should pass the SQL generated by your query to your DBA. The DBA could then compile the query as a View, which can be selected as a data source within Crystal Reports as if it were a table.

~Kurt

 

[SimonPetherick]

Kurt
Is there any way I can do this myself as most people are away on holidays?
SP

 

[synapsevampire]

If you have permissions you probably can. More importantly, even Crystal does NOT suggest using their SQL Designer these days.

You might want to share what database you're using if you want help developing a View.

Generally it's as simple as saying CREATE VIEW MYVIEW AS <insert the SQL>

-k

 

[SimonPetherick]

This is the SQL query:

Select
dp.dty_id,
dp.sit_id,
dv.reading_date,
(Select
dvs.report_value
from
data_points dps,
data_values dvs
where
dps.dpt_id = dvs.dpt_id
and dps.dty_id = 60
and dvs.reading_date = trunc(dv.reading_date+0.74999)+0.25
and dps.sit_id = 1) HV6AM
from
data_points dp,
data_values dv
where
dp.dpt_id = dv.dpt_id
and dp.dty_id = 60
and dp.sit_id = 1
;

 

[pgtek]

hi

If you have a development database you can create this view
are you using ms sql if so you should be able to use
query analyzer and do it from there. Just paste your query in the views design window
If you have to do it on production databse
and you have permission to do so you can do it on that one
I suggess you try it first on the development database

cheers

pg



pgtek

 

[synapsevampire]

Patrick: Ask someone what database you're using if you don't know.

I'll help, but I can't if you don't know, or don't respond to the absolute basics, such as what database you're using. Consider it akin to saying that you want to alter a program but you're unwilling to share what programming language you're using.

-k

 

[SimonPetherick]

We're using an Oracle database.