Translate SQL 'IF' to dynamic list

Posts   
 
    
scotru
User
Posts: 104
Joined: 16-Feb-2006
# Posted on: 05-May-2013 01:59:08   

Is it possible to translate an SQL if to a dynamic list? I've got SQL with a field clause:

  if(isnull(sum(worship_credits.credits)),0,sum(worship_credits.credits)) AS credits_earned 

Can I translate this to a dynamic list? Here's what I have without the IF to handle NULLs:

            fields.DefineField(WorshipCreditsFieldIndex.Credits, 8, "credits_earned");
            fields[8].AggregateFunctionToApply = AggregateFunction.Sum;

I feel like this may be obvious--but I'm not seeing it.

Thanks in advance for any help!

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 06-May-2013 06:11:33   

You can write that as a ScalarQueryExpression and some additional DBFunctionCalls. This is an approximate code:

fields.DefineField(WorshipCreditsFieldIndex.Credits, 8, "credits_earned");

IExpression creditsEarnedExp = new ScalarQueryExpression(
    WorshipCreditsFieldIndex.Credits.SetAggregateFunction(AggregateFunction.Sum),
    null, null);

IExpression creditsEarnedExpIsNullExp = new DbFunctionCall("IsNull",
    new object[]{creditsEarnedExp  });

IExpression creditsEarnedExpIFExp = new DbFunctionCall("IF",
    new object[]{creditsEarnedExpIsNullExp , 0, creditsEarnedExpIsNullExp  });

fields[8].ExpressionToApply = creditsEarnedExpIFExp;

I wrote an article about LLBLGen expressions, if you want to take a look: http://www.llblgening.com/archive/2009/09/llblgen-pro-expressions-and-scalar-queries/

David Elizondo | LLBLGen Support Team