Hi,

I Would like to write the following SQL into LLBLGen using SelfServicing.

SQL:

select sum(y.x)
from (
    SELECT COUNT(DISTINCT RuleHistoryID) as x
    FROM MonitorMailHistory  
    WHERE MailMessageID = 29699914 AND NotificationID IS NOT NULL
    GROUP BY TransactionID
) as y

Are there some examples of documents on how to do this?

First I had only the folling SQL

SQL:

SELECT COUNT(DISTINCT RuleHistoryID) as x
    FROM MonitorMailHistory  
    WHERE MailMessageID = 29699914 AND NotificationID IS NOT NULL
    GROUP BY TransactionID

To solve this I justed an ScalarQueryExpression but how can I do two ScalarQueryExpressions in one field.

fields.DefineField(New EntityField2("ruleCount", _
                                                       New ScalarQueryExpression(MonitorMailHistoryFields.RuleHistoryID.SetAggregateFunction(AggregateFunction.CountDistinct), _
                                                                                 ruleCountFilter, Nothing, Nothing, ruleCountGroupBy)), 4)

Thx

First I had only the folling SQL

SQL: Code:

SELECT COUNT(DISTINCT RuleHistoryID) as x FROM MonitorMailHistory WHERE MailMessageID = 29699914 AND NotificationID IS NOT NULL GROUP BY TransactionID

To solve this I justed an ScalarQueryExpression but how can I do two ScalarQueryExpressions in one field.

Code:

fields.DefineField(New EntityField2("ruleCount", _ New ScalarQueryExpression(MonitorMailHistoryFields.RuleHistoryID.SetAggregateFunction(AggregateFunction.CountDistinct), _ ruleCountFilter, Nothing, Nothing, ruleCountGroupBy)), 4)

No need for a ScalarQueryExpression just use the following:

fields.DefineField(MonitorMailHistoryFields.RuleHistoryID, 4, "ruleCount", AggregateFunction.Count);

select sum(y.x) from ( SELECT COUNT(DISTINCT RuleHistoryID) as x FROM MonitorMailHistory WHERE MailMessageID = 29699914 AND NotificationID IS NOT NULL GROUP BY TransactionID ) as y

I think you need to look at Derived tables and dynamic relations