I have the following SQL that I am trying to implement in LLBLGen:

select wbic.reporting_well_id from wellbore_company_interest wbic where wbic.wellbore_id = WCD.wellbore_id and wbic.reporting_well_id = (select min(reporting_well_id) from wellbore_company_interest wbic2 where wbic2.wellbore_id = wbic.wellbore_id and sysdate >= wbic2.reporting_well_eff_dt and sysdate <= nvl( wbic2.reporting_well_exp_dt, to_date(‘3000-01-01’,‘yyyy-mm-dd’)) and sysdate >= wbic.reporting_well_eff_dt and sysdate <= nvl( wbic.reporting_well_exp_dt, to_date(‘3000-01-01’,‘yyyy-mm-dd’))

So just on the inner select, I came up with the following:

Dim well as New WELL_CURRENT_DATAEntity(1000) '(just for example) Dim minReportingWellID As Long Dim wbciCollection As New WELLBORE_COMPANY_INTERESTCollection Dim filter As llb.IPredicateExpression = New llb.PredicateExpression

filter.Add(WELLBORE_COMPANY_INTERESTFields.WELLBORE_ID = Well.WELLBORE_ID) filter.AddWithAnd(WELLBORE_COMPANY_INTERESTFields.REPORTING_WELL_EFF_DT <= System.DateTime.Now) filter.AddWithAnd(WELLBORE_COMPANY_INTERESTFields.REPORTING_WELL_EXP_DT >= System.DateTime.Now Or WELLBORE_COMPANY_INTERESTFields.REPORTING_WELL_EXP_DT = System.DBNull.Value)

minReportingWellID = CType(wbciCollection.GetScalar(WELLBORE_COMPANY_INTERESTFieldIndex.REPORTING_WELL_ID, (WELLBORE_COMPANY_INTERESTFields.REPORTING_WELL_ID * 1), AggregateFunction.Min, filter), Long)

I was then going to use minReportingWellID to execute the outer portion of the query.

Questions: 1) Is this the proper way to do this? It seems odd that there isn't an overload for GetScalar that accepts just the Field, AggregateFunction, and filter....why do I have to use the bogus expression?

2) Is there a way to execute the entire query in one step?

This can be done in one query as follows (Sample code not tested):

Use a ScalarQueryExpression to implement the inner sub-query:

IExpression rightOperand = new ScalarQueryExpression(WELLBORE_COMPANY_INTERESTFieldIndex.REPORTING_WELL_ID.SetAggregateFunction( AggregateFunction.Min), myInnerFilter);

Then use the above ScalarQueryExpression in a FieldCompareExpressionPredicate:

IPredicate filter = FieldCompareExpressionPredicate(WELLBORE_COMPANY_INTERESTFieldIndex.REPORTING_WELL_ID, null, ExOp.Equal, rightOperand);