dear all,

i try to build the following sql statement with llblgen code:

        ResultsetFields fields = new ResultsetFields((int)KamfallFieldIndex.AmountOfFields + 4);
        KamfallEntity fall = new KamfallEntity();
        for (int i = 0; i < (int)KamfallFieldIndex.AmountOfFields; i++)
        {
            fields.DefineField(fall.Fields[i], i);
        }

        //Strasse
        fields.DefineField(KamsuchgebietFields.OrtBez, (int)KamfallFieldIndex.AmountOfFields, "Strasse");
        //Gemeinde
        fields.DefineField(KamsuchgebietFields.OrtBez, (int)KamfallFieldIndex.AmountOfFields + 1, "Gemeinde", "TGemeinde");
        //Bezirk
        fields.DefineField(KamsuchgebietFields.OrtBez, (int)KamfallFieldIndex.AmountOfFields + 2, "Bezirk", "TBezirk");
        //Region
        fields.DefineField(KamsuchgebietFields.OrtBez, (int)KamfallFieldIndex.AmountOfFields + 3, "Region", "TRegion");

        // Must activate this predicateExpression
        PredicateExpression predicateExpression = new PredicateExpression(
                KamfallFields.DatenstatusId == Codes.GetValueId(CodeGroup.DATENSTATUS, CodeValue.IMPORT) |
                KamfallFields.DatenstatusId == Codes.GetValueId(CodeGroup.DATENSTATUS, CodeValue.DELETE_CANDIDATE) |
                KamfallFields.DatenstatusId == Codes.GetValueId(CodeGroup.DATENSTATUS, CodeValue.MUTATION_MERGE) |
                KamfallFields.DatenstatusId == Codes.GetValueId(CodeGroup.DATENSTATUS, CodeValue.NEW) |
                KamfallFields.DatenstatusId == Codes.GetValueId(CodeGroup.DATENSTATUS, CodeValue.SYSTEM_MERGE));

        predicateExpression.Add(KamfallFields.BenutzergruppeId == Program.BENUTZER_GRUPPE_ID);

        //the bucket to keep filter and relations
        RelationPredicateBucket bucket = new RelationPredicateBucket();
        //a relation to perform additional filter within a relation
        EntityRelation relation;

        //Fall -> Fall_Adresse
        bucket.Relations.Add(KamfallEntity.Relations.KamfallAdresseEntityUsingFallId, JoinHint.Left);

        //Fall_Adress -> Adresse
        bucket.Relations.Add(KamfallAdresseEntity.Relations.KamadresseEntityUsingAdresseId, JoinHint.Left);

        //Strasse:
        //Adresse -> Suchgebiet
        bucket.Relations.Add(KamadresseEntity.Relations.KamsuchgebietEntityUsingAdresseId, JoinHint.Left);
        //Suchgebiet -> OrtsTyp
        //relation = (EntityRelation)bucket.Relations.Add(KamsuchgebietEntity.Relations.OrmOrtstypEntityUsingOrtstypId, "TypStrasse", JoinHint.Right);
        relation = (EntityRelation)bucket.Relations.Add(KamsuchgebietEntity.Relations.OrmOrtstypEntityUsingOrtstypId, "TypStrasse", JoinHint.Right);
        //and a additional filter set to STRASSE
        relation.CustomFilter = new PredicateExpression(OrmOrtstypFields.WerteCode.SetObjectAlias("TypStrasse") == "ORTS");

        //Gemeinde:
        //Adresse -> Suchgebiet
        bucket.Relations.Add(KamadresseEntity.Relations.KamsuchgebietEntityUsingAdresseId, "TGemeinde", JoinHint.Left);
        //Suchgebiet -> OrtsTyp
        relation = (EntityRelation)bucket.Relations.Add(KamsuchgebietEntity.Relations.OrmOrtstypEntityUsingOrtstypId, "TypGemeinde", JoinHint.Right);
        //and the additional filter set to Gemeinde
        relation.CustomFilter = new PredicateExpression(OrmOrtstypFields.WerteCode.SetObjectAlias("TypGemeinde") == "GEM");

        //Bezirk:
        //Adresse -> Suchgebiet
        bucket.Relations.Add(KamadresseEntity.Relations.KamsuchgebietEntityUsingAdresseId, "TBezirk", JoinHint.Left);
        //Suchgebiet -> OrtsTyp
        relation = (EntityRelation)bucket.Relations.Add(KamsuchgebietEntity.Relations.OrmOrtstypEntityUsingOrtstypId, "TypBezirk", JoinHint.Right);
        //and the additional filter set to BEZIRK
        relation.CustomFilter = new PredicateExpression(OrmOrtstypFields.WerteCode.SetObjectAlias("TypBezirk") == "BEZ");

        //Region:
        //Adresse -> Suchgebiet
        bucket.Relations.Add(KamadresseEntity.Relations.KamsuchgebietEntityUsingAdresseId, "TRegion", JoinHint.Left);
        //Suchgebiet -> OrtsTyp
        relation = (EntityRelation)bucket.Relations.Add(KamsuchgebietEntity.Relations.OrmOrtstypEntityUsingOrtstypId, "Region", JoinHint.Right);
        //and the additional filter set to REGION
        relation.CustomFilter = new PredicateExpression(OrmOrtstypFields.WerteCode.SetObjectAlias("Region") == "REG");

        //Add the where clause defined above
        bucket.PredicateExpression.Add(predicateExpression);

        return DataAccess.Generic.SelectData(bucket, fields, maxNumberOfItemsToReturn);

an i get no data returned. the sql statement i would like to build and i would expect to get is:

SELECT * FROM ( SELECT DISTINCT TAVKAMFALL.OID AS Oid, TAVKAMSUCHGEBIET.ORT_BEZ AS Strasse , LPA_T2.ORT_BEZ AS Gemeinde, LPA_T4.ORT_BEZ AS Bezirk, LPA_T6.ORT_BEZ AS Region FROM TAVKAMFALL
LEFT JOIN TAVKAMFALL_ADRESSE ON TAVKAMFALL.OID=TAVKAMFALL_ADRESSE.FALL_ID LEFT JOIN TAVKAMADRESSE ON TAVKAMADRESSE.OID=TAVKAMFALL_ADRESSE.ADRESSE_ID LEFT JOIN TAVKAMSUCHGEBIET ON TAVKAMADRESSE.OID=TAVKAMSUCHGEBIET.ADRESSE_ID RIGHT JOIN AVREP.TAVORMORTSTYP LPA_O1 ON LPA_O1.OID=TAVKAMSUCHGEBIET.ORTSTYP_ID AND LPA_O1.WERTE_CODE = 'ORTS' LEFT JOIN TAVKAMSUCHGEBIET LPA_T2 ON TAVKAMADRESSE.OID=LPA_T2.ADRESSE_ID RIGHT JOIN AVREP.TAVORMORTSTYP LPA_O3 ON LPA_O3.OID=LPA_T2.ORTSTYP_ID AND LPA_O3.WERTE_CODE = 'GEM' LEFT JOIN TAVKAMSUCHGEBIET LPA_T4 ON TAVKAMADRESSE.OID=LPA_T4.ADRESSE_ID RIGHT JOIN AVREP.TAVORMORTSTYP LPA_O5 ON LPA_O5.OID=LPA_T4.ORTSTYP_ID AND LPA_O5.WERTE_CODE = 'BEZ' LEFT JOIN TAVKAMSUCHGEBIET LPA_T6 ON TAVKAMADRESSE.OID=LPA_T6.ADRESSE_ID RIGHT JOIN AVREP.TAVORMORTSTYP LPA_O7 ON LPA_O7.OID=LPA_T6.ORTSTYP_ID AND LPA_O7.WERTE_CODE = 'REG' WHERE
(TAVKAMFALL.DATENSTATUS_ID IS NULL OR TAVKAMFALL.DATENSTATUS_ID = '86864ABA1B9B4CAC93051F2FFD1549A7' OR TAVKAMFALL.DATENSTATUS_ID = '624372D766094AA69F1B196ED4B82075' OR TAVKAMFALL.DATENSTATUS_ID = '2C9DE1749053493F8C90666AE63C6EE3' OR TAVKAMFALL.DATENSTATUS_ID = '87B4B887D1DB47FF80039BF1DB2610D2') AND TAVKAMFALL.BENUTZERGRUPPE_ID = 'KAS' ) WHERE rownum <= 100

And what i really get is:

SELECT * FROM ( SELECT DISTINCT TAVKAMFALL.OID AS Oid, TAVKAMSUCHGEBIET.ORT_BEZ AS Strasse , LPA_T2.ORT_BEZ AS Gemeinde, LPA_T4.ORT_BEZ AS Bezirk, LPA_T6.ORT_BEZ AS Region FROM TAVKAMFALL
LEFT JOIN TAVKAMFALL_ADRESSE ON TAVKAMFALL.OID=TAVKAMFALL_ADRESSE.FALL_ID LEFT JOIN TAVKAMADRESSE ON TAVKAMADRESSE.OID=TAVKAMFALL_ADRESSE.ADRESSE_ID LEFT JOIN TAVKAMSUCHGEBIET ON TAVKAMADRESSE.OID=TAVKAMSUCHGEBIET.ADRESSE_ID RIGHT JOIN AVREP.TAVORMORTSTYP LPA_O1 ON LPA_O1.OID=TAVKAMSUCHGEBIET.ORTSTYP_ID AND LPA_O1.WERTE_CODE = 'ORTS' LEFT JOIN TAVKAMSUCHGEBIET LPA_T2 ON TAVKAMADRESSE.OID=LPA_T2.ADRESSE_ID RIGHT JOIN AVREP.TAVORMORTSTYP LPA_O3 ON LPA_O3.OID=TAVKAMSUCHGEBIET.ORTSTYP_ID AND LPA_O3.WERTE_CODE = 'GEM' LEFT JOIN TAVKAMSUCHGEBIET LPA_T4 ON TAVKAMADRESSE.OID=LPA_T4.ADRESSE_ID RIGHT JOIN AVREP.TAVORMORTSTYP LPA_O5 ON LPA_O5.OID=TAVKAMSUCHGEBIET.ORTSTYP_ID AND LPA_O5.WERTE_CODE = 'BEZ' LEFT JOIN TAVKAMSUCHGEBIET LPA_T6 ON TAVKAMADRESSE.OID=LPA_T6.ADRESSE_ID RIGHT JOIN AVREP.TAVORMORTSTYP LPA_O7 ON LPA_O7.OID=TAVKAMSUCHGEBIET.ORTSTYP_ID AND LPA_O7.WERTE_CODE = 'REG' WHERE
(TAVKAMFALL.DATENSTATUS_ID IS NULL OR TAVKAMFALL.DATENSTATUS_ID = '86864ABA1B9B4CAC93051F2FFD1549A7' OR TAVKAMFALL.DATENSTATUS_ID = '624372D766094AA69F1B196ED4B82075' OR TAVKAMFALL.DATENSTATUS_ID = '2C9DE1749053493F8C90666AE63C6EE3' OR TAVKAMFALL.DATENSTATUS_ID = '87B4B887D1DB47FF80039BF1DB2610D2') AND TAVKAMFALL.BENUTZERGRUPPE_ID = 'KAS' ) WHERE rownum <= 100

I marked the differences as BOLD. The problem is, that the second sql statement, built by llblgen, is coorect in terms of oracle but it does not return any data while the first sql statemnt does.

And i do not see, what i have made wrong. does anyone of you can see it?

thanx you very much in advance,

chris

It would have been easier had you provided a simpler Repro code.

Anyway I think the reason is that you are using Aliases defining an Entity in a relation then you don't use the same alias again when you use the previously defined entity in another relation.

So here is what you should do.

1- Avoid using Aliases at all, unless you are Joining to the same entity more then once, other than that you shouldn't use aliases.

2- If you have to use an alias in one relation, make sure to supply the same alias for the same entity used in another relation, and for that you should use the other Overload available for the Relations.Add() method, which accepts 2 strings, one for the start entity alias, and the other is for the End entity alias.

public virtual IEntityRelation Add( 
   IEntityRelation relationToAdd,
   string aliasRelationStartEntity,
   string aliasRelationEndEntity,
   JoinHint hint
)

thats it, sometimes i can't see the forest for the trees. thank you very much.