Solved

SQL Expression within Crystal Reports

Posted on 2011-03-09
5
493 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
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Trigger 8 37
SQL Select Query help 1 37
Linked Server - SP with Param to VIew 7 22
Checking for column changes SQL 2014 4 21
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

756 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