Hi

I need to run the following sql using the FieldCompareSetPredicate.

select * from notification a where a.statusid = 4 and not exists ( select 'X' from notification b where b.statusid < 4 and a.staffid = b.staffid and a.weekdate = b.weekdate)

The primary key on the notification is not the staff id and weekdate. They can appear numerous times in the table.

When I went to use the FieldcompareSetPredicate I realised that it only seems to work on a.staffid = b.staffid but I must have the weekdate as well.

Any help will be appreciated.

Thanks

Please show us the code snippet you have so far. And... LLBLGen version and runtime library version? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7720)

Code so far without the fieldcomparesetpredicate. Am in the process of changing to meet new requriements so was investigating the best way to do this.

        ResultsetFields fields = new ResultsetFields(5);
        fields.DefineField(StaffFields.Name, 0);
        fields.DefineField(StatusFields.Descr, 1, "Status");
        fields.DefineField(NotificationFields.Descr, 2, "Reason");
        fields.DefineField(NotificationFields.NotificationDt, 3);
        fields[3].SetExpression(new DbFunctionCall("CONVERT(CHAR(10),{0},103)", new object[] { NotificationFields.NotificationDt }));
        fields.DefineField(NotificationFields.WeekDate, 4);
        fields[4].SetExpression(new DbFunctionCall("CONVERT(CHAR(10),{0},103)", new object[] { NotificationFields.WeekDate }));

        // add joins to tables
        bucket = new RelationPredicateBucket();
        bucket.Relations.Add(NotificationEntity.Relations.StatusEntityUsingStatusId, JoinHint.Inner);
        bucket.Relations.Add(NotificationEntity.Relations.StaffEntityUsingStaffId, JoinHint.Inner);

        // add where clauses
        bucket.PredicateExpression.Add(StatusFields.StatusId == statEnt.StatusId);

        if (wrkGrpID !=0)
            bucket.PredicateExpression.AddWithAnd(StaffFields.WorkGroupId == wrkGrpID);
        else
            bucket.PredicateExpression.AddWithAnd(StaffFields.StaffId == staff.StaffId);

        //sort by name
        SortExpression sort = new SortExpression();
        sort.Add(StaffFields.Name | SortOperator.Ascending);
        sort.Add(NotificationFields.WeekDate | SortOperator.Ascending);

        //set up the table to store the result
        DataTable dtResults = new DataTable("Results");

        adapter.FetchTypedList(fields, dtResults, bucket, 0, sort, false);

Am using Version 2.6 of LLBLGen. Am using .Net20.dll runtime versions.

thanks

For:

not exists ( select 'X' from notification b where b.statusid < 4 and a.staffid = b.staffid and a.weekdate = b.weekdate)

Please try this:

var innerFilter = new PredicateExpression();
innerFilter.Add(NotificationFields.StatusId.SetObjectAlias("b") < 4);
innerFilter.Add(NotificationFields.StaffId == NotificationFields.StaffId .SetObjectAlias("b"));
innerFilter.Add(NotificationFields.weekdate == NotificationFields.weekdate.SetObjectAlias("b"));

var filter = new FieldCompareSetPredicate(NotificationFields.AnyField, null, NotificationFields.AnyField.SetObjectAlias("b"), null, SetOperator.Exist, innerFilter, true);

The above code uses an Adapter only overload of FieldCompareSetPredicate CTor. For selfServicing, just remove the null parameters.

Thanks for your help. It worked beautifully.