We've got a fairly complex schema we're using that has some very tricky effective dates. Here's an example of the type of schema we're talking about:

TBL_EMPLOYEE

Employee_Row_Id (uniqueidentifier, pk) Employee_Id (uniqueidentifier) Name (string) Effective_Date (datetime)

TBL_JOB

Job_Row_Id (uniqueidentifier, pk) Employee_Row_Id (uniqueidentifier, fk) Name (string) Effective_Date

These are simply examples, not actual tables in use, but they have the same field concepts. There is a relation between Job.Employee_Row_Id and Employee.Employee_Row_Id.

Here's the query we need to run to get the current employee record for a given job (assuming that the Employee_Row_Id doesn't always go to the current employee record):

SELECT * FROM TBL_EMPLOYEE E
 WHERE E.EMPLOYEE_ID = (SELECT EMPLOYEE_ID 
                  FROM TBL_EMPLOYEE E3, TBL_JOB J 
                 WHERE E3.EMPLOYEE_ROW_ID = J.EMPLOYEE_ROW_ID 
               AND J.JOB_ROW_ID = @JobID)
   AND E.EFFECTIVE_DATE IN (SELECT MAX(E2.EFFECTIVE_DATE)
                  FROM TBL_EMPLOYEE E2
                 WHERE E2.EFFECTIVE_DATE <= GETDATE()
                   AND E2.EMPLOYEE_ID = E.EMPLOYEE_ID)

Now, the problem I'm running into is how to handle that reference to E.EMPLOYEE_ID from the outer query in the second sub query. We're using Self-Servicing at the moment. Any ideas?

As long as you alias the Tbl_employee in the subqueries, this will be ok, as the database system will make it a co-related subquery automatically. So don't specify an alias for E on the fields from E.

Let me just make sure I'm clear on this - I should make an alias for EMPLOYEE_ID from the outer query, and use that in the inner query? I guess I didn't read far enough into the documentation, and didn't know you could do that with the mapper! How would you do that in code?

ConfluentData wrote:

Let me just make sure I'm clear on this - I should make an alias for EMPLOYEE_ID from the outer query, and use that in the inner query? I guess I didn't read far enough into the documentation, and didn't know you could do that with the mapper! How would you do that in code?

You should alias the inner table.

It's similar to this: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3401 and http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3382

which should get you started

I think we're on the verge of cracking this nut, but unfortunately I'm getting null reference with this code:

PredicateExpression InnerQuery1 = new PredicateExpression(PredicateFactory.CompareValue(JobFieldIndex.JobRowId, ComparisonOperator.Equal, JobID));

RelationCollection InnerRelations1 = new RelationCollection();
InnerRelations1.Add(EmployeeEntity.Relations.JobEntityUsingEmployeeRowId);

FieldCompareSetPredicate Joiner1 = new FieldCompareSetPredicate(EntityFieldFactory.Create(EmployeeFieldIndex.EmployeeId), EntityFieldFactory.Create(EmployeeFieldIndex.EmployeeId), SetOperator.Equal, InnerQuery1, InnerRelations1);


PredicateExpression InnerQuery2 = new PredicateExpression(PredicateFactory.CompareValue(EmployeeFieldIndex.EffectiveDate, ComparisonOperator.LessEqual, DateTime.Now));
InnerQuery2.AddWithAnd(PredicateFactory.CompareExpression(EmployeeFieldIndex.EmployeeId, ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(EmployeeFieldIndex.EmployeeId)), "E2"));

IEntityField OuterEffectiveDate = EntityFieldFactory.Create(EmployeeFieldIndex.EffectiveDate);
IEntityField InnerEffectiveDate = EntityFieldFactory.Create(EmployeeFieldIndex.EffectiveDate);
InnerEffectiveDate.Alias = "E2";
InnerEffectiveDate.AggregateFunctionToApply = AggregateFunction.Max;

FieldCompareSetPredicate Joiner2 = new FieldCompareSetPredicate(OuterEffectiveDate, null, InnerEffectiveDate, null, SetOperator.Equal, InnerQuery2);

PredicateExpression Filter = new PredicateExpression(Joiner1);
Filter.AddWithAnd(Joiner2);

EmployeeCollection Employees = new EmployeeCollection();
Employees.GetMulti(Filter);

It looks like the null reference is ocurring on the "Joiner2" predicate with those nulls in there. Without them, I get an error telling me there's not table aliased for "E2". Any ideas?

ConfluentData wrote:

I think we're on the verge of cracking this nut, but unfortunately I'm getting null reference with this code:

(...)

It looks like the null reference is ocurring on the "Joiner2" predicate with those nulls in there. Without them, I get an error telling me there's not table aliased for "E2". Any ideas?

The second subquery uses the adapter constructor, so you should remove the null values. You specify a relation, and an alias in the first subquery. The relation though hasn't an alias set for either entity, so you will get a FROM clause without aliases for the tables, though you use an alias 'E2' for the field in the where clause, which can't find the table, as 'E2' isnt in the first subquery's FROM clause.

However! , I now see you don't need the first subquery. Your original query:

SELECT  * 
FROM    TBL_EMPLOYEE E
WHERE   E.EMPLOYEE_ID = 
(
    SELECT  EMPLOYEE_ID
    FROM    TBL_EMPLOYEE E3, TBL_JOB J
    WHERE   E3.EMPLOYEE_ROW_ID = J.EMPLOYEE_ROW_ID
            AND 
            J.JOB_ROW_ID = @JobID
)
AND E.EFFECTIVE_DATE IN 
(
    SELECT  MAX(E2.EFFECTIVE_DATE)
    FROM    TBL_EMPLOYEE E2
    WHERE   E2.EFFECTIVE_DATE <= GETDATE()
            AND 
            E2.EMPLOYEE_ID = E.EMPLOYEE_ID
)

can also be written as:

SELECT  * 
FROM    TBL_EMPLOYEE E INNER JOIN TBL_JOB J
        ON E.EMPLOYEE_ROW_ID = J.EMPLOYEE_ROW_ID
WHERE   J.JOB_ROW_ID = @JobID
AND E.EFFECTIVE_DATE IN 
(
    SELECT  MAX(E2.EFFECTIVE_DATE)
    FROM    TBL_EMPLOYEE E2
    WHERE   E2.EFFECTIVE_DATE <= GETDATE()
            AND 
            E2.EMPLOYEE_ID = E.EMPLOYEE_ID
)

Which simply requires you to pass the relation to the GetMulti, and a simple fieldcomparevalue predicate on jobid + the second subquery you have now. No need for aliasing E and J.