Hi there

I'm having an issue with a query I'm working on with dynamic relationships.

Here is my sql. SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4 FROM Table1, Table2 WHERE (Table1.PIN_Number = Table2.PIN_Number AND Table2.Field6 = [myPIN] AND (Table2.DateJoinedGroup IS NULL OR Table2.DateJoinedGroup <= '[mToday]') AND (Table2.DateLeftGroup IS NULL OR Table2.DateLeftGroup >= '[mToday]') AND (ActualOn IS NULL) AND (ActualOff IS NULL) AND (Notified = 0))

As you can see Table1.Pin_Number = Table2.PIN_Number This is not a current relationship on the database so I need to use a Dynamic Relationship.

This is what I have so far. I have not added any filters yet except the Pin fields.DefineField(Table1Fields.Field1.SetObjectAlias("ca"), 0) fields.DefineField(Table1Fields.Field2.SetObjectAlias("ca"), 1) fields.DefineField(Table1Fields.Field3.SetObjectAlias("ca"), 2) fields.DefineField(Table1Fields.Field4.SetObjectAlias("ca"), 3) fields.DefineField(Table1Fields.Field5.SetObjectAlias("ca"), 4)

    ' Define filter
    filter.Add(Table1Fields.PinNumber = iPin)

    'Define Derived table as Relationship does not exist
    dtDefinition = New DerivedTableDefinition(fields, "be", filter)

    relations = New DynamicRelation(dtDefinition, JoinHint.Inner, _
                                    EntityType.Table1Entity, "ca", _
                                    (New EntityField(Table2FieldIndex.PinNumber.ToString(), "be", _
                                    GetType(String)) = Table1Fields.PinNumber.SetObjectAlias("ca")))

    relationsToUse.Add(relations)
    relationsToUse.SelectListAlias = "ca"

    oTable1.GetMulti(Nothing, 99999, sortClauses:=sorter, relations:=relationsToUse) ' 99999 is max records back

These are a couple of the errors back from Trace.] SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "myDB.dbo.Table1.PIN_Number" could not be bound. The multi-part identifier "LPA_c2.Field1" could not be bound.

It appears that it is picking up all the fields, I only want certain fields.

This is the resulting SQL just before it crashed SELECT [LPA_c2].[Field1], [LPA_c2].[Field2], [LPA_c2].[Field3], [LPA_c2].[Field4], [LPA_c2].[PIN_Number] AS [PinNumber], [LPA_c2].[Field6], [LPA_c2].[Field7], [LPA_c2].[Field8], [LPA_c2].[Field9]

FROM ( (SELECT [LPA_c2].[Field1], [LPA_c2].[Field4], [LPA_c2].[Field3], [LPA_c2].[Field4], [LPA_c2].[PIN_Number] AS [PinNumber] FROM [myDB].[dbo].[Table1] [ca] WHERE ( [myDB].[dbo].[Table1].[PIN_Number] = @PinNumber1)) [LPA_b1] INNER JOIN [myDB].[dbo].[Table1] [LPA_c2] ON [LPA_b1].[PinNumber] = [LPA_c2].[PIN_Number]) ORDER BY [myDB].[dbo].[Table1].[Field8] ASC,[myDB].[dbo].[Table1].[Field1] ASC

As you can see, I'm getting this. Select All Fields from Select my selected fields from Table1 where pin = @pinNumber1 Inner Join Table 1 On PinNumber = Table1 Pin Number. Order By Table1 fields.

I want this. Select only myFields from Table1 inner join Table2 On Table1.PinNumber = Table2.PinNumber Where Table 1 filter Order by Table1 fields

I know my join is incorrect but when I change it around I get different errors. Can you please fill in the blanks on where I'm going wrong.?

Thanks

SELECT Table1.Field1,
Table1.Field2,
Table1.Field3,
Table1.Field4 
FROM Table1, Table2 
WHERE (Table1.PIN_Number = Table2.PIN_Number AND Table2.Field6 = [myPIN] AND (Table2.DateJoinedGroup IS NULL OR Table2.DateJoinedGroup <= '[mToday]') AND (Table2.DateLeftGroup IS NULL OR Table2.DateLeftGroup >= '[mToday]') AND (ActualOn IS NULL) AND (ActualOff IS NULL) AND (Notified = 0))

From your first sql query, you don't need DynamicRelations. DynamicRelations are for this kind of sql queries:

SELECT o.*
FROM
(
    SELECT  CustomerId 
    FROM    Customers
    WHERE   Country = @country
) c INNER JOIN Orders o ON
  c.CustomerId = o.CustomerId

So, you only need to construct a custom relation. Here is an example: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14517