Hi,

I need to use a Database function Year and Month in my predicate. This is the query i need to generate from LLBLGen. Using 2.5.0.0 runtime version.

Select c.productId

, Sum(Case When (month(a.datePerformed) = 1 and year(a.datePerformed) = 2007) then 1 else 0 End) As Jan

, Sum(Case When (month(a.datePerformed) = 2 and year(a.datePerformed) = 2007) then 1 else 0 End) As Feb

, Sum(Case When (month(a.datePerformed) = 3 and year(a.datePerformed) = 2007) then 1 else 0 End) As Mar

, Sum(Case When (month(a.datePerformed) = 4 and year(a.datePerformed) = 2007) then 1 else 0 End) As Apr

, Sum(Case When (month(a.datePerformed) = 5 and year(a.datePerformed) = 2007) then 1 else 0 End) As May

, Sum(Case When (month(a.datePerformed) = 6 and year(a.datePerformed) = 2007) then 1 else 0 End) As Jun

, Sum(Case When (month(a.datePerformed) = 7 and year(a.datePerformed) = 2007) then 1 else 0 End) As Jul

, Sum(Case When (month(a.datePerformed) = 8 and year(a.datePerformed) = 2007) then 1 else 0 End) As Aug

, Sum(Case When (month(a.datePerformed) = 9 and year(a.datePerformed) = 2007) then 1 else 0 End) As Sep

, Sum(Case When (month(a.datePerformed) = 10 and year(a.datePerformed) = 2007) then 1 else 0 End) As Oct

, Sum(Case When (month(a.datePerformed) = 11 and year(a.datePerformed) = 2007) then 1 else 0 End) As Nov

, Sum(Case When (month(a.datePerformed) = 12 and year(a.datePerformed) = 2007) then 1 else 0 End) As Dec

, Sum(Case When year(a.datePerformed) = 2007 then 1 Else 0 End) AS YTD

, Sum(Case When year(a.datePerformed) = 2006 then 1 Else 0 End) AS YTD1

from Activity.ActivityHeader a, Activity.ActivityPerson b, Activity.ActivityDetail c,

 Activity.ActivityType d

Where year(a.datePerformed) in (2007, 2006) -- I need to --use year db function Year

and b.activityId = a.id

and b.locationId = 83

and c.activityId = a.id

and d.id = a.activityTypeId and d.category = 'S'

group by c.productId --, month(a.datePerformed)

order by c.productId

Help is very much appreciated.

Regards

Prabhu

It should look like the following:

EntityField myField = ActivityHeaderFields.DatePerformed ;
myField.ExpressionToApply = new DbFunctionCall( "YEAR", new object[] { ActivityHeaderFields.DatePerformed } );

int[] values = new int[2] {2007, 2006};
predicateExpression.Add(new FieldCompareRangePredicate(
    myField, null, values));

Hi Walaa,

Thanks for your quick reply...

In the above query i need to nest the DBFunctionCall in the select column list .

Select c.productId

, Sum(Case When (month(a.datePerformed) = 1 and year(a.datePerformed) = 2007) then 1 else 0 End) As Jan

, Sum(Case When year(a.datePerformed) = 2007 then 1 Else 0 End) AS YTD

, Sum(Case When year(a.datePerformed) = 2006 then 1 Else 0 End) AS YTD1

from Activity.ActivityHeader a, Activity.ActivityPerson b, Activity.ActivityDetail c,

 Activity.ActivityType d

Where year(a.datePerformed) in (2007, 2006) -- I need to --use year db function Year

and b.activityId = a.id

and b.locationId = 83

and c.activityId = a.id

and d.id = a.activityTypeId and d.category = 'S'

group by c.productId --, month(a.datePerformed)

order by c.productId

Please let me know how to code for a single nested case statement in the above select query.

Regards

Prabhu

Sorry i need to know how to nest a dbfunctioncall.

I think you may use Constant feature of the DBFunctionCall to use one instance without nesting. eg. For:

Case When (month(a.datePerformed) = 1 and year(a.datePerformed) = 2007) then 1 else 0 End)

Try the following:

new DbFunctionCall(
    "CASE WHEN (month({0}) = 1 and year({1}) = 2007) then 1 else 0 End", 
    new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });

Walaa,

Thanks again for quick response. Will let you know once i try your solution.

Regards

Prabhu

Hi Walaa,

I am getting the following error.

"Failed to convert parameter value from a Int32 to a DateTime."

This is my method

public List<ActivityReport> GetActivityViewInfo(int id, int fromYear, string activityType, bool fromLocation) { List<ActivityReport> rowsToReturn = null;

        try
        {
            List<ActivityReport> collActivityReport = new List<ActivityReport>();
            DataProjectorToCustomClass<ActivityReport> projector = new DataProjectorToCustomClass<ActivityReport>(collActivityReport);
            IPrefetchPath2 prefetch = GetActivityViewInfoPrefetch("en-US");
            IRelationPredicateBucket bucket = GetFilterDataBucket(prefetch);
            FillPredicateExpressionForActivityViewInfo(bucket.PredicateExpression, id,fromLocation,activityType,fromYear);

            ResultsetFields fields = new ResultsetFields(16);

            fields.DefineField(ActivityDetailFields.ProductId, 0, "ProductId", string.Empty);
            fields.DefineField(ProductResourceDetailFields.Value, 1, "Product", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 2, "Jan", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 3, "Feb", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 4, "Mar", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 5, "Apr", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 6, "May", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 7, "Jun", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 8, "Jul", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 9, "Aug", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 10, "Sep", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 11, "Oct", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 12, "Nov", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 13, "Dec", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 14, "YTD", string.Empty);
            fields.DefineField(ActivityHeaderFields.DatePerformed, 15, "YTD1", string.Empty);

            //fields[2].ExpressionToApply = new DbFunctionCall("CASE {0} WHEN 'MA' THEN 'Massachusetts' WHEN 'OR' THEN 'Oregon' ELSE 'Unknown' END", new object[] { new DbFunctionCall("MONTH", new object[] { ActivityHeaderFields.DatePerformed }), new DbFunctionCall("YEAR", new object[] { ActivityHeaderFields.DatePerformed }) });
            fields[2].ExpressionToApply = new DbFunctionCall("SUM(CASE WHEN (month({0}) = 1 and year({1}) = 2007) then 1 else 0 End)", new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });
            fields[3].ExpressionToApply = new DbFunctionCall( "SUM(CASE WHEN (month({0}) = 1 and year({1}) = 2007) then 1 else 0 End)",new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });
            fields[4].ExpressionToApply = new DbFunctionCall("SUM(CASE WHEN (month({0}) = 1 and year({1}) = 2007) then 1 else 0 End)", new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });
            fields[5].ExpressionToApply = new DbFunctionCall("SUM(CASE WHEN (month({0}) = 1 and year({1}) = 2007) then 1 else 0 End)", new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });
            fields[6].ExpressionToApply = new DbFunctionCall("SUM(CASE WHEN (month({0}) = 1 and year({1}) = 2007) then 1 else 0 End)", new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });
            fields[7].ExpressionToApply = new DbFunctionCall("SUM(CASE WHEN (month({0}) = 1 and year({1}) = 2007) then 1 else 0 End)", new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });
            fields[8].ExpressionToApply = new DbFunctionCall("SUM(CASE WHEN (month({0}) = 1 and year({1}) = 2007) then 1 else 0 End)", new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });
            fields[9].ExpressionToApply = new DbFunctionCall("SUM(CASE WHEN (month({0}) = 1 and year({1}) = 2007) then 1 else 0 End)", new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });
            fields[10].ExpressionToApply = new DbFunctionCall("SUM(CASE WHEN (month({0}) = 1 and year({1}) = 2007) then 1 else 0 End)", new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });
            fields[11].ExpressionToApply = new DbFunctionCall("SUM(CASE WHEN (month({0}) = 1 and year({1}) = 2007) then 1 else 0 End)", new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });
            fields[12].ExpressionToApply = new DbFunctionCall("SUM(CASE WHEN (month({0}) = 1 and year({1}) = 2007) then 1 else 0 End)", new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });
            fields[13].ExpressionToApply = new DbFunctionCall("SUM(CASE WHEN (month({0}) = 1 and year({1}) = 2007) then 1 else 0 End)", new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });
            fields[14].ExpressionToApply = new DbFunctionCall("Sum(Case When year(a.datePerformed) = 2007 then 1 Else 0 End)", new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });
            fields[15].ExpressionToApply = new DbFunctionCall("Sum(Case When year(a.datePerformed) = 2006 then 1 Else 0 End)", new object[] { ActivityHeaderFields.DatePerformed, ActivityHeaderFields.DatePerformed });

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[0]);
            ISortExpression sort = new SortExpression(new SortClause(ActivityDetailFields.ProductId, null, SortOperator.Ascending));

            List<IDataValueProjector> valueProjector = new List<IDataValueProjector>();
            valueProjector.Add(new DataValueProjector("ProductId", 0, typeof(int))); 
            valueProjector.Add(new DataValueProjector("Product", 1, typeof(string)));
            valueProjector.Add(new DataValueProjector("Jan", 2, typeof(int)));
            valueProjector.Add(new DataValueProjector("Feb", 3, typeof(int)));
            valueProjector.Add(new DataValueProjector("Mar", 4, typeof(int)));
            valueProjector.Add(new DataValueProjector("Apr", 5, typeof(int)));
            valueProjector.Add(new DataValueProjector("May", 6, typeof(int)));
            valueProjector.Add(new DataValueProjector("Jun", 7, typeof(int)));
            valueProjector.Add(new DataValueProjector("Jul", 8, typeof(int)));
            valueProjector.Add(new DataValueProjector("Aug", 9, typeof(int)));
            valueProjector.Add(new DataValueProjector("Sep", 10, typeof(int)));
            valueProjector.Add(new DataValueProjector("Oct", 11, typeof(int)));
            valueProjector.Add(new DataValueProjector("Nov", 12, typeof(int)));
            valueProjector.Add(new DataValueProjector("Dec", 13, typeof(int)));
            valueProjector.Add(new DataValueProjector("YTD", 14, typeof(int)));
            valueProjector.Add(new DataValueProjector("YTD1", 15, typeof(int)));

            oData.FetchProjection(valueProjector, projector, fields, bucket, 0,sort, groupByClause, false, -1, -1);

            rowsToReturn = projector.Destination;
        }
        catch (Exception ex) { OptimaExceptionHandler.HandleException(ex); }
        return rowsToReturn;
    }

    private IPrefetchPath2 GetActivityViewInfoPrefetch(string language)
    {
        IPrefetchPath2 prefetchPath = null;

        IPredicateExpression langExp = new PredicateExpression();
        langExp.Add(new FieldCompareValuePredicate(LanguageFields.Code, null, ComparisonOperator.Equal, language));
        try
        {
            prefetchPath = new PrefetchPath2((int)EntityType.ActivityHeaderEntity);
            prefetchPath.Add(ActivityHeaderEntity.PrefetchPathActivityDetail).SubPath.Add( ActivityDetailEntity.PrefetchPathProduct).SubPath.Add(ProductEntity.PrefetchPathProductResource).SubPath.Add( ProductResourceEntity.PrefetchPathProductResourceDetail).SubPath.Add( ProductResourceDetailEntity.PrefetchPathLanguage,0, langExp);
            prefetchPath.Add(ActivityHeaderEntity.PrefetchPathActivityPerson);
            prefetchPath.Add(ActivityHeaderEntity.PrefetchPathActivityType);
        }
        catch (Exception ex) { OptimaExceptionHandler.HandleException(ex); }

        return prefetchPath;
    }

    private void FillPredicateExpressionForActivityViewInfo(IPredicateExpression predicateToFill, int id, bool fromLocation,string activityType,int fromYear)
    {
        if (fromLocation)
        {
            if (id >= 0) predicateToFill.Add(ActivityPersonFields.LocationId == id);
        }
        else
        {
            if (id >= 0) predicateToFill.Add(ActivityPersonFields.PersonId == id);
        }

        predicateToFill.AddWithAnd(ActivityTypeFields.Category == activityType);

        EntityField2 myField = ActivityHeaderFields.DatePerformed;
        myField.ExpressionToApply = new DbFunctionCall("YEAR", new object[] { ActivityHeaderFields.DatePerformed });

        int[] values = new int[2] { fromYear - 1, fromYear };
        predicateToFill.AddWithAnd(new FieldCompareRangePredicate(myField, null, values));
       }

Please let me know how to resolve.

Regards

Prabhu

That's a lot of code to look at, in which line does it give the mentioned exception/error?

I am getting when i call " [color value="FF0000"]oData.FetchProjection(valueProjector, projector, fields, bucket, 0,sort, groupByClause, false, -1, -1);[/color]" this line of code

Hi Walaa,

I had find where the issue is coming.

In the PredicateFunction i am trying to pass a range of year (integer) values to a Datetime column DatePerformed in ActivityHeader table. The code is

EntityField2 myField = ActivityHeaderFields.DatePerformed; myField.ExpressionToApply = new DbFunctionCall("YEAR", new object[] { ActivityHeaderFields.DatePerformed }); int[] values = new DateTime[2] {2006,2007 }; predicateToFill.AddWithAnd(new FieldCompareRangePredicate(myField, null, values));

The sample code generated by runtime is shown below...I am trying to pass value here as integer but the variable declared by the runtime for the DatePerformed field is DateTime as i highlighted in the code below. Please let me know how to resolve this issue.

SELECT DISTINCT [OptimaTigerDB].[Activity].[ActivityDetail].[productId] AS [ProductId], [OptimaTigerDB].[Products].[ProductResourceDetail].[value] AS [Product], SUM(CASE WHEN (month([OptimaTigerDB].[Activity].[ActivityHeader].[datePerformed]) = 1 and year([OptimaTigerDB].[Activity].[ActivityHeader].[datePerformed]) = 2007) then 1 else 0 End) AS [Jan] FROM ((((((( [OptimaTigerDB].[Activity].[ActivityHeader] LEFT JOIN [OptimaTigerDB].[Activity].[ActivityDetail] ON
[OptimaTigerDB].[Activity].[ActivityHeader].[id]=[OptimaTigerDB].[Activity].[ActivityDetail].[activityId]) LEFT JOIN [OptimaTigerDB].[Products].[Product] ON
[OptimaTigerDB].[Products].[Product].[id]=[OptimaTigerDB].[Activity].[ActivityDetail].[productId]) LEFT JOIN [OptimaTigerDB].[Products].[ProductResource] ON
[OptimaTigerDB].[Products].[ProductResource].[id]=[OptimaTigerDB].[Products].[Product].[descriptionId]) LEFT JOIN [OptimaTigerDB].[Products].[ProductResourceDetail] ON [OptimaTigerDB].[Products].[ProductResource].[id]=[OptimaTigerDB].[Products].[ProductResourceDetail].[resourceId]) LEFT JOIN [OptimaTigerDB].[Country].[Language] ON [OptimaTigerDB].[Country].[Language].[id]=[OptimaTigerDB].[Products].[ProductResourceDetail].[languageId]) LEFT JOIN [OptimaTigerDB].[Activity].[ActivityPerson] ON [OptimaTigerDB].[Activity].[ActivityHeader].[id]=[OptimaTigerDB].[Activity].[ActivityPerson].[activityId]) LEFT JOIN [OptimaTigerDB].[Activity].[ActivityType] ON [OptimaTigerDB].[Activity].[ActivityType].[id]=[OptimaTigerDB].[Activity].[ActivityHeader].[activityTypeId]) WHERE ( ( [OptimaTigerDB].[Activity].[ActivityPerson].[locationId] = @LocationId1 AND [OptimaTigerDB].[Activity].[ActivityType].[category] = @Category2 AND YEAR([OptimaTigerDB].[Activity].[ActivityHeader].[datePerformed]) BETWEEN @DatePerformed3 AND @DatePerformed4)) GROUP BY [OptimaTigerDB].[Activity].[ActivityDetail].[productId] ORDER BY [OptimaTigerDB].[Activity].[ActivityDetail].[productId] ASC

Parameter: @DatePerformed3 : DateTime. Direction: Input. Value: 2007. Parameter: @DatePerformed4 : DateTime. Input. Value: 2006.

Regards

Prabhu

Hi,

Walaa wrote:

It should look like the following:

EntityField myField = ActivityHeaderFields.DatePerformed ;
myField.ExpressionToApply = new DbFunctionCall( "YEAR", new object[] { ActivityHeaderFields.DatePerformed } );

int[] values = new int[2] {2007, 2006};
predicateExpression.Add(new FieldCompareRangePredicate(
    myField, null, values));

When i to pass integer values i.e 2006 and 2007 , type cast error is throwing.

This is the error i am getting " An exception was caught during the execution of a retrieval query: Failed to convert parameter value from a Int32 to a DateTime.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

Runtime generate the FieldCompareRangePredicate variables as DateTime but from the code i am passing integer values. Please let me know how to overcome this situation.

Regards

Prabhu

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