CR9 SQL expression using fields from the report 1

[ppeettee]

In CR8.5 you could do an SQL expression field that would return a different value for each record.
For example a report showing a list of Films (film.id in the report) could have an SQL expression field to show the first date it was booked out.

(Select Min(LT."Start_Date") from LENDING_TABLE LT 
Where LT.FLM_ID = FILM.id)

But when I try this in crystal 9 I get an error saying that the FILM Table is not found

Any way for this to work in crystal 9?

Cheers
Pete

 

[lbass]

(
Select Min("Start_Date") from LENDING_TABLE LT
Where LT."FLM_ID" = LENDING_TABLE."FLM_ID"
)

In v.9.0 to XI, you have to omit the table designation within the summary function. Also the where clause sets up a faux group on "FLM_ID" so that the minimum can be calculated within that group. I'm not sure about your punctuation--it depends upon your datasource, which you haven't identified, but in your expression, you referenced a table "FILM" in your where clause that was not present in your From clause. You should be able to get the correct results using my suggestion, if you use the correct punctuation.

-LB

 

[ppeettee]

Thanks for the quick response, I'm using a sybase database and CR9.0

I actually posted a simplified example of the problem I am having and if I remove the table designation within the summary function I get an error

"Column 'Start_Date' found in more than one table -- need a correlation name"

in crystal 8.5 you can reference a table that is not in the from clause so long as the table is in the report with no alias.

 

[lbass]

In XI (and I believe in 9.0), you cannot designate a table name within the function. You can certainly try it:

(
Select Min(LT."Start_Date") from LENDING_TABLE LT
Where LT."FLM_ID" = LENDING_TABLE."FLM_ID"
)

I'm surprised you would get that error though, since you should only be referencing the Lending_Table within the SQL expression. Please show what you actually tried. If you added the second table, please explain why you feel you must.

-LB

 

[ppeettee]

This is the actual SQl expression field

Clients Attend YSessions that are part of an YActivity.
the YActivity is in turn part of a team (YACTIVITY."YS_Team_Code") I need the first time they attended for that team
(the report itself is grouped by team)

(Select Min("Start_Date") from YSESSION YS, YSESSION_ATTENDANCE ysa, YACTIVITY YACT
where 
YS."YSession_No" = ysa."YSession_No"
and yact."YActivity_No" = YS."YActivity_No"
and YACT."YS_Team_Code" = YACTIVITY."YS_Team_Code"
and ysa.Client_No = YSESSION_ATTENDANCE.Client_No)

Gives the "Column 'Start_Date' found in more than one table -- need a correlation name"

But

(Select Min(YS."Start_Date") from YSESSION YS, YSESSION_ATTENDANCE ysa, YACTIVITY YACT
where 
YS."YSession_No" = ysa."YSession_No"
and yact."YActivity_No" = YS."YActivity_No"
and YACT."YS_Team_Code" = YACTIVITY."YS_Team_Code"
and ysa.Client_No = YSESSION_ATTENDANCE.Client_No)

Gives the standard error you get when you don't omit the table designation within the summary function.

I've tried using actual joins as well as the way I've joined them here.

I'm getting close to rewriting the report in 8.5

 

[lbass]

You might find something to help in thread767-1430337. Otherwise, if CR 8.5 or CR2008 are an option, the usual SQL expression syntax works in both.

-LB