Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Translate SQL 'IF' to dynamic list
 

Pages: 1
LLBLGen Pro Runtime Framework
Translate SQL 'IF' to dynamic list
Page:1/1 

  Print all messages in this thread  
Poster Message
scotru
User



Location:
College Place, WA
Joined on:
16-Feb-2006 01:03:42
Posted:
104 posts
# 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:

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

Code:
            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!
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8110 posts
# Posted on: 06-May-2013 06:11:33.  
You can write that as a ScalarQueryExpression and some additional DBFunctionCalls. This is an approximate code:
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'ing (articles and code snippets) | linkedin | twitter
 
Top
Pages: 1  


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

Version: 2.1.12172008 Final.