Hi Frans,

I'm trying to implement a dynamic list in the Self Servicing model that does the following:

        select a.alertid, a.alertnm, b.alertnotesid, b.alertid, b.notestxt
        from tb_alert a
        left join tb_alertnotes b on a.alertid = b.alertid 
            and b.alertnotesid = (select top 1 alertnotesid from tb_alertnotes where alertid = a.alertid order by alertnotesid desc)

Here's where I am:

        Dim fields As New ResultsetFields(4)
        fields.DefineField(AlertFieldIndex.AlertID, 0, "AlertID", "a")
        fields.DefineField(AlertFieldIndex.AlertNm, 1, "AlertNm", "a")
        fields.DefineField(AlertNotesFieldIndex.AlertNotesID, 2, "AlertNotesID", "f")
        fields.DefineField(AlertNotesFieldIndex.NotesTxt, 3, "NotesTxt", "f")

        ' Create Left Join relationship
        Dim relations As IRelationCollection = New RelationCollection
        relations.Add(AlertEntity.Relations.AlertNotesEntityUsingAlertID, "a", "f", JoinHint.Left)

        ' Sort results
        Dim sorter As ISortExpression = New SortExpression
        sorter.Add(SortClauseFactory.Create(AlertFieldIndex.AlertNm, SortOperator.Ascending))

        ' Create Where clause filters
        Dim filter As IPredicateExpression = New PredicateExpression
        filter.Add(PredicateFactory.CompareValue(AlertFieldIndex.AlertTypeCd, ComparisonOperator.Equal, SelectedAlertTypeCd, "a"))

        ' Retrieve Datatable
        Dim dao As New TypedListDAO
        dao.GetMultiAsDataTable(fields, Alerts, 0, sorter, filter, relations, True, Nothing, Nothing, 0, 0)

I've gotten this far without a problem. But I can't visualize the FieldCompareSetPredicate that needs to be assigned to the relations CustomFilter.

I've looked at other similar queries in the Forum, but none deal with joins.

Any help you can give me constructing the FieldCompareSetPredicate would be greatly appreciated.

Your code doesn't seem to be for the query you've mentioned, at least your predicate does something else than what's described in the query.

Nevertheless, the subquery in your query should be something like this:

PredicateExpression subQueryFilter = new PredicateExpression();
subQueryFilter.Add(PredicateFactory.CompareExpression(
    AlertNotesFieldIndex.AlertId, ComparisonOperator.Equal,
        new Expression(EntityFieldFactory.Create(AlertNotesFieldIndex.AlertId)), "a"));
PredicateExpression onFilter = new PredicateExpression();
onFilter.Add(new FieldCompareSetPredicate(
    EntityFieldFactory.Create(AlertNotesFieldIndex.AlertNotesId), null,
    EntityFieldFactory.Create(AlertNotesFieldIndex.AlertNotesId), null,
    SetOperator.Equal,
    subQueryFilter,
    null,
    "f",
    1,
    new SortExpression(SortClauseFactory.Create(AlertNotesFieldIndex.AlertNotesId, SortOperator.Descending))));

You then specify onFilter as the CustomFilter for the relation.

I specified 'f' as alias, as you use that too in your C# code, in your query that's 'b'

Otis wrote:

Your code doesn't seem to be for the query you've mentioned, at least your predicate does something else than what's described in the query.

Your right, I cut my code sample out of a large query in a poor attempt to narrow the focus to my question.

Thanks for answering with just what I needed!