LLBLGEN version: 2.0.0.0 Final; 6th, December 2006.

I have a nvarchar column that stores data of various types, among these DateTime. I would like to perform a select on this column where date earlier than another date, i.e. something like this:

and 
CASE 
    WHEN ISDATE(wsdfv.[Value]) = 0 THEN 0
    WHEN (wsdfv.[Value] > GetDate()) THEN 1
END = 1
and

As you can see, if the data is not valid DateTime, the row should be excluded from result, not cause an exception.

Is there a way I can write this in llblgen using a FieldCompareValuePredicate or similar?

hotchill, CASE Function is not supported as I know. I suggest you the next workaround:

1. Create a User Funcition Call. Somthing like this:

CREATE FUNCTION MyIsAccomplishDateFunction
(
    -- Add the parameters for the function here
    @anything varchar(50)
)
RETURNS bit
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ResultVar bit

    -- Default value
    SELECT @ResultVar = 0

    -- Maybe value 1
    IF ISDATE(@anything) = 1
        IF Cast(@anything as datetime) > GetDate()
            SELECT @ResultVar = 1

    -- return result
    RETURN @ResultVar

END
GO

2. Rewrite, so your sql seems to:

... and
dbo.MyIsAccomplishDateFunction(wsdfv) = 1
and ...

3. Use LLBLGenPro DBFunctionCalls at your code filter._ Ref: LLBLGenPro Help - Calling a database function. _

Let us know if there are issues with this approach.

Thanks very much for your reply master daelmo.

My function will look something like this:

DbFunctionCall isBeforeDateExpression = new DbFunctionCall("dbo", "MyIsAccomplishDateFunction", new object[] {WizardStepDataFieldValueFields.Value, predicateData.Value});

PredicateData.Value is my date (I do not use sql GetDate as date to compare with).

How do I make an IPredicate from this DbFunctionCall object? I cannot use the FieldCompareExpressionPredicate because I want to compare with true, not a database field.

Use a FieldCompareValuePredicate, and use an entityField which has the ExpressionToApply set to the isBeforeDateExpression.

Yes, that was my initial thought as well, but I am in a subselect. How do I define an extra field in the sub select result set?

/// <summary>
/// Adds the report instance predicate expression.
/// </summary>
/// <param name="topFilter">The top filter.</param>
/// <param name="reportName">Name of the report.</param>
/// <param name="predicateData">The predicate data.</param>
private void AddReportInstancePredicateExpression(IRelationPredicateBucket topFilter, string reportName, DataFieldValuePredicateData<string> predicateData) {
    IRelationPredicateBucket reportInstanceInner = new RelationPredicateBucket();
    reportInstanceInner.Relations.Add(ReportInstanceEntity.Relations.ReportTypeEntityUsingReportTypeId);
    reportInstanceInner.Relations.Add(ReportInstanceEntity.Relations.WizardStepDataRowInstanceEntityUsingReportInstanceId);
    reportInstanceInner.Relations.Add(WizardStepDataRowInstanceEntity.Relations.WizardStepDataFieldValueEntityUsingWizardStepDataRowInstanceId);
    reportInstanceInner.Relations.Add(WizardStepDataFieldValueEntity.Relations.WizardStepDataFieldEntityUsingWizardStepDataFieldId);
    reportInstanceInner.PredicateExpression.Add(new FieldCompareValuePredicate(ReportTypeFields.Name, null, ComparisonOperator.Equal, reportName));
    reportInstanceInner.PredicateExpression.Add(new FieldCompareValuePredicate(WizardStepDataFieldFields.Name, null, ComparisonOperator.Equal, predicateData.Name));
    switch (predicateData.DataType) {
        case DataFieldValuePredicateData<string>.PredicateDataType.String:
            reportInstanceInner.PredicateExpression.Add(new FieldCompareValuePredicate(WizardStepDataFieldValueFields.Value, null, predicateData.ComparisonOperator, predicateData.Value));
            break;
           case DataFieldValuePredicateData<string>.PredicateDataType.Date:
            DbFunctionCall isBeforeDateExpression = new DbFunctionCall("dbo", "MyIsAccomplishDateFunction", new object[] {WizardStepDataFieldValueFields.Value, predicateData.Value});
            
            //reportInstanceInner.PredicateExpression.Add(new Expre new FieldCompareExpressionPredicate
            //reportInstanceInner.PredicateExpression.Add(new Expression(1, ExOp.Equal, new DbFunctionCall("", null)));
            break;
    }
    reportInstanceInner.PredicateExpression.Add(new FieldCompareNullPredicate(ReportInstanceFields.ReportNumber, null, true));

    FieldCompareSetPredicate reportInstanceOuter = new FieldCompareSetPredicate(WizardStepDataRowInstanceFields.ReportInstanceId, null, ReportInstanceFields.ReportInstanceId, null, SetOperator.In, reportInstanceInner.PredicateExpression, reportInstanceInner.Relations);
    topFilter.PredicateExpression.Add(reportInstanceOuter);
}

case DataFieldValuePredicateData<string>.PredicateDataType.Date:

EntityField2 myField = myEntity.AnyField;
myField.ExpressionToApply = new DbFunctionCall("dbo", "MyIsAccomplishDateFunction", new object[] {WizardStepDataFieldValueFields.Value, predicateData.Value});
            
reportInstanceInner.PredicateExpression.Add(myField == 1);
// similar to
// reportInstanceInner.PredicateExpression.Add(new FieldCompareValuePredicate(myField, null, ComparisonOperator.Equal, 1));

break;

Thanks a bunch guys

Posting final code in case someone needs similar:

/// <summary>
/// Adds the report instance predicate expression.
/// </summary>
/// <param name="topFilter">The top filter.</param>
/// <param name="reportName">Name of the report.</param>
/// <param name="predicateData">The predicate data.</param>
private void AddReportInstancePredicateExpression(IRelationPredicateBucket topFilter, string reportName, DataFieldValuePredicateData<string> predicateData) {
    IRelationPredicateBucket reportInstanceInner = new RelationPredicateBucket();
    reportInstanceInner.Relations.Add(ReportInstanceEntity.Relations.ReportTypeEntityUsingReportTypeId);
    reportInstanceInner.Relations.Add(ReportInstanceEntity.Relations.WizardStepDataRowInstanceEntityUsingReportInstanceId);
    reportInstanceInner.Relations.Add(WizardStepDataRowInstanceEntity.Relations.WizardStepDataFieldValueEntityUsingWizardStepDataRowInstanceId);
    reportInstanceInner.Relations.Add(WizardStepDataFieldValueEntity.Relations.WizardStepDataFieldEntityUsingWizardStepDataFieldId);
    reportInstanceInner.PredicateExpression.Add(new FieldCompareValuePredicate(ReportTypeFields.Name, null, ComparisonOperator.Equal, reportName));
    reportInstanceInner.PredicateExpression.Add(new FieldCompareValuePredicate(WizardStepDataFieldFields.Name, null, ComparisonOperator.Equal, predicateData.Name));
    switch (predicateData.DataType) {
        case DataFieldValuePredicateData<string>.PredicateDataType.String:
            reportInstanceInner.PredicateExpression.Add(new FieldCompareValuePredicate(WizardStepDataFieldValueFields.Value, null, predicateData.ComparisonOperator, predicateData.Value));
            break;
        case DataFieldValuePredicateData<string>.PredicateDataType.DateTime:
            EntityField2 datePredicateExpressionResultField;
            switch (predicateData.ComparisonOperator) {
                case ComparisonOperator.LessEqual:
                    DateTime comparisonDate;
                    if(DateTime.TryParse(predicateData.Value, CultureInfo.InvariantCulture, DateTimeStyles.None, out comparisonDate)) {
                        datePredicateExpressionResultField = new EntityField2("IsDateLessThanOrEqual", new DbFunctionCall("dbo", "usp_date_is_less_than_or_equal", new object[] { WizardStepDataFieldValueFields.Value, comparisonDate}));
                    } else {
                        throw new NOV.Falcon.Application.Exception("DateTime of predicate data is invalid: \"" + predicateData.Name + ", " + predicateData.Value + "\"");
                    }
                    break;
                default:
                    throw new NOV.Falcon.Application.Exception("Operator not implemented for DateTime: " + predicateData.ComparisonOperator);
            }
            reportInstanceInner.PredicateExpression.Add(datePredicateExpressionResultField == 1);
            break;
        default:
            throw new NOV.Falcon.Application.Exception("Predicate data type not supported: " + predicateData.DataType);
    }
    reportInstanceInner.PredicateExpression.Add(new FieldCompareNullPredicate(ReportInstanceFields.ReportNumber, null, true));

    FieldCompareSetPredicate reportInstanceOuter = new FieldCompareSetPredicate(WizardStepDataRowInstanceFields.ReportInstanceId, null, ReportInstanceFields.ReportInstanceId, null, SetOperator.In, reportInstanceInner.PredicateExpression, reportInstanceInner.Relations);
    topFilter.PredicateExpression.Add(reportInstanceOuter);
}

PRINT 'Checking for the existence of this procedure'
IF (SELECT OBJECT_ID('usp_date_is_less_than_or_equal','P')) IS NOT NULL
    BEGIN
        PRINT 'Procedure already exists. So, dropping it'
        DROP PROC usp_date_is_less_than_or_equal
    END
GO

CREATE FUNCTION usp_date_is_less_than_or_equal
(
    @WizardStepDataFieldValue nvarchar(2000),
    @ComparisonDate DateTime 
)
RETURNS bit
AS
BEGIN
    IF ISDATE(@WizardStepDataFieldValue) = 1
        IF @WizardStepDataFieldValue <= @ComparisonDate
            return 1
    RETURN 0
END
GO
PRINT 'Created procedure usp_date_is_less_than_or_equal'

PRINT 'Granting EXECUTE permission on usp_date_is_less_than_or_equal to all users'
GRANT EXEC ON usp_date_is_less_than_or_equal TO public

SET NOCOUNT OFF
GO