Hi, I'm using LLBL 2.6 and I'm trying to generate specific SQL and am having trouble with one aspect of my join to a derived table. I am trying to write the following SQL Query:

SELECT * FROM pt_FlowsheetEntry_Elements pfe
JOIN
(
    SELECT Name, MAX(Audit_Created_On) as CreatedDate
    FROM pt_FlowsheetEntry_Elements
    WHERE (Value <> '' AND NOT Value IS NULL)
    AND FlowsheetEntry_ID IN (SELECT ID FROM pt_FlowsheetEntry WHERE            Patient_ID = 'dd6650a2-20c7-449a-b6ee-e308d55ebcf6')
    GROUP BY Name
) inner_pfe
ON pfe.Audit_Created_On = inner_pfe.CreatedDate AND pfe.Name = inner_pfe.Name

The code I've written is:

ResultsetFields dtFields = new ResultsetFields(2);
dtFields.DefineField(PatientFlowsheetEntryElementFields.Name, 0);
dtFields.DefineField(PatientFlowsheetEntryElementFields.AuditCreatedOn, 1);
dtFields[1].AggregateFunctionToApply = AggregateFunction.Max;
GroupByCollection dtGroupBy = new GroupByCollection(dtFields[0]);

PredicateExpression filter = new PredicateExpression();
filter.Add(PatientFlowsheetEntryElementFields.Value != DBNull.Value);
filter.AddWithOr(PatientFlowsheetEntryElementFields.Value != String.Empty);

PredicateExpression filter1 = new PredicateExpression();
filter1.Add(new FieldCompareSetPredicate(PatientFlowsheetEntryElementFields.FlowsheetEntryId,
                PatientFlowsheetEntryFields.Id, SetOperator.In, (PatientFlowsheetEntryFields.PatientId == visitdata.patient.Id)));

filter.AddWithAnd(filter1);

DerivedTableDefinition dtDefinition = new DerivedTableDefinition(dtFields, "dt_pfe", filter, dtGroupBy);

EntityField derivedOnClause = new EntityField(PatientFlowsheetEntryElementFieldIndex.AuditCreatedOn.ToString    (), "dt_pfe", typeof(DateTime));
DynamicRelation relation = new DynamicRelation(dtDefinition, JoinHint.Inner,    EntityType.PatientFlowsheetEntryElementEntity, "pfe", derivedOnClause ==    PatientFlowsheetEntryElementFields.AuditCreatedOn.SetObjectAlias("pfe"));

EntityField derivedOnClause1 = new EntityField(PatientFlowsheetEntryElementFieldIndex.Name.ToString(),
    "dt_pfe", typeof(String));
DynamicRelation relation1 = new DynamicRelation(dtDefinition, JoinHint.Inner,   EntityType.PatientFlowsheetEntryElementEntity, "pfe1", derivedOnClause1 ==  PatientFlowsheetEntryElementFields.Name.SetObjectAlias("pfe1"));


RelationCollection relations = new RelationCollection();
relations.Add(relation);
relations.Add(relation1);
            
PatientFlowsheetEntryElementCollection elementCollection = new PatientFlowsheetEntryElementCollection();
elementCollection.GetMulti(null, relations);

The result is this (I've replaced all the fields with a "*"):

SELECT *
FROM (
( (
    SELECT [dbo].[pt_FlowsheetEntry_Elements].[Name], 
    MAX([dbo].[pt_FlowsheetEntry_Elements].[Audit_Created_On]) AS [AuditCreatedOn] 
    FROM [dbo].[pt_FlowsheetEntry_Elements]  
    WHERE 
    ( [dbo].[pt_FlowsheetEntry_Elements].[Value] IS NOT NULL OR [dbo].[pt_FlowsheetEntry_Elements].[Value]  <> @Value1 AND ( [dbo].[pt_FlowsheetEntry_Elements].[FlowsheetEntry_ID] IN (SELECT [dbo].   [pt_FlowsheetEntry].[ID] AS [Id] FROM [dbo].[pt_FlowsheetEntry]  WHERE [dbo].[pt_FlowsheetEntry].   [Patient_ID] = @PatientId2))
        )
    GROUP BY [dbo].[pt_FlowsheetEntry_Elements].[Name]
    ) [LPA_d1] 
INNER JOIN [dbo].[pt_FlowsheetEntry_Elements] [LPA_p2]  ON  [LPA_d1].[AuditCreatedOn] = [LPA_p2].[Audit_Created_On]) 
INNER JOIN [dbo].[pt_FlowsheetEntry_Elements] [LPA_p3]  ON  [LPA_d1].[Name] = [LPA_p3].[Name]) 

This is correct except for the fact that I'm getting 2 inner joins rather than 1 with an "AND" clause. I've tried changing the alias for the second relations to "pfe" to match the first, but then the second relation seems to have no effect. I admit I'm new to LLBL so am struggling with some of the concepts.

You don't have to create two dynamic relations. Just add a second clause to the predicate. This is an approximate code:

ResultsetFields dtFields = new ResultsetFields(2);
dtFields.DefineField(PatientFlowsheetEntryElementFields.Name, 0);
dtFields.DefineField(PatientFlowsheetEntryElementFields.AuditCreatedOn, 1);
dtFields[1].AggregateFunctionToApply = AggregateFunction.Max;
GroupByCollection dtGroupBy = new GroupByCollection(dtFields[0]);

PredicateExpression filter = new PredicateExpression();
filter.Add(PatientFlowsheetEntryElementFields.Value != DBNull.Value);
filter.AddWithOr(PatientFlowsheetEntryElementFields.Value != String.Empty);

PredicateExpression filter1 = new PredicateExpression();
filter1.Add(new FieldCompareSetPredicate(PatientFlowsheetEntryElementFields.FlowsheetEntryId,
                PatientFlowsheetEntryFields.Id, SetOperator.In, (PatientFlowsheetEntryFields.PatientId == visitdata.patient.Id)));

filter.AddWithAnd(filter1);

DerivedTableDefinition dtDefinition = new DerivedTableDefinition(dtFields, "dt_pfe", filter, dtGroupBy);

EntityField derivedOnClause = new EntityField(PatientFlowsheetEntryElementFieldIndex.AuditCreatedOn.ToString    (), "dt_pfe", typeof(DateTime));

EntityField derivedOnClause1 = new EntityField(PatientFlowsheetEntryElementFieldIndex.Name.ToString(),
    "dt_pfe", typeof(String));

DynamicRelation relation = new DynamicRelation(dtDefinition, JoinHint.Inner,     EntityType.PatientFlowsheetEntryElementEntity, "pfe", derivedOnClause ==    PatientFlowsheetEntryElementFields.AuditCreatedOn.SetObjectAlias("pfe")
     & derivedOnClause1 ==   PatientFlowsheetEntryElementFields.Name.SetObjectAlias("pfe1"));

RelationCollection relations = new RelationCollection();
relations.Add(relation);

...

Perfect! Thanks a ton.

Hi,

I'm running into one more problem. The query generated has select fields like "[pt_FlowsheetEntry_Elements].[ID]". Which is normally fine except we've aliased this table to LPA_p2. So I'm getting a "The multi-part identifier" error on all my selected fields. Can you tell me how I tell LLBL to use the correct alias?

Thanks!

SELECT DISTINCT 
[dbo].[pt_FlowsheetEntry_Elements].[ID] AS [Id], 
[dbo].[pt_FlowsheetEntry_Elements].[FlowsheetEntry_ID] AS [FlowsheetEntryId],
[dbo].[pt_FlowsheetEntry_Elements].[Name],
[dbo].[pt_FlowsheetEntry_Elements].[Audit_Created_On] AS [AuditCreatedOn]
FROM (
( (
    SELECT [dbo].[pt_FlowsheetEntry_Elements].[Name], 
    MAX([dbo].[pt_FlowsheetEntry_Elements].[Audit_Created_On]) AS [AuditCreatedOn] 
    FROM [dbo].[pt_FlowsheetEntry_Elements] 
    WHERE 
    ( [dbo].[pt_FlowsheetEntry_Elements].[Value] IS NOT NULL OR [dbo].[pt_FlowsheetEntry_Elements].[Value]   <> @Value1 AND ( [dbo].[pt_FlowsheetEntry_Elements].[FlowsheetEntry_ID] IN (SELECT [dbo].  [pt_FlowsheetEntry].[ID] AS [Id] FROM [dbo].[pt_FlowsheetEntry] WHERE [dbo].[pt_FlowsheetEntry].    [Patient_ID] = @PatientId2))
        )
    GROUP BY [dbo].[pt_FlowsheetEntry_Elements].[Name]
    ) [LPA_d1] 
INNER JOIN [dbo].[pt_FlowsheetEntry_Elements] [LPA_p2] ON [LPA_d1].[AuditCreatedOn] = [LPA_p2].[Audit_Created_On]) 
INNER JOIN [dbo].[pt_FlowsheetEntry_Elements] [LPA_p3] ON [LPA_d1].[Name] = [LPA_p3].[Name]) 

never mind. Figured it out. Just using:

relations.SelectListAlias = "pfe";