Creating a report from a SQL query

[zeeeeek]

How can I convert the following SQL query into a report?
In particular, I don't know how to convert the WHERE portion of the query into a selection formula.

Here is the SQL query:

SELECT P1.CustID, J1.EntryDate, J1.ImplementationStage, C2.CallID
FROM Profile P1 JOIN CallLog C2 ON P1.CustID = C2.CustID
JOIN Journal J1 ON C2.CallID = J1.CallID
WHERE J1.EntryDate IN (
SELECT MAX(J2.EntryDate)
FROM Journal J2
WHERE J2.ImplementationStage IS NOT NULL
AND J2.ImplementationStage <> ''
AND J2.CallID = C2.CallID
)

 

[IdoMillet]

Use Crystal SQL Designer and paste your query there to create your data source for the Crystal report
(or create a View in your database based on this query).

hth,
- Ido ixm7@psu.edu

 

[kenhamady]

CR doesn't support subqueries for WHERE clauses.

You will need to either:1) Use the Crystal SQL designer, and report off of the QRY file that you create
2) Write a stored procedure/view in the database and report off of that. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[MalcolmW]

You could also just rewrite your WHERE clause as a derived table, then add it to the FROM clause. This is a compromise solution - better than a QRY file (anything is) ;-), but it is less transparent SQL than using a view/stored proc. Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net