May I have code for retrive max value from sql database on condition "max(arrec_no) where branch_id = '01' "

SELECT MAX(arrec_no) AS Expr1 FROM arrec WHERE (branch_id = '01')

This code should get you what your looking for. Good Luck!

// Adapter:
IPredicate filter = PredicateFactory.CompareValue(arrecFieldIndex.branch_id, ComparisonOperator.Equal, "01");
DataAccessAdapter adapter = new DataAccessAdapter();
Integer max = (Integer)adapter.GetScalar(EntityFieldFactory.Create(arrecFieldIndex.arrec_no), null, 
    AggregateFunction.Max, filter, null);
    
    
// selfservicing:
arrecCollection col = new arrecCollection();
IPredicate filter = PredicateFactory.CompareValue(arrecFieldIndex.branch_id, ComparisonOperator.Equal, "01");
Integer max = (Integer)col.GetScalar(arrecFieldIndex.arrec_no, null, AggregateFunction.Max, filter, null);

Thanks.

May I have code for

select max(arrec_no) as NoMax where left(arrec_no,5) = '01050'

Thanks again.

functions on fields aren't supported at the moment.

You can add functions on fields yourself by using a custom IExpression implementation. Use the following thread as an idea how to do that. The following thread is about a date function, but you can of course use any function/parameter types you'd like, the mechanism is the same:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

using System; using SD.LLBLGen.Pro.ORMSupportClasses;

namespace DataModelUtils { /// <summary> /// Summary description for FunctionExpression. /// </summary> [Serializable] public class FunctionExpression:Expression { #region class member declarations private string _functionToApply; #endregion

    public FunctionExpression():base()
    {
        _functionToApply = string.Empty;
    }

    /// Selfservicing constructor.All other constructors are hidden, we only work on a single field. 
    public FunctionExpression(IEntityField field, string functionToApply) : base(field)
    {
        _functionToApply = functionToApply;
    }

    /// Adapter constructor. All other constructors are hidden, we only work on a single field.
    public FunctionExpression(IEntityField2 field, string functionToApply) : base(field)
    {
        _functionToApply = functionToApply;
    }


    public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
    {
        if(_functionToApply.Length>0)
        {
            return string.Format("{0}({1})", _functionToApply, base.ToQueryText(ref uniqueMarker, inHavingClause));
        }
        else
        {
            return base.ToQueryText(ref uniqueMarker, inHavingClause);
        }
    }


    #region properties
    public string FunctionToApply
    {
        get { return _functionToApply;}
        set { _functionToApply = value;}
    }
    #endregion
}

}

I call

IExpression yearCall = new DataModelUtils.FunctionExpression( EntityFieldFactory.Create(ArrecFieldIndex.ArrecDate), "year"); FieldCompareValuePredicate filterB = PredicateFactory.CompareValue( ArrecFieldIndex.ArrecDate, ComparisonOperator.Equal, 2005); filterB.FieldCore.ExpressionToApply = yearCall; A.AddWithAnd(filterB);

It 's work OK

but I can not send left(xxxx,5) to FunctionExpression

I can work now with this code

using System; using SD.LLBLGen.Pro.ORMSupportClasses;

namespace DataModelUtils { /// <summary> /// Summary description for FunctionExpression. /// </summary> [Serializable] public class FunctionExpression:Expression { #region class member declarations private string _functionToApply; private string _functionToApplyParameter;

    #endregion

    public FunctionExpression():base()
    {
        _functionToApply = string.Empty;
        _functionToApplyParameter = string.Empty;

    }

    /// Selfservicing constructor.All other constructors are hidden, we only work on a single field. 
    public FunctionExpression(IEntityField field, string functionToApply) : base(field)
    {
        _functionToApply = functionToApply;
    }
    public FunctionExpression(IEntityField field, string functionToApply,string functionToApplyParameter) : base(field)
    {
        _functionToApply = functionToApply;
        _functionToApplyParameter = functionToApplyParameter;
    }


    /// Adapter constructor. All other constructors are hidden, we only work on a single field.
    public FunctionExpression(IEntityField2 field, string functionToApply) : base(field)
    {
        _functionToApply = functionToApply;
    }
    public FunctionExpression(IEntityField2 field, string functionToApply,string functionToApplyParameter) : base(field)
    {
        _functionToApply = functionToApply;
        _functionToApplyParameter = functionToApplyParameter;

    }


    public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
    {
        if(_functionToApply.Length>0)
        {
            if(_functionToApplyParameter.Length>0)
            {
                return string.Format("{0}({1},{2})", _functionToApply, base.ToQueryText(ref uniqueMarker, inHavingClause),_functionToApplyParameter);

            }
            else
            {
                return string.Format("{0}({1})", _functionToApply, base.ToQueryText(ref uniqueMarker, inHavingClause));

            }
        }
        else
        {
            return base.ToQueryText(ref uniqueMarker, inHavingClause);
        }
    }


    #region properties
    public string FunctionToApply
    {
        get { return _functionToApply;}
        set { _functionToApply = value;}
    }
    #endregion
}

}

Thanks.

I can not add " and or " in condition

where (field1 = 'A' or field1 = 'B') and (field2 = 'C' or field2 = 'D')

or

where field1 = 'A' and (field2 = 'C' or field3 = 'D')

Use per () a predicate expression and add that to the parent predicate expression.

So (A or B) and (C or D) becomes 3 predicate expressions: 1 for A or B -> pe1 1 for C or D -> pe2 and one for pe1 and pe2