Hello.. i'm trying to get a single column sorted distinct result set from a view.

Here's my code

public DataTable LoadClaimPeriodSeries(int entityId)
    {
        RelationPredicateBucket filter = new RelationPredicateBucket();
        filter.PredicateExpression.Add(VClaimsFields.ClientId == entityId);
        ((FieldCompareValuePredicate)filter.PredicateExpression[0].Contents).ObjectAlias = "vClaims";

        ResultsetFields fields = new ResultsetFields(1);
        fields.DefineField(VClaimsFields.Period, 0, "Period", "vClaims");

        SortExpression exp = new SortExpression(VClaimsFields.Period | SortOperator.Descending);
        DataTable tlist = new DataTable();

        DataAccessAdapter adapter = new DataAccessAdapter();
        adapter.FetchTypedList(fields, tlist, filter, 0, exp, false, null);
        adapter.CloseConnection();

        return tlist;
    }

The problem with it is i get all the Period values matching the WHERE clause. DISTINCT is not added to the query. If the SortExpression is nulled/omitted DISTINCT is added to the query.

I can't see a valid reason for this... when I execute the generated sql myself and insert the DISTINCT keyword in I get no issues. Just a lovely sorted list of distinct Period column values.

Why is it so?

I removed the table alias stuff from the code and it functioned as expected...