Solved

SQL Expression within Crystal Reports

Posted on 2011-03-09
5
494 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
[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
  • 2
5 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 100

Accepted Solution

by:
mlmcc earned 250 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 250 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 100

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

734 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