We tried to achieve: select tavpompof.oid, tavpomadresse.hauptadresse from tavpompof, tavpomadresse where tavpompof.oid = 'F8B820C34B011XO' and tavpompof.oid = tavpomadresse.pof_id (+)
and tavpomadresse.hauptadresse (+) = 1 Result: 1 record found

We read that we have to use AnsiJoins: Or with the parameter <add key="OracleAnsiJoins" value="true" /> in the app.config:

But it didn't work either: select tavpompof.oid, tavpomadresse.hauptadresse from tavpompof left outer join tavpomadresse on (tavpompof.oid = tavpomadresse.pof_id) where tavpompof.oid = 'F8B820C34B011XO' and tavpomadresse.hauptadresse = 1 Result: No record found

Expected (and correct) result would be: select tavpompof.oid, tavpomadresse.hauptadresse from tavpompof left outer join tavpomadresse on (tavpompof.oid = tavpomadresse.pof_id and tavpomadresse.hauptadresse = 1) where tavpompof.oid = 'F8B820C34B011XO'

Can you please check?

Thanks and Regards Wolfgang

(Version: LLBL Gen 2.0.0.0 Final / February 14th, 2007)

Any Feedback?

select tavpompof.oid, tavpomadresse.hauptadresse from tavpompof left outer join tavpomadresse on (tavpompof.oid = tavpomadresse.pof_id and tavpomadresse.hauptadresse = 1) where tavpompof.oid = 'F8B820C34B011XO'

Would you please post the code you used to try to achieve the above query?

Most probably you didn't set the CustomFilter for the entityRelation used. This should append the (and tavpomadresse.hauptadresse = 1) predicate to the On Clause.

Perfekt! You are right. It was the CustomFilter. This works now for the example and I already began a dynamic list with my needs but I'm stucked on the nested releations and the multiple use of one table (Pim/PomWertebereich_S):

Here is my starting code:

        public static Collection<PersonQueryResult> ListMitarbeiter()
        {
            DataAccessAdapter adapter = new DataAccessAdapter();
            ResultsetFields fields = new ResultsetFields(25);
            fields.DefineField(PersonFields.Oid, 0, "PersonId", "Person");
            fields.DefineField(MitarbeiterFields.Oid, 1, "MitarbeiterId", "Mitarbeiter");
            fields.DefineField(MitarbeiterFields.AhvNr, 2, "AhvNr", "Mitarbeiter");
            fields.DefineField(MitarbeiterFields.PersonalNr, 3, "PersonalNr", "Mitarbeiter");
            fields.DefineField(MitarbeiterFields.Geburtsdatum, 4, "Geburtsdatum", "Mitarbeiter");
            fields.DefineField(PomWertebereichSFields.WerteBez, 5, "GeschlechtText", "PomWertebereichS"); 
            fields.DefineField(PimWertebereichSFields.WerteBez, 6, "DienstgradText", "PimWertebereichS_1"); 
            fields.DefineField(PomWertebereichSFields.WerteBez, 7, "BerufsTitelText", "PomWertebereichS_1"); 
            fields.DefineField(PimWertebereichSFields.WerteBez, 8, "BesoldungsklasseText", "PimWertebereichS");
            fields.DefineField(PersonFields.PofCode, 9, "PofCode", "Person");
            fields.DefineField(PersonFields.PofName, 10, "PofName", "Person");
            fields.DefineField(PersonFields.PofNameA, 11, "PofNameA", "Person");
            fields.DefineField(PersonFields.Vorname, 12, "PofVorname", "Person");
            fields.DefineField(PersonFields.VornameA, 13, "PofVornameA", "Person");
            fields.DefineField(PersonFields.Mitarbeiter, 14, "Mitarbeiter", "Person");
            fields.DefineField(PersonFields.OriginalPofId, 15, "OriginalPofId", "Person");
            fields.DefineField(PersonFields.Deaktiviert, 16, "Deaktiviert", "Person");
            fields.DefineField(PersonFields.Poftyp, 17, "Poftyp", "Person");
            fields.DefineField(PersonFields.StammoeBez, 18, "StammoeBez", "Person");
            fields.DefineField(PersonFields.DeaktiviertZeitpunkt, 19, "DeaktiviertZeitpunkt", "Person");
            fields.DefineField(AdresseFields.Postleitzahl, 20, "Postleitzahl", "Adresse");
            fields.DefineField(AdresseFields.Ortschaft, 21, "Ortschaft", "Adresse");
            fields.DefineField(AdresseFields.Strasse, 22, "Strasse", "Adresse");
            fields.DefineField(AdresseFields.AdressZusatz, 23, "AdressZusatz", "Adresse");
            fields.DefineField(AdresseFields.HausNr, 24, "HausNr", "Adresse");

         IRelationPredicateBucket bucket = new RelationPredicateBucket();

         EntityRelation relation = (EntityRelation)PersonEntity.Relations.AdresseEntityUsingPofId;
         relation.CustomFilter = new PredicateExpression(AdresseFields.Hauptadresse == 1);
         bucket.Relations.Add(relation, JoinHint.Left);

         relation = (EntityRelation)PimWertebereichSEntity.Relations.BesoldungsklasseEntityUsingWbId;
         relation.CustomFilter = new PredicateExpression(PimWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, JoinHint.Left);

         relation = (EntityRelation)PimWertebereichSEntity.Relations.DienstgradEntityUsingWbId;
         relation.CustomFilter = new PredicateExpression(PimWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, JoinHint.Left);

            DataTable dynamicList = new DataTable();

         adapter.FetchTypedList(fields, dynamicList, bucket);
        
            return null;
        }

If I try to run my code there is now an ORMRelationException it thrown:

Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?

So I cannot see the generated code. :-( I wanted to check the aliases for the PimWertebereich_S-Tables as well.

Can you please send me the code how to convert following statement in a dynamic list? (please attend the "...-_1"-aliases in the FROM-Part).

SELECT 
  TAVPOMPOF.OID AS Person_Id,
  TAVPIMMITARBEITER.OID AS Mitarbeiter_Id,
  TAVPIMMITARBEITER.AHV_NR AS Ahv_Nr,
  TAVPIMMITARBEITER.PERSONAL_NR AS Personal_Nr,
  TAVPIMMITARBEITER.GEBURTSDATUM AS Geburtsdatum,
  TAVPiMWERTEBEREICH_S.WERTE_BEZ AS Dienstgrad_Text,
  TAVPOMWERTEBEREICH_S_1.WERTE_BEZ AS Berufs_Titel_Text,
  TAVPOMWERTEBEREICH_S.WERTE_BEZ AS Geschlecht_Text,
  TAVPiMWERTEBEREICH_S_1.WERTE_BEZ AS Besoldungsklasse_Text,
  TAVPOMPOF.POF_CODE AS Pof_Code,
  TAVPOMPOF.POF_NAME AS Pof_Name,
  TAVPOMPOF.POF_NAME_A AS Pof_Name_A,
  TAVPOMPOF.VORNAME AS Pof_Vorname,
  TAVPOMPOF.VORNAME_A AS Pof_Vorname_A,
  TAVPOMPOF.MITARBEITER AS Mitarbeiter,
  TAVPOMPOF.ORIGINAL_POF_ID AS Original_Pof_Id,
  TAVPOMPOF.DEAKTIVIERT AS Deaktiviert,
  TAVPOMPOF.POFTYP AS Poftyp,
  TAVPOMPOF.STAMMOE_BEZ AS Stammoe_Bez,
  TAVPOMPOF.DEAKTIVIERT_ZEITPUNKT AS Deaktiviert_Zeitpunkt,
  TAVPOMADRESSE.POSTLEITZAHL AS Postleitzahl,
  TAVPOMADRESSE.ORTSCHAFT AS Ortschaft,
  TAVPOMADRESSE.STRASSE AS Strasse,
  TAVPOMADRESSE.ADRESS_ZUSATZ AS Adress_Zusatz,
  TAVPOMADRESSE.HAUS_NR AS Haus_Nr
FROM 
  ((TAVPOMADRESSE RIGHT JOIN 
  ((TAVPIMBESOLDUNG LEFT JOIN TAVPIMBESOLDKL ON TAVPIMBESOLDUNG.BESOLDUNGSKLASSE_ID = TAVPIMBESOLDKL.OID) 
  RIGHT JOIN ((((TAVPOMPOF LEFT JOIN TAVPIMMITARBEITER ON TAVPOMPOF.OID = TAVPIMMITARBEITER.PERSON_ID) 
  LEFT JOIN TAVPIMDIENSTGRAD ON TAVPIMMITARBEITER.INF_DIENSTGRAD_ID = TAVPIMDIENSTGRAD.OID) 
  LEFT JOIN TAVPOMBERUFSTITEL ON TAVPOMPOF.BERUFSTITEL_ID = TAVPOMBERUFSTITEL.OID) 
  INNER JOIN TAVPOMGESCHLECHT ON TAVPOMPOF.GESCHLECHT_ID = TAVPOMGESCHLECHT.OID)
  ON (TAVPIMBESOLDUNG.MITARBEITER_ID = TAVPIMMITARBEITER.OID  AND BESOLDUNG_VON >= SYSDATE AND BESOLDUNG_BIS < SYSDATE)) 
  ON TAVPOMADRESSE.POF_ID = TAVPOMPOF.OID AND HAUPTADRESSE = 1) 
  LEFT JOIN TAVPOMWERTEBEREICH_S ON TAVPOMGESCHLECHT.OID = TAVPOMWERTEBEREICH_S.WB_ID AND TAVPOMWERTEBEREICH_S.SPRACHE = 1 ) 
  LEFT JOIN TAVPOMWERTEBEREICH_S TAVPOMWERTEBEREICH_S_1 ON (TAVPOMBERUFSTITEL.OID = TAVPOMWERTEBEREICH_S_1.WB_ID AND  TAVPOMWERTEBEREICH_S_1.SPRACHE = 1)
  LEFT JOIN TAVPiMWERTEBEREICH_S ON TAVPIMDIENSTGRAD.OID = TAVPiMWERTEBEREICH_S.WB_ID AND TAVPiMWERTEBEREICH_S.SPRACHE = 1
  LEFT JOIN TAVPiMWERTEBEREICH_S TAVPiMWERTEBEREICH_S_1 ON TAVPIMBESOLDUNG.OID = TAVPiMWERTEBEREICH_S.WB_ID AND TAVPiMWERTEBEREICH_S_1.SPRACHE = 1
WHERE 
  (((TAVPOMPOF.POFTYP)='P') AND
  ((TAVPOMPOF.ORIGINAL_POF_ID) Is Null) AND
  ((TAVPOMPOF.DEAKTIVIERT)='0'))
ORDER BY Pof_Name, Vorname, pof_code

Kind Regards Wolfgang

IRelationPredicateBucket bucket = new RelationPredicateBucket();

     EntityRelation relation = (EntityRelation)PersonEntity.Relations.AdresseEntityUsingPofId;
     relation.CustomFilter = new PredicateExpression(AdresseFields.Hauptadresse == 1);
     bucket.Relations.Add(relation, JoinHint.Left);

     relation = (EntityRelation)PimWertebereichSEntity.Relations.BesoldungsklasseEntityUsingWbId;
     relation.CustomFilter = new PredicateExpression(PimWertebereichSFields.Sprache == 1);
     bucket.Relations.Add(relation, JoinHint.Left);

     relation = (EntityRelation)PimWertebereichSEntity.Relations.DienstgradEntityUsingWbId;
     relation.CustomFilter = new PredicateExpression(PimWertebereichSFields.Sprache == 1);
     bucket.Relations.Add(relation, JoinHint.Left);

The relation collection should be a chain of relations, it's exactly like you are selecting from One table only and adding joins (inner or outer to other tables).

And the table you are selecting from is the first entity used in the first relation, which is the PersonEntity.

So after the first relation, now you have Person and Address in your list of entities, anything that comes later should be linked to any of them, or linked to any of the previously added relations. (like a chain).

And that was not the case when you added the second relation (relation at index 1). Here you are deifining a JOIN between PimWertebereichSEntity & DienstgradEntity, while PimWertebereichSEntity haven't been joined before.

Hint: If I didn't mistake the count, you'll need to have a total of 6 different relation added to the relationCollection.

Sorry but I don't know how. Can you please guide me?

Does this mean I have to go from Adresse -> right -> Besoldung -> left -> Besoldkl -> right -> Mitarbeiter... Like the SQL? But I have brackets in the SQL. How can I implement this? And how can I use the "same" Table more than one (usualy with aliases) but where do I define this?

If you can provide me with the .net-code then I have a template. But now I'm stucked.

Thanks and regards Wolfgang

Does this mean I have to go from Adresse -> right -> Besoldung -> left -> Besoldkl -> right -> Mitarbeiter... Like the SQL?

Exactly.

But I have brackets in the SQL. How can I implement this?

I think you can flatten the Joins and execlude the brackets at all. It might be a matter of ordering the Joins.

And how can I use the "same" Table more than one (usualy with aliases) but where do I define this?

By Defining the alias for the tables that are used more than once, aliases should be passed as a parameter to an overload of the relations.Add() method.

Now make sure to let the aliases match those used in the DefineField methods (eg. PomWertebereichS_1)

If it gets too complicated, you can just put the Query into a database View and map it as a TypedView.

Joins with brackets which seem to join 2 sets which are created by separate joins are actually always rewritable as a single join statement without any brackets. Please add the extra filters you perhaps need in the ON clauses with the appropriate relation object's CustomFilter property.

Hi, I managed to rewrite the From-Clause with the same rowcount:

...
FROM
  TAVPOMPOF 
  LEFT JOIN TAVPIMMITARBEITER  ON  TAVPOMPOF.OID=TAVPIMMITARBEITER.PERSON_ID 
  LEFT JOIN TAVPIMDIENSTGRAD  ON  TAVPIMDIENSTGRAD.OID=TAVPIMMITARBEITER.INF_DIENSTGRAD_ID 
  LEFT JOIN TAVPOMBERUFSTITEL  ON  TAVPOMBERUFSTITEL.OID=TAVPOMPOF.BERUFSTITEL_ID
  LEFT JOIN TAVPOMGESCHLECHT  ON  TAVPOMGESCHLECHT.OID=TAVPOMPOF.GESCHLECHT_ID
  LEFT JOIN TAVPOMADRESSE  ON   TAVPOMPOF.OID=TAVPOMADRESSE.POF_ID AND HAUPTADRESSE = 1
  LEFT JOIN TAVPOMADRESSTYP  ON  TAVPOMADRESSTYP.OID=TAVPOMADRESSE.ADRESSTYP_ID 
  LEFT JOIN TAVPIMBESOLDUNG  ON  TAVPIMMITARBEITER.OID=TAVPIMBESOLDUNG.MITARBEITER_ID AND BESOLDUNG_VON >= SYSDATE AND BESOLDUNG_BIS < SYSDATE
  LEFT JOIN TAVPOMWERTEBEREICH_S                        ON TAVPOMGESCHLECHT.OID = TAVPOMWERTEBEREICH_S.WB_ID AND TAVPOMWERTEBEREICH_S.SPRACHE = 1  
  LEFT JOIN TAVPOMWERTEBEREICH_S TAVPOMWERTEBEREICH_S_1 ON TAVPOMBERUFSTITEL.OID = TAVPOMWERTEBEREICH_S_1.WB_ID AND  TAVPOMWERTEBEREICH_S_1.SPRACHE = 1
  LEFT JOIN TAVPiMWERTEBEREICH_S                        ON TAVPIMDIENSTGRAD.OID = TAVPiMWERTEBEREICH_S.WB_ID AND TAVPiMWERTEBEREICH_S.SPRACHE = 1
  LEFT JOIN TAVPiMWERTEBEREICH_S TAVPiMWERTEBEREICH_S_1 ON TAVPIMBESOLDUNG.OID = TAVPiMWERTEBEREICH_S.WB_ID AND TAVPiMWERTEBEREICH_S_1.SPRACHE = 1
WHERE 
...

So I think I can go a step further with the relation.CustomFilter (all with JoinHint.Left)

But I still don't know how to use the Alias for the TAVPiMWERTEBEREICH_S_1 / TAVPOMWERTEBEREICH_S_1 in C# (Dynamic List). Means I don't understand Walaas remark:

Now make sure to let the aliases match those used in the DefineField methods (eg. PomWertebereichS_1)

Thanks and Regards Wolfgang

Hello,

when you define your typedlist you add field using alias for your datatable:

fields.DefineField(PimWertebereichSFields.WerteBez, 6, "DienstgradText", "PimWertebereichS_1"); 

If you want your alias to be defined, you need to add it in your relation between the second use of "PimWertebereichS" table and the other table. You can add alias like that:

relation =xxxxTOPimWertebereichS
         bucket.Relations.Add(relation,"PimWertebereichS_1", JoinHint.Left);

In this sample the alias is used on the PimWertebereichS table.

Hi, the Alias is still not working:

C#-Code:

            DataAccessAdapter adapter = new DataAccessAdapter();
            ResultsetFields fields = new ResultsetFields(25);
            fields.DefineField(PersonFields.Oid, 0, "PersonId");
            fields.DefineField(MitarbeiterFields.Oid, 1, "MitarbeiterId");
            fields.DefineField(MitarbeiterFields.AhvNr, 2, "AhvNr");
            fields.DefineField(MitarbeiterFields.PersonalNr, 3, "PersonalNr");
            fields.DefineField(MitarbeiterFields.Geburtsdatum, 4, "Geburtsdatum");
         fields.DefineField(PomWertebereichSFields.WerteBez, 5, "GeschlechtText"); 
            fields.DefineField(PimWertebereichSFields.WerteBez, 6, "DienstgradText");
         fields.DefineField(PomWertebereichSFields.WerteBez, 7, "BerufsTitelText", "PomWertebereichS_1"); 
            fields.DefineField(PimWertebereichSFields.WerteBez, 8, "BesoldungsklasseText", "PimWertebereichS_1");
            fields.DefineField(PersonFields.PofCode, 9, "PofCode");
            fields.DefineField(PersonFields.PofName, 10, "PofName");
            fields.DefineField(PersonFields.PofNameA, 11, "PofNameA");
            fields.DefineField(PersonFields.Vorname, 12, "PofVorname");
            fields.DefineField(PersonFields.VornameA, 13, "PofVornameA");
            fields.DefineField(PersonFields.Mitarbeiter, 14, "Mitarbeiter");
            fields.DefineField(PersonFields.OriginalPofId, 15, "OriginalPofId");
            fields.DefineField(PersonFields.Deaktiviert, 16, "Deaktiviert");
            fields.DefineField(PersonFields.Poftyp, 17, "Poftyp");
            fields.DefineField(PersonFields.StammoeBez, 18, "StammoeBez");
            fields.DefineField(PersonFields.DeaktiviertZeitpunkt, 19, "DeaktiviertZeitpunkt");
            fields.DefineField(AdresseFields.Postleitzahl, 20, "Postleitzahl");
            fields.DefineField(AdresseFields.Ortschaft, 21, "Ortschaft");
            fields.DefineField(AdresseFields.Strasse, 22, "Strasse");
            fields.DefineField(AdresseFields.AdressZusatz, 23, "AdressZusatz");
            fields.DefineField(AdresseFields.HausNr, 24, "HausNr");

         IRelationPredicateBucket bucket = new RelationPredicateBucket();

         EntityRelation relation = (EntityRelation)PersonEntity.Relations.MitarbeiterEntityUsingPersonId;
         bucket.Relations.Add(relation, JoinHint.Left);

         relation = (EntityRelation)PersonEntity.Relations.BerufstitelEntityUsingBerufstitelId;
         bucket.Relations.Add(relation, JoinHint.Left);

         relation = (EntityRelation)PersonEntity.Relations.GeschlechtEntityUsingGeschlechtId;
         bucket.Relations.Add(relation, JoinHint.Left);

         relation = (EntityRelation)PersonEntity.Relations.AdresseEntityUsingPofId;
         relation.CustomFilter = new PredicateExpression(AdresseFields.Hauptadresse == 1);
         bucket.Relations.Add(relation, JoinHint.Left);

         relation = (EntityRelation)AdresseEntity.Relations.AdresstypEntityUsingAdresstypId;
         bucket.Relations.Add(relation, JoinHint.Left);

         relation = (EntityRelation)MitarbeiterEntity.Relations.DienstgradEntityUsingInfDienstgradId;
         bucket.Relations.Add(relation, JoinHint.Left);

         relation = (EntityRelation)MitarbeiterEntity.Relations.BesoldungEntityUsingMitarbeiterId;
         relation.CustomFilter = new PredicateExpression(BesoldungFields.BesoldungVon >= DateTime.Now);
         relation.CustomFilter.AddWithAnd(new PredicateExpression(BesoldungFields.BesoldungBis < DateTime.Now));
         bucket.Relations.Add(relation, JoinHint.Left);

         relation = (EntityRelation)PomWertebereichSEntity.Relations.GeschlechtEntityUsingWbId;
         relation.CustomFilter = new PredicateExpression(PomWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, JoinHint.Right);

         relation = (EntityRelation)PomWertebereichSEntity.Relations.BerufstitelEntityUsingWbId;
         relation.CustomFilter = new PredicateExpression(PomWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, "PomWertebereichS_1", JoinHint.Left);

         relation = (EntityRelation)PimWertebereichSEntity.Relations.DienstgradEntityUsingWbId;
         relation.CustomFilter = new PredicateExpression(PimWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, JoinHint.Right);

         relation = (EntityRelation)PimWertebereichSEntity.Relations.BesoldungsklasseEntityUsingWbId;
         relation.CustomFilter = new PredicateExpression(PimWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, "PimWertebereichS_1", JoinHint.Left);
            DataTable dynamicList = new DataTable();

         adapter.FetchTypedList(fields, dynamicList, bucket);

Generated SQL:

SELECT 
  "ADAM_DEV"."TAVPOMPOF"."OID" AS "PersonId",
  "ADAM_DEV"."TAVPIMMITARBEITER"."OID" AS "MitarbeiterId",
  "ADAM_DEV"."TAVPIMMITARBEITER"."AHV_NR" AS "AhvNr",
  "ADAM_DEV"."TAVPIMMITARBEITER"."PERSONAL_NR" AS "PersonalNr",
  "ADAM_DEV"."TAVPIMMITARBEITER"."GEBURTSDATUM" AS "Geburtsdatum",
  "ADAM_DEV"."TAVPOMWERTEBEREICH_S"."WERTE_BEZ" AS "GeschlechtText",
  "ADAM_DEV"."TAVPIMWERTEBEREICH_S"."WERTE_BEZ" AS "DienstgradText",
  "PomWertebereichS_1"."WERTE_BEZ" AS "BerufsTitelText",
  "PimWertebereichS_1"."WERTE_BEZ" AS "BesoldungsklasseText",
  "ADAM_DEV"."TAVPOMPOF"."POF_CODE" AS "PofCode",
  "ADAM_DEV"."TAVPOMPOF"."POF_NAME" AS "PofName",
  "ADAM_DEV"."TAVPOMPOF"."POF_NAME_A" AS "PofNameA",
  "ADAM_DEV"."TAVPOMPOF"."VORNAME" AS "PofVorname",
  "ADAM_DEV"."TAVPOMPOF"."VORNAME_A" AS "PofVornameA",
  "ADAM_DEV"."TAVPOMPOF"."MITARBEITER" AS "Mitarbeiter",
  "ADAM_DEV"."TAVPOMPOF"."ORIGINAL_POF_ID" AS "OriginalPofId",
  "ADAM_DEV"."TAVPOMPOF"."DEAKTIVIERT" AS "Deaktiviert",
  "ADAM_DEV"."TAVPOMPOF"."POFTYP" AS "Poftyp",
  "ADAM_DEV"."TAVPOMPOF"."STAMMOE_BEZ" AS "StammoeBez",
  "ADAM_DEV"."TAVPOMPOF"."DEAKTIVIERT_ZEITPUNKT" AS "DeaktiviertZeitpunkt",
  "ADAM_DEV"."TAVPOMADRESSE"."POSTLEITZAHL" AS "Postleitzahl",
  "ADAM_DEV"."TAVPOMADRESSE"."ORTSCHAFT" AS "Ortschaft",
  "ADAM_DEV"."TAVPOMADRESSE"."STRASSE" AS "Strasse",
  "ADAM_DEV"."TAVPOMADRESSE"."ADRESS_ZUSATZ" AS "AdressZusatz",
  "ADAM_DEV"."TAVPOMADRESSE"."HAUS_NR" AS "HausNr"
FROM 
  ((((((((((( "ADAM_DEV"."TAVPOMPOF"  LEFT JOIN "ADAM_DEV"."TAVPIMMITARBEITER"  ON  "ADAM_DEV"."TAVPOMPOF"."OID"="ADAM_DEV"."TAVPIMMITARBEITER"."PERSON_ID") 
  LEFT JOIN "ADAM_DEV"."TAVPOMBERUFSTITEL"  ON  "ADAM_DEV"."TAVPOMBERUFSTITEL"."OID"="ADAM_DEV"."TAVPOMPOF"."BERUFSTITEL_ID") 
  LEFT JOIN "ADAM_DEV"."TAVPOMGESCHLECHT"  ON  "ADAM_DEV"."TAVPOMGESCHLECHT"."OID"="ADAM_DEV"."TAVPOMPOF"."GESCHLECHT_ID") 
  LEFT JOIN "ADAM_DEV"."TAVPOMADRESSE"  ON  "ADAM_DEV"."TAVPOMPOF"."OID"="ADAM_DEV"."TAVPOMADRESSE"."POF_ID" AND ( "ADAM_DEV"."TAVPOMADRESSE"."HAUPTADRESSE" = 1)) 
  LEFT JOIN "ADAM_DEV"."TAVPOMADRESSTYP"  ON  "ADAM_DEV"."TAVPOMADRESSTYP"."OID"="ADAM_DEV"."TAVPOMADRESSE"."ADRESSTYP_ID") 
  LEFT JOIN "ADAM_DEV"."TAVPIMDIENSTGRAD"  ON  "ADAM_DEV"."TAVPIMDIENSTGRAD"."OID"="ADAM_DEV"."TAVPIMMITARBEITER"."INF_DIENSTGRAD_ID") 
  LEFT JOIN "ADAM_DEV"."TAVPIMBESOLDUNG"  ON  "ADAM_DEV"."TAVPIMMITARBEITER"."OID"="ADAM_DEV"."TAVPIMBESOLDUNG"."MITARBEITER_ID" 
  AND ( "ADAM_DEV"."TAVPIMBESOLDUNG"."BESOLDUNG_VON" >= sysdate AND ( "ADAM_DEV"."TAVPIMBESOLDUNG"."BESOLDUNG_BIS" < sysdate))) 
  LEFT JOIN "ADAM_DEV"."TAVPOMWERTEBEREICH_S"  ON  "ADAM_DEV"."TAVPOMGESCHLECHT"."OID"="ADAM_DEV"."TAVPOMWERTEBEREICH_S"."WB_ID" AND ( "ADAM_DEV"."TAVPOMWERTEBEREICH_S"."SPRACHE" = 1)) 
  LEFT JOIN "ADAM_DEV"."TAVPOMBERUFSTITEL" "LPA_P1"  ON  "LPA_P1"."OID"="ADAM_DEV"."TAVPOMWERTEBEREICH_S"."WB_ID" AND ( "ADAM_DEV"."TAVPOMWERTEBEREICH_S"."SPRACHE" = 1)) 
  LEFT JOIN "ADAM_DEV"."TAVPIMWERTEBEREICH_S"  ON  "ADAM_DEV"."TAVPIMDIENSTGRAD"."OID"="ADAM_DEV"."TAVPIMWERTEBEREICH_S"."WB_ID" AND ( "ADAM_DEV"."TAVPIMWERTEBEREICH_S"."SPRACHE" = 1)) 
  LEFT JOIN "ADAM_DEV"."TAVPIMBESOLDKL" "LPA_P2"  ON  "LPA_P2"."OID"="ADAM_DEV"."TAVPIMWERTEBEREICH_S"."WB_ID" AND ( "ADAM_DEV"."TAVPIMWERTEBEREICH_S"."SPRACHE" = 1))

Error Message:

ORA-00904: "PimWertebereichS_1"."WERTE_BEZ": invalid identifier

Why is the from-clause NOT using PimWertebereichS_1/PomWertebereichS_1 as aliases for the TAVPIMWERTEBEREICH_S / TAVPOMWERTEBEREICH_S (also in the customFilter .SPRACHE=1 !!) and uses generated Aliases ("LPA_P1" and "LPA_P2") for the joined tables? Where can I set the Alias in relation.CustomFilter = new PredicateExpression(PomWertebereichSFields.Sprache == 1) or is this done automatically??

         relation = (EntityRelation)PomWertebereichSEntity.Relations.GeschlechtEntityUsingWbId;
         relation.CustomFilter = new PredicateExpression(PomWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, JoinHint.Right);

         relation = (EntityRelation)PomWertebereichSEntity.Relations.BerufstitelEntityUsingWbId;
         relation.CustomFilter = new PredicateExpression(PomWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, "PomWertebereichS_1", JoinHint.Left);

         relation = (EntityRelation)PimWertebereichSEntity.Relations.DienstgradEntityUsingWbId;
         relation.CustomFilter = new PredicateExpression(PimWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, JoinHint.Right);

         relation = (EntityRelation)PimWertebereichSEntity.Relations.BesoldungsklasseEntityUsingWbId;
         relation.CustomFilter = new PredicateExpression(PimWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, "PimWertebereichS_1", JoinHint.Left);
            DataTable dynamicList = new DataTable();

The above code should be changed to look like the following:

         relation = (EntityRelation)GeschlechtEntity.Relations.PomWertebereichSEntity...
         relation.CustomFilter = new PredicateExpression(PomWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, JoinHint.Right);

         relation = (EntityRelation)BerufstitelEntity.Relations.PomWertebereichSEntity...;
         relation.CustomFilter = new PredicateExpression(PomWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, "PomWertebereichS_1", JoinHint.Left);

         relation = (EntityRelation)DienstgradEntity.Relations.PimWertebereichSEntity...;
         relation.CustomFilter = new PredicateExpression(PimWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, JoinHint.Right);

         relation = (EntityRelationBesoldungsklasseEntity.Relations.PimWertebereichSEntity...;
         relation.CustomFilter = new PredicateExpression(PimWertebereichSFields.Sprache == 1);
         bucket.Relations.Add(relation, "PimWertebereichS_1", JoinHint.Left);
            DataTable dynamicList = new DataTable();

Thanks for the response. But now I get an Error: (even before the SQL-Call. Therefore I can't see the generated SQL)

ORMRelationException
Relation at index 10 doesn't contain an entity already added to the FROM clause. Bad alias?

This occours in the line:

adapter.FetchTypedList(fields, dynamicList, bucket);

Stack-Trace:

at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.PreprocessRelations()
at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText(Int32& uniqueMarker)
at SD.LLBLGen.Pro.DQE.Oracle.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
at SD.LLBLGen.Pro.DQE.Oracle.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateQueryFromElements(IEntityFields2 fieldCollectionToFetch, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize, IFieldPersistenceInfo[]& persistenceInfo, IRetrievalQuery& selectQuery)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket)
at Avanti.ADAM.DataAccessLayer.LLBLGenProvider.PofDA.ListMitarbeiter()
at Avanti.ADAM.DataAccessLayer.LLBLGenProvider.DataProvider.ListMitarbeiter()
at Avanti.ADAM.DataAccessLayer.Logic.PofDalc.ListMitarbeiter() in D:\\Projekte\\PublSec\\ADAMSource\\Avanti.ADAM\\DataAccessLayer\\Avanti.ADAM.DataAccessLayer.Logic\\PofDalc.cs:line 45
at Avanti.ADAM.DataAccessLayer.Test.DataAccessLayerTester.Main() in D:\\Projekte\\PublSec\\ADAMSource\\Avanti.ADAM\\DataAccessLayer\\Avanti.ADAM.DataAccessLayer.Test\\DataAccessLayerTester.cs:line 50
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()"

The Alias matched:

         fields.DefineField(PomWertebereichSFields.WerteBez, 7, "BerufsTitelText", "PomWertebereichS_1"); 
            fields.DefineField(PimWertebereichSFields.WerteBez, 8, "BesoldungsklasseText", "PimWertebereichS_1");

Thanks and Regards Wolfgang

Walaa said earlier:

The relation collection should be a chain of relations, it's exactly like you are selecting from One table only and adding joins (inner or outer to other tables).

And the table you are selecting from is the first entity used in the first relation, which is the PersonEntity.

So after the first relation, now you have Person and Address in your list of entities, anything that comes later should be linked to any of them, or linked to any of the previously added relations. (like a chain).

And that was not the case when you added the second relation (relation at index 1). Here you are deifining a JOIN between PimWertebereichSEntity & DienstgradEntity, while PimWertebereichSEntity haven't been joined before.

I'll say it in another words.

For the following relation:

relation = (EntityRelation)SomeEntity.Relations.AnotherEntityUsingAnId; bucket.Relations.Add(relation);

To use the above relation, SomeEntity should have been defined in an earlier relation. (or a relation to SomeEntity should have been defined earlier).

Make sure you follow that rule and you won't get that exception again.

Another example, now that we have a relation to AnotherEntity defined, we can use it to define a relation to YetAnotherEntity as follows:

relation = (EntityRelation)AnotherEntity.Relations.YetAnotherEntityUsingAnyId; bucket.Relations.Add(relation);

Finally I managed to recreate the SQL with LLBL Gen. Thanks for your help.

The reasons: I missed the relation between besoldung and besoldungsklasse and I have to implement the custom filter like this:

relation.CustomFilter = new PredicateExpression(PimWertebereichSFields.Sprache.SetObjectAlias("PimWertebereichS_1") == 1);

(use of SetObjectAlias)

Can you please confirm that I have to use dynamic lists when I want to use Aliases? Or can I use typed Lists and add some Code there?

Because with the typed Lists I have a List<PersonQueryResult> as result. With the dynamic List I have a DataTable as result. Would it be possible to use dynamic Lists with a result of List<PersonQueryResult> ?

Thanks and Regards Wolfgang

Can you please confirm that I have to use dynamic lists when I want to use Aliases?

Well you would use Dynamic Lists to create lists in code, without the necessity of the designer. This sometimes can become handy if you just want to pull a small list of data from the database without having to re-generate the code again. You can use alisases along the LLBLGen framework (predicates, entityFields, dynamic lists, etc.).

Would it be possible to use dynamic Lists with a result of List<PersonQueryResult> ?

No. Dynamic Lists always return a DataTable. However you can a variety of options to fetch data: EntityCollections, TypedViews, TypedLists, DynamicLists, Projections... So read LLBLGenPro Help - Using generated code - Using TypedViews, TypedLists and DynamicLists section for more info.