Hi,

I am having difficulty trying to use the examples of the max aggregate in a where clause. Here is the SQL of the stored procedure I am converting:

    SELECT  d.dataID,s.FileType, v.Prefix, d.name, s.FileType, s.DataSize
        FROM
            Livelink.LivelinkSA.DTree d inner join livelink.LivelinkSA.rimsnodeclassification r 
            on d.dataID=r.nodeID 
            inner join
            dbo.v_Region_Prefix v on v.DataID = d.dataID inner join
            Livelink.LivelinkSA.DVersData s on s.docID = d.dataID

        WHERE d.parentId = @DataId AND
            s.Version = (Select MAX(Version) FROM Livelink.LivelinkSA.DVersData s 
            inner join Livelink.LivelinkSA.Dtree on s.docID = d.dataID)     

Here is what I have so far:

DataTable dtResults;

 FolderDocCountSubReportTypedList tlFolderDocCountSub = new FolderDocCountSubReportTypedList();

IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareValuePredicate(DtreeFields.ParentId, ComparisonOperator.Equal, DataId));

dtResults = (DataTable)tlFolderDocCountSub;
return dtResults;

I have spent most of the day getting the where max part to work, but cannot. Any help and examples would be greatly appreciated.

Thanks!

Are you sure your query will properly run?

WHERE d.parentId = @DataId AND s.Version = (Select MAX(Version) FROM Livelink.LivelinkSA.DVersData s inner join Livelink.LivelinkSA.Dtree on s.docID = d.dataID)

I think the sub-Query is using 's' & 'd' as aliases and both have been used before in the main query.

Anyway I think you may use FieldCompareExpressionPredicate, to compare the s.Version field with an entityField having its ExpressionToUse set to a ScalarQueryExpression.

Please search for the bold words in the LLBLGen Pro manual.