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?

 

[briangriffin]

How are you associating the Decision Date with the Person ID now?

I'm assuming there are some joins involved. Regardless, a command object would work for you rather than a SQL Expression:


select a.personID, max(b.decisiondate) as maxdate

from PersonTable a inner join DecisionTable b
on a.somefield = b.somefield

group by a.personID


Then left outer join the command to your existing table and add the MaxDate field to the details section. Although if you're going to this trouble you may want to consider creating one command to return the entire dataset, including the max date.

 

[pmax9999]

I don't recommend creating a Command and joining it to other tables. If you are going to create a Command it needs to provide the entire data set.

I think the SQL Expression is a thoroughly workable solution, but code is Database specific. Please advise Database type and the Table names and Column names involved (including the columns on which the tables join) and someone should be able to help (I am familiar with SQL Server and Oracle, but not nuch beyond that).

If you have a piece of code that you have been working with, post that as well.

Cheers
Pete.

 

[dazum]

I am using Oracle as the database. I forgot to mention that there is an Intake.Id field that is common to both the Intake table and the Intake_Participant table. I would think that would be the join. The Person_Id field is in the Intake_Participant table and I would like the SQL Expression to show the latest Decision_Date for each Person_Id. The Decision_Date is in the Intake table.
I've attempted using the following code:
(
SELECT MAX("Decision_Date")
FROM "Intake" i
left inner join "Intake_Participant" ip on
i."Intake_ID" = ip."Intake_ID"
WHERE ip."Person_ID" = "Intake_Participant"."Person_ID"
)
with this code I get an error: invalid table identifier "Intake_Participant"
I hope this gives additional info and help clarify what I am hoping to get from the SQL Expression.

 

[pmax9999]

I see from these forums that many people seem to take a slightly different approach to SQL Expressions. My usual approach is to write it as I would if I were doing it outside of Crystal (ie, with no use of quotation marks) except for anything that references one of the report tables themselves (ie, to limit the returned data to a single record associated with a report field).

My approach would therefore be as follows:

(
SELECT	MAX(Decision_Date)
FROM 	Intake 				i
	inner join Intake_Participant 	ip on i.Intake_ID = ip.Intake_ID
WHERE 	Person_ID = "Intake_Participant"."Person_ID"
)

I don't have any way to test for syntax errors so apolgies if I have missed something. Let me know how it goes.

Cheers
Pete.

 

[dazum]

Pete;
I won't be able to check to see how it works until I'm back at work on Monday. I'm anxious to try out your code and will let you know how it works out.
Thanks
Dave

 

[lbass]

Try this SQL expression:

(
SELECT MAX("Decision_Date")
FROM "Intake_Participant" ip, "Intake" i
WHERE ip."Intake_ID" = i."Intake_ID" and
ip."Person_ID" = "Intake_Participant"."Person_ID"
)

I think both tables have to be present in the main report. At least Intake_Participant does.

-LB

 

[dazum]

Pete, lbass
I've tried each of your suggestions
(
SELECT MAX("Decision_Date")
FROM "Intake_Participant" ip, "Intake" i
WHERE ip."Intake_ID" = i."Intake_ID" and
ip."Person_ID" = "Intake_Participant"."Person_ID"
)
and
(
SELECT MAX("Decision_Date")
FROM "Intake" i
inner join "Intake_Participant" ip on i."Intake_ID" = ip."Intake_ID"
WHERE "Person_ID" = "Intake_Participant"."Person_ID"
)
but with both codes I received the same error: ORA: 00942 "table or view does not exist"
I checked and both the Intake and Intake_Participant tables are in the report. I am open to any suggestions.
I am also going to experiment with the code to see if I can come up with a solution.

 

[dazum]

I modified the 2 different codes as follows;
(
SELECT MAX("INTAKE"."DECISION_DATE")
FROM "INTAKE"
inner join "INTAKE_PARTICIPANT" on "INTAKE_PARTICIPANT"."INTAKE_ID" = "INTAKE"."INTAKE_ID"
WHERE "INTAKE_PARTICIPANT"."PERSON_ID" = "INTAKE_PARTICIPANT"."PERSON_ID"
)
and
(
SELECT MAX("INTAKE"."DECISION_DATE")
FROM "INTAKE", "INTAKE_PARTICIPANT"
WHERE "INTAKE_PARTICIPANT"."INTAKE_ID" = "INTAKE"."INTAKE_ID" and
"INTAKE_PARTICIPANT"."PERSON_ID" = "INTAKE_PARTICIPANT"."PERSON_ID"
)
I was able to get the SQL Expressions to pull data without the ORA: 00942 error, but the SQL Expression is only bringing in a 11-19-02 date for the every different Person_ID.
Any suggestions to adjust the code to get the max date for each Person_ID?

 

[pmax9999]

OK, so the earlier problems were all due to case.

I have seen this problem before where the SQL Expression returns the same result for every row. There is no reason why this solution should work, but assigning the tables an alias without ever refering to that alias has ususally worked. Can't explain why it works but it does.

So, try this:

(
SELECT	MAX(DECISION_DATE)
FROM 	INTAKE 				i
	inner join INTAKE_PARTICIPANT	ip on INTAKE.INTAKE_ID = INTAKE_PARTICIPANT.INTAKE_ID
WHERE 	PERSON_ID = "INTAKE_PARTICIPANT"."PERSON_ID"
)

Pete

 

[hilfy]

I think you guys are all getting it too complicated! Try this:

SELECT MAX(i.DECISION_DATE)
FROM INTAKE i
WHERE i.PERSON_ID = "INTAKE_PARTICIPANT"."PERSON_ID"

If Intake_Participant is already in the main report, there's no need to join it to Intake in the SQL Expression.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[lbass]

It might help clarify if you show the content of the SQL query for the main report.

-LB

 

[dazum]

hilfy, the Person_ID field is only in the INTAKE_PARTICIPANT table and not in the INTAKE table. That is why I'm joining on the Intake_ID field which is in both tables.
Pete,
I tried your latest code, but it errored out using the "i" and "ip" identifiers. I was able to get data using a modified version of your code (see below), but the SQL Expression is bring in the date 11-19-12. I don't know why it is bringing in that date because the 2 Person_ID's in the report don't have a DECISION_DATE of 11-19-12.
(
SELECT MAX("DECISION_DATE")
FROM "INTAKE"
inner join "INTAKE_PARTICIPANT" on "INTAKE"."INTAKE_ID" = "INTAKE_PARTICIPANT"."INTAKE_ID"
WHERE "PERSON_ID" = "INTAKE_PARTICIPANT"."PERSON_ID"
)
lbass,
Below is the SQL Query from the main report:
SELECT "INTAKE_PARTICIPANT"."PERSON_ID", "INTAKE"."INTAKE_ID", "INTAKE"."DECISION_DATE", ((
SELECT MAX("DECISION_DATE")
FROM "INTAKE"
inner join "INTAKE_PARTICIPANT" on "INTAKE"."INTAKE_ID" = "INTAKE_PARTICIPANT"."INTAKE_ID"
WHERE "PERSON_ID" = "INTAKE_PARTICIPANT"."PERSON_ID"
)
)
FROM "SACWIS_VPD"."INTAKE" "INTAKE" INNER JOIN "SACWIS_VPD"."INTAKE_PARTICIPANT" "INTAKE_PARTICIPANT" ON "INTAKE"."INTAKE_ID"="INTAKE_PARTICIPANT"."INTAKE_ID"
WHERE ("INTAKE_PARTICIPANT"."PERSON_ID"=3844163 OR "INTAKE_PARTICIPANT"."PERSON_ID"=3943354)
ORDER BY "INTAKE_PARTICIPANT"."PERSON_ID"

 

[lbass]

Try this:

(
SELECT MAX("DECISION_DATE")
FROM "INTAKE_PARTICIPANT" A, "INTAKE" B
WHERE A."INTAKE_ID"=B."INTAKE_ID" and
A."PERSON-ID"="INTAKE_PARTICIPANT"."PERSON_ID"
)

Or, try adding the owner:

(
SELECT MAX("DECISION_DATE")
FROM "SACWIS_VPD"."INTAKE_PARTICIPANT" A, "SACWIS_VPD"."INTAKE" B
WHERE A."INTAKE_ID"=B."INTAKE_ID" and
A."PERSON-ID"="INTAKE_PARTICIPANT"."PERSON_ID"
)

You might also try a different driver.

-LB

 

[dazum]

Hi lbass
I tried both of your latest suggestions, but I get an "Invalid Identifier" error. I remove the "A" and "B" identifiers and am able to get data from the SQL Expression, but it only brings in the current date for each record.

 

[Davest11]

I know that this thread is ancient, but I thought I'd take a shot and see if this issue was ever resolved. I'm having the same issue now...did you ever find a solution?

 

[dazum]

Davest11
I never was able to find a solution to this issue. From what I can tell, a SQL Expression works when the fields are in the same table, I was try to use fields from 2 different tables. I am not positive on that, but I could never get the correct data when using 2 different tables in a SQL Expression. If you can write the code, a SQL command might work.

 

[Davest11]

I just keep getting the Invalid Identifier error on references to tables that are in the report which I'm trying to use to link to the SQL Expression.

 

[PeterDimitrov]

try to change the SQL expression by adding an alias for the tables, which are part of the SQLexpression
SELECT MAX(sqli."DECISION_DATE")
FROM "INTAKE" sqli
inner join "INTAKE_PARTICIPANT" sqlip on sqli."INTAKE_ID" = sqlip."INTAKE_ID"
WHERE sqlip."PERSON_ID" = "INTAKE_PARTICIPANT"."PERSON_ID"

note that the last person_id is still "INTAKE_PARTICIPANT"."PERSON_ID"

the previous SQL might confuse the report because the tables "INTAKE_PARTICIPANT" and "INTAKE" are part of the SQLExpression and the report

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[dazum]

PeterDemitrov,
I tried your sugestion of using the alias for the tables and copied your exact code into my SQL Expression, but it errored out with the message ORA00936: missing expression.