Solved

SQL Expression within Crystal Reports

Posted on 2011-03-09
5
482 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now