Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Expression within Crystal Reports

Posted on 2011-03-09
5
Medium Priority
?
500 Views
Last Modified: 2012-05-11
Hello all.  I have two tables, CDM_PERSONS and SD_SERVICECALLS
I need to join the two based on the following code:

select hpsd.CDM_PERSONS.PER_NAME from HPSD.CDM_PERSONS
join HPSD.SD_SERVICECALLS
on SD_SERVICECALLS.SER_ASS_PER_TO_OID = CDM_PERSONS.PER_OID
where hpsd.SD_SERVICECALLS.SER_ID = 209599.00


This works like a charm in Toad when running directly against the DB.  However, I am not sure how to do this within crystal reports...unsure of the syntax.  I want to be able to drag this formula/expression directly onto the crystal report.

Any thoughts?
select hpsd.CDM_PERSONS.PER_NAME from HPSD.CDM_PERSONS 
join HPSD.SD_SERVICECALLS 
on SD_SERVICECALLS.SER_ASS_PER_TO_OID = CDM_PERSONS.PER_OID
where hpsd.SD_SERVICECALLS.SER_ID = 209599.00

Open in new window

0
Comment
Question by:nm67304
  • 2
4 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 35086547
How did you build your report?
You built a data source, selected the tables, joined the tables inserted the record selection formula and it doesn't work?

Can you tell us how you're doing this please?
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 35087065
There are 2 ways to do this.

1. Create a new report
    Add the data source
    Select the tables
    In the LINK tab join them by dragging the field from 1 to the linking field in the othe rtable.
    Use the SELECT EXPERT to add the where clause

2.  Create a new report
    Add the data source
    Create a COMMAND using the SQL you have above.

mlmcc
0
 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 1000 total points
ID: 35088851
1)  A SQL Expression field can only return a single distinct value, per record.  Your expression cannot return a recordset

2)  You cannot correlate date in your main report with a SELECT statement against an Oracle database.  It's an Oracle limitation.

You'll probably be best off building the report against a SQL Command and including any subqueries you need so you can drag and drop fields onto the report as necessary.

~Kurt
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35321871
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

824 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