SQL Expression - max date

[dazum]

I am using CR 11 and trying to create a SQL Expression to get a maximum date for a person_id, but the field for the person_id and the field for the date are in different tables (i.e. the Person_Id is in the Intake_Participant table and the Decision_Date is in the Intake table)
Below is what I'm trying to accomplish with a SQL Expression;

394335 (Person_ID Group)
Decision_Date -----------------SQL EXPRESSION
2/14/2009 --------------------- 4/25/2010
4/25/2010 --------------------- 4/25/2010
1/22/2010 --------------------- 4/25/2010

384416 (Person_ID Group)
Decision_Date -----------------SQL EXPRESSION
6/21/2012 --------------------- 6/21/2012
5/2/2011 ---------------------- 6/21/2012
9/18/2011 --------------------- 6/21/2012
Is it possible to create a SQL Expression to accomplish what is shown above?

 

[Davest11]

dazum - Did you surround the entire statement with parentheses? The only time I've seen that error is when I forgot that.

 

[dazum]

Davestll,
I did not have the SQL Expression enclosed in parenthesis, but when did enclose it I started getting "invalid identifier" - "INTAKE_PARTICIPANT"."PERSON_ID"

 

[Davest11]

but when did enclose it I started getting "invalid identifier" - "INTAKE_PARTICIPANT"."PERSON_ID""

Yes, that's what I expected. : / I've searched many forums, and found many threads on this topic, and NONE have ever come to a conclusion...they always just peter out.

What version of Oracle are you using? My vendor is currently still using v9i, and I'm hopeful that an upcoming upgrade might resolve the issue.

 

[dazum]

Davest11,
I'm using Oracle v 10g

 

[Davest11]

Way to wreck my morning. ; )

It seems to me that, when the SQL Expression is run before it's saved, it isn't "aware" of the rest of the report context. Thus, references to the tables that are in the main report aren't recognized as valid. I suspect that, if there was a way to stop Crystal from running the expression when it's saved, it would work fine when it was actually executed as part of the full SQL statement that's passed when the report is run.

 

[PeterDimitrov]

I guess this is an issue related to Oracle. As a workaround you can try to create a view like this:

SELECT sqlip."PERSON_ID", MAX(sqli."DECISION_DATE")
FROM "INTAKE" sqli
inner join "INTAKE_PARTICIPANT" sqlip on sqli."INTAKE_ID" = sqlip."INTAKE_ID"

Then use this view in your report without using a SQLexpression.
You need to have permissions to create views. If you cannot create views a command will be a solution too, but you might need to rewrite the report

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.