Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Embedded Select Statement in CR10

Posted on 2004-09-22
4
Medium Priority
?
296 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 28

Accepted Solution

by:
bdreed35 earned 1000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

670 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