Hi,

I'm trying to translate the following (Oracle) query into the LLBLGen predicate syntax:

            SELECT * 
            FROM KRD.VEHICLE
            WHERE GREATEST(
                            GREATEST(LASTMODIFIEDVOERTUIG, LASTMODIFIEDMERKTYPE),
                            GREATEST(LASTMODIFIEDNAP, LASTMODIFIEDVOERTUIGRELATIE)
                          ) > '2008-01-01'

Could someone please give me a hint how I do this? I don't expect you to write my code, but I have no clue where to start. E.g., I wonder if the 'GREATEST' operator maps to the 'AggregateFunction.Max' expression in LLBLGen.

Help is greatly appreciated!

Thanks, peerke

GREATEST, that's a Database function.

Would you please check the following documentation section: Generated code - Calling a database function

Thanks for your reply. I checked the documentation an I constructed this code snippet:

            VehicleTypedView vehicles = new VehicleTypedView();
            
            ResultsetFields fields = new ResultsetFields(4);
            fields.DefineField(VehicleTypedViewFields.Lastmodifiedvoertuig, 0, "Lastmodifiedvoertuig");
            fields.DefineField(VehicleTypedViewFields.Lastmodifiedmerktype, 1, "Lastmodifiedmerktype");
            fields.DefineField(VehicleTypedViewFields.Lastmodifiednap, 2, "Lastmodifiednap");
            fields.DefineField(VehicleTypedViewFields.Lastmodifiedvoertuigrelatie, 3, "Lastmodifiedvoertuigrelatie");

            fields[0].ExpressionToApply = new DbFunctionCall("GREATEST", new object[] { VehicleTypedView.Lastmodifiedvoertuig, VehicleTypedView.Lastmodifiedmerktype });
            fields[2].ExpressionToApply = new DbFunctionCall("GREATEST", new object[] { VehicleTypedView.Lastmodifiednap, VehicleTypedView.Lastmodifiedvoertuigrelatie });

            DataTable results = new DataTable();
            TypedListDAO dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, results, 0, null, null, null, true, null, null, 0, 0);
            foreach (DataRow row in results.Rows)
            {
                //do something
            }

However, I'm stuck again now. how do I apply the DbFunctionCall- filter to my typed view? I would like to use that typed view so I have my results in a strongly typed way. Also, in my query I apply the 'GREATEST' function on the results of 2 other GREATEST-function calls. How do I map that to my LLBLGen filter?

Thanks again! peerke

SELECT * FROM KRD.VEHICLE WHERE GREATEST( GREATEST(LASTMODIFIEDVOERTUIG, LASTMODIFIEDMERKTYPE), GREATEST(LASTMODIFIEDNAP, LASTMODIFIEDVOERTUIGRELATIE) ) > '2008-01-01'

ResultsetFields are only used when creating a dynamicList, you shouldn't use them if you are dealing with a TypedView.

I think you'll need something like the following:

VehicleTypedView vehicles = new VehicleTypedView();

IExpression exp1 = new DbFunctionCall("GREATEST", new object[] { VehicleTypedViewFields.Lastmodifiedvoertuig,  VehicleTypedViewFields.Lastmodifiedmerktype}) ;

IExpression exp2 = new DbFunctionCall("GREATEST", new object[] { VehicleTypedViewFields.Lastmodifiednap,  VehicleTypedViewFields.Lastmodifiedvoertuigrelatie}) ;

EntityField myField = VehicleTypedViewFields.anyField; // just pick any field.

myField.ExpressionToApply = new DbFunctionCall("GREATEST", new object[] { exp1, exp2});

PredicateExpression filter = new PredicateExpression(myField > "2008-01-01");
vehicles.Fill(0, null, false, filter);

Walaa, you're the best, this is what I needed!

I have to say, the LLBLGen framework is very intuitive, but I just have to get used to the 'predicate' way of transforming my old SQL code to 'LLBLGen'-code. I guess it's just takes a lot of practice.

Thanks again for your help!

peerke

I'm glad it worked with you. Frankly speaking the guy who created LLBLGen Pro (Frans Bouma) is the best, I'm just a user of the framework.