SQL Expression within Crystal Reports

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

nm67304Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mlmccConnect With a Mentor Commented:
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
 
LIONKINGCommented:
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
 
Kurt ReinhardtConnect With a Mentor Sr. Business Intelligence Consultant/ArchitectCommented:
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
 
mlmccCommented:
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
All Courses

From novice to tech pro — start learning today.