Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> retrive max value from sql database on condition
 

Pages: 1
LLBLGen Pro Runtime Framework
retrive max value from sql database on condition
Page:1/1 

  Print all messages in this thread  
Poster Message
nopchan
User



Location:

Joined on:
30-Apr-2005 02:19:03
Posted:
56 posts
# Posted on: 10-Sep-2005 03:49:29.  
May I have code for retrive max value from sql database on condition "max(arrec_no) where branch_id = '01' "


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


  Top
Paul.Lewis
User



Location:
Columbus Ohio USA
Joined on:
22-Aug-2005 21:06:58
Posted:
147 posts
# Posted on: 10-Sep-2005 04:37:56.  
This code should get you what your looking for. Good Luck!

Code:
// 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);


  Top
nopchan
User



Location:

Joined on:
30-Apr-2005 02:19:03
Posted:
56 posts
# Posted on: 10-Sep-2005 12:01:26.  
Thanks.


May I have code for

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

Thanks again.
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 10-Sep-2005 12:32:39.  
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


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
nopchan
User



Location:

Joined on:
30-Apr-2005 02:19:03
Posted:
56 posts
# Posted on: 12-Sep-2005 04:44:01.  
Quote:
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


Quote:
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


  Top
nopchan
User



Location:

Joined on:
30-Apr-2005 02:19:03
Posted:
56 posts
# Posted on: 12-Sep-2005 06:59:02.  
I can work now with this code

Quote:
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.


  Top
nopchan
User



Location:

Joined on:
30-Apr-2005 02:19:03
Posted:
56 posts
# Posted on: 12-Sep-2005 11:30:05.  
I can not add " and or " in condition

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

or

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



  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 12-Sep-2005 13:03:31.  
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


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.