Solved

Embedded Select Statement in CR10

Posted on 2004-09-22
4
291 Views
Last Modified: 2006-11-17
Hi,

We're aiming to embed a select statement into a crystal report that has been created just using the database expert. We were wondering whether this is possible using crystal itself or whether we need to write the sql query first and then base a report on the query.

Our sql would be something like:

(select fkey from notes where created = max(created).....).  and we want to in bed this as a column in another sql statement created by crystal using the database expert.

Thanks

Steve
0
Comment
Question by:sbarwood
4 Comments
 
LVL 28

Accepted Solution

by:
bdreed35 earned 250 total points
ID: 12122115
As long as your database/driver allow it, you can use a SQL Expression to do this.

Create a new SQL Expression in the Field Explorer of the report that you wanted to add it to.  If "SQL Expressions" is not available in the Field Explorer, than the database/driver doesn't support it.
Paste in your SQL in to the SQL Expression editor.  Make sure that you surround the SQL statement in parenthesis.
Just remember that the SQL Expression can only return one value when it executes.
Save and Close the editor.
Drag the SQL expression from the Field Explorer to the report canvas and Crystal will add it to the SQL it generates for the main report.

If you can't use SQL Expressions, you could always create a SQL Command.
The SQL Command allows you to use pure SQL  that you have written or generated, and uses it as a datasource just like a table or view.
To access a SQL Command, open the Database Expert.
Below an active connection to a datasource, you should see "Add Command", amongst other things.
Double click it to add it to the right list.
In the dialog that opens up, you can paste your SQL here.
When you click OK it will test the SQL works.
Once you close out of the Database Expert, you will see the command available the same as a table or view.
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12122211
Bdreed is right. There is no addition for that. But still I will go with a stored procedure in the db. It is simple, fast and puts your logic in the db.
Regards
Emre
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question