I am trying to get the following sql query work in llblgen.

SELECT DISTINCT c.ENTITY_NA, a.EMPR_ID, a.YEAR, a.QUARTER, a.SUBMIT_DT, b.FIRST_NA, b.LAST_NA, b.MDDL_INIT_NA, b.CALC_AM, e.OWED_AM, d.PAID_AM FROM ( ( ( ( EMPR_HDR a INNER JOIN UNIT_DTL b ON a.QUARTER = b.QUARTER AND a.YEAR = b.YEAR AND a.EMPR_ID = b.EMPR_ID AND a.SEQ_NU = b.SEQ_NU) INNER JOIN EMPR c ON c.EMPR_ID = a.EMPR_ID) INNER JOIN AMOUNT_DTL d ON d.Id = b.Id AND d.Year = b.Year AND d.EMPR_ID = b.EMPR_ID) LEFT JOIN ACCT_DUE e ON e.EMPR_ID = a.EMPR_ID AND e.Quarter = a.Quarter and e.t_seq_nu = d.t_Seq_nu and e.year = d.year) WHERE ( ( a.EMPR_ID = '1234' AND a.quarter = '3' AND a.year = '2011' AND e.TYPE_CD = 'EXDE' AND a.STATUS_CD = 'SUBM' AND c.STATUS_CD = 'ACTV'))

Here is the llblgen query i have written so far.

int counter = 0; ResultsetFields fields = new ResultsetFields(11); fields.DefineField(EmprFields.EntityNa, counter++); fields.DefineField(EmprHdrFields.EmprId, counter++); fields.DefineField(EmprHdrFields.Year, counter++); fields.DefineField(EmprHdrFields.Quarter, counter++); fields.DefineField(EmprHdrFields.SubmitDt, counter++); fields.DefineField(UnitDtlFields.FirstNa, counter++); fields.DefineField(UnitDtlFields.LastNa, counter++); fields.DefineField(UnitDtlFields.MddlInitNa, counter++); fields.DefineField(UnitDtlFields.CalcAm, counter++); fields.DefineField(AcctDueFields.OwedAm, counter++); fields.DefineField(AmountDtlFields.PaidAm, counter++);

        RelationCollection relations = new RelationCollection();

        IEntityRelation entityRelationEmprHdrAndUnitDtl = new EntityRelation();
        entityRelationEmprHdrAndUnitDtl.AddEntityFieldPair(EmprHdrFields.Quater, UnitDtlFields.Quater);
        entityRelationEmprHdrAndUnitDtl.AddEntityFieldPair(EmprHdrFields.Year, UnitDtlFields.Year);
        entityRelationEmprHdrAndUnitDtl.AddEntityFieldPair(EmprHdrFields.EmprId, UnitDtlFields.EmprId);
        entityRelationEmprHdrAndUnitDtl.AddEntityFieldPair(EmprHdrFields.SeqNu, UnitDtlFields.SeqNu);
        relations.Add(entityRelationEmprHdrAndUnitDtl, JoinHint.Inner);


        IEntityRelation entityRelationUIEmployer = new EntityRelation();
        entityRelationUIEmployer.AddEntityFieldPair(EmprFields.EmprId, EmprHdrFields.EmprId);
        relations.Add(entityRelationUIEmployer, JoinHint.Inner);

        IEntityRelation entityRelationUITaxableAmSum = new EntityRelation();
        entityRelationUITaxableAmSum.AddEntityFieldPair(AmountDtlFields.Id, UnitDtlFields.Id);
        entityRelationUITaxableAmSum.AddEntityFieldPair(AmountDtlFields.Year, EmprHdrFields.Year);
        entityRelationUITaxableAmSum.AddEntityFieldPair(AmountDtlFields.EmprId, EmprHdrFields.EmprId);
        relations.Add(entityRelationUITaxableAmSum, JoinHint.Inner);


        IEntityRelation entityRelationEmprAcctDue = new EntityRelation();

        entityRelationEmprAcctDue.AddEntityFieldPair(AcctDueFields.TSeqNu, AmountDtlFields.TSeqNu);
        entityRelationEmprAcctDue.AddEntityFieldPair(AcctDueFields.EmprId, EmprHdrFields.EmprId);
        entityRelationEmprAcctDue.AddEntityFieldPair(**AcctDueFields.Quarter**, EmprHdrFields.Quarter);
        entityRelationEmprAcctDue.AddEntityFieldPair(AcctDueFields.Year, EmprHdrFields.Year);
        relations.Add(entityRelationEmprAcctDue, JoinHint.Left);


        IPredicateExpression pred = new PredicateExpression();
        pred.Add(EmprHdrFields.EmprId == 69596);
        pred.Add(EmprHdrFields.RptQtr == 2011);
        pred.Add(EmprHdrFields.RptYr == 3);
        pred.Add(AcctDueFields.TypeCd == "EXWG");
        pred.Add(EmprHdrFields.StatusCd == "SUBM");
        pred.Add(UiEmployerFields.StatusCd == "ACTV");

When i execute the query i get the following error.

An exception was caught during the execution of a retrieval query: ORA-00904: "UFACTS"."AMOUNT_DTL"."RPT_QTR": invalid identifier.

Below is the generated sql query for the fourth relation.

LEFT JOIN
       "FACTS"."ACCT_DUE"
    ON "FACTS"."ACCT_DUE"."TAXABLE_SEQ_NU" =
          "FACTS"."AMOUNT_DTL"."TAXABLE_SEQ_NU"
       AND "FACTS"."ACCT_DUE"."EMPR_ACCT_ID" =
              "FACTS"."AMOUNT_DTL"."EMPR_ACCT_ID"
       AND **"FACTS"."ACCT_DUE"."RPT_QTR" =
              "FACTS"."AMOUNT_DTL"."RPT_QTR"**         AND "FACTS"."UI_EMPR_ACCT_DUE"."RPT_YR" =
              "FACTS"."AMOUNT_DTL"."RPT_YR")

In the entity relation i am specifiying the join on

entityRelationEmprAcctDue.AddEntityFieldPair(AcctDueFields.Quarter, EmprHdrFields.Quarter);

but the query generated has AMOUNT_DTL.RPT_QTR.

Am i doing something wrong here ?

I am using the 2.6.08.0619 build. I download the latest assembly 2.6.11.0427 and got the same error.

In this code you try to cram two relations into one:

IEntityRelation entityRelationEmprAcctDue = new EntityRelation();
entityRelationEmprAcctDue.AddEntityFieldPair(AcctDueFields.TSeqNu, AmountDtlFields.TSeqNu);
entityRelationEmprAcctDue.AddEntityFieldPair(AcctDueFields.EmprId, EmprHdrFields.EmprId);
entityRelationEmprAcctDue.AddEntityFieldPair(AcctDueFields.Quarter, EmprHdrFields.Quarter);
entityRelationEmprAcctDue.AddEntityFieldPair(AcctDueFields.Year, EmprHdrFields.Year);
relations.Add(entityRelationEmprAcctDue, JoinHint.Left);

Try to split them and create two relations: AcctDue-EmprHdr and AcctDue-AmountDtl

As per your suggestion i changed the last relationship to in to two relations.

IEntityRelation entityRelationEmprAcctDue = new EntityRelation(); entityRelationEmprAcctDue.AddEntityFieldPair(AcctDueFields.EmprId, EmprHdrFields.EmprId); entityRelationEmprAcctDue.AddEntityFieldPair(AcctDueFields.Quarter, EmprHdrFields.Quarter); entityRelationEmprAcctDue.AddEntityFieldPair(AcctDueFields.Year, EmprHdrFields.Year); relations.Add(entityRelationEmprAcctDue, JoinHint.Left);

        IEntityRelation entityRelationEmprAcctAmDtl = new EntityRelation();
        entityRelationEmprAcctAmDtl.AddEntityFieldPair(AcctDueFields.TSeqNu, AmountDtlFields.TSeqNu);
        relations.Add(entityRelationEmprAcctAmDtl, JoinHint.Left);

But when i execute the code the last relationship is ignored and its not part of the generated sql.

when i execute the code the last relationship is ignored and its not part of the generated sql.

Strange. Could you please post the FROM part of the generated SQL.

if i add the entityRelationEmprAcctAmDtl relation after i add the entityRelationEmprAcctDue then entityRelationEmprAcctAmDtl is ignored and it happens the same if the change the order of relations. Always the last relation that i add to the collection gets ignored.

SELECT DISTINCT "UFACTS"."EMPR"."ENTITY_NA" AS "EntityNa", "FACTS"."EMPR_HDR"."EMPR_ID" AS "EmprId", "FACTS"."EMPR_HDR"."RPT_YR" AS :"Year", "FACTS"."EMPR_HDR"."QUARTER" AS "Quarter", "FACTS"."EMPR_HDR"."FILING_MTHD" AS "FilingMthd", "FACTS"."EMPR_HDR"."FILING_DT" AS "FilingDt", "FACTS"."EMPR_HDR"."SUBMIT_DT" AS "SubmitDt", "FACTS"."UNIT_DTL"."FIRST_NA" AS "FirstNa", "FACTS"."UNIT_DTL"."LAST_NA" AS "LastNa", "FACTS"."UNIT_DTL"."MDDL_INIT_NA" AS "MddlInitNa", "FACTS"."UNIT_DTL"."CALC_AM" AS "CalcAm", "FACTS"."ACCT_DUE"."OWED_AM" AS "OwedAm", "FACTS"."AMOUNT_DTL"."PAID_AM" AS "PaidAm" FROM ( ( ( ( "FACTS"."EMPR_HDR" INNER JOIN "FACTS"."UNIT_DTL" ON "FACTS"."EMPR_HDR"."QUARTER" = "FACTS"."UNIT_DTL"."QUARTER" AND "FACTS"."EMPR_HDR"."YEAR" = "FACTS"."UNIT_DTL"."YEAR" AND "FACTS"."EMPR_HDR"."EMPR_ID" = "FACTS"."UNIT_DTL"."EMPR_ID" AND "FACTS"."EMPR_HDR"."SEQ_NU" = "FACTS"."UNIT_DTL"."SEQ_NU") INNER JOIN "FACTS"."EMPR" ON "FACTS"."EMPR"."EMPR_ID" = "FACTS"."EMPR_HDR"."EMPR_ID") INNER JOIN "FACTS"."AMOUNT_DTL" ON "FACTS"."AMOUNT_DTL"."ID" = "FACTS"."UNIT_DTL"."ID" AND "FACTS"."AMOUNT_DTL"."YEAR" = "FACTS"."UNIT_DTL"."YEAR" AND "FACTS"."AMOUNT_DTL"."EMPR_ID" = "FACTS"."UNIT_DTL"."EMPR_ID") LEFT JOIN "FACTS"."ACCT_DUE" ON "FACTS"."ACCT_DUE"."EMPR_ID" = "FACTS"."EMPR_HDR"."EMPR_ID" AND "FACTS"."ACCT_DUE"."QUARTER" = "FACTS"."EMPR_HDR"."QUARTER" AND "FACTS"."ACCT_DUE"."YEAR" = "FACTS"."EMPR_HDR"."YEAR") WHERE ( ( "FACTS"."EMPR_HDR"."QUARTER" = '3' AND "FACTS"."EMPR_HDR"."YEAR" = '2011' AND "FACTS"."ACCT_DUE"."TYPE_CD" = 'EXWG' AND "FACTS"."EMPR_HDR"."STATUS_CD" = 'SUBM' AND "FACTS"."EMPR"."STATUS_CD" = 'ACTV'))

I found something strange in the joins. Let's take another look at the generated SQL (the one in your last post):

SELECT DISTINCT
    ...
    "FACTS"."ACCT_DUE"."OWED_AM" AS "OwedAm",
    "FACTS"."AMOUNT_DTL"."PAID_AM" AS "PaidAm"
    
FROM ( ( ( ( 
    "FACTS"."EMPR_HDR"

    INNER JOIN "FACTS"."UNIT_DTL"
        ON "FACTS"."EMPR_HDR"."QUARTER" = "FACTS"."UNIT_DTL"."QUARTER"
        AND "FACTS"."EMPR_HDR"."YEAR" = "FACTS"."UNIT_DTL"."YEAR"
        AND "FACTS"."EMPR_HDR"."EMPR_ID" = "FACTS"."UNIT_DTL"."EMPR_ID"
        AND "FACTS"."EMPR_HDR"."SEQ_NU" = "FACTS"."UNIT_DTL"."SEQ_NU")

    INNER JOIN "FACTS"."EMPR"
        ON "FACTS"."EMPR"."EMPR_ID" = "FACTS"."EMPR_HDR"."EMPR_ID")

    INNER JOIN "FACTS"."AMOUNT_DTL"
        ON "FACTS"."AMOUNT_DTL"."ID" = "FACTS"."UNIT_DTL"."ID"
        AND "FACTS"."AMOUNT_DTL"."YEAR" = "FACTS"."UNIT_DTL"."YEAR"
        AND "FACTS"."AMOUNT_DTL"."EMPR_ID" = "FACTS"."UNIT_DTL"."EMPR_ID")

    LEFT JOIN "FACTS"."ACCT_DUE"
        ON "FACTS"."ACCT_DUE"."EMPR_ID" = "FACTS"."EMPR_HDR"."EMPR_ID"
        AND "FACTS"."ACCT_DUE"."QUARTER" = "FACTS"."EMPR_HDR"."QUARTER"
        AND "FACTS"."ACCT_DUE"."YEAR" = "FACTS"."EMPR_HDR"."YEAR")  
            
WHERE ...

Now assume that the last join was added, so the query would looks like:

SELECT DISTINCT
    ...
    "FACTS"."ACCT_DUE"."OWED_AM" AS "OwedAm",
    "FACTS"."AMOUNT_DTL"."PAID_AM" AS "PaidAm"
    
FROM ( ( ( ( 
    "FACTS"."EMPR_HDR"

    INNER JOIN "FACTS"."UNIT_DTL"
        ON "FACTS"."EMPR_HDR"."QUARTER" = "FACTS"."UNIT_DTL"."QUARTER"
        AND "FACTS"."EMPR_HDR"."YEAR" = "FACTS"."UNIT_DTL"."YEAR"
        AND "FACTS"."EMPR_HDR"."EMPR_ID" = "FACTS"."UNIT_DTL"."EMPR_ID"
        AND "FACTS"."EMPR_HDR"."SEQ_NU" = "FACTS"."UNIT_DTL"."SEQ_NU")

    INNER JOIN "FACTS"."EMPR"
        ON "FACTS"."EMPR"."EMPR_ID" = "FACTS"."EMPR_HDR"."EMPR_ID")

    INNER JOIN "FACTS"."AMOUNT_DTL"
        ON "FACTS"."AMOUNT_DTL"."ID" = "FACTS"."UNIT_DTL"."ID"
        AND "FACTS"."AMOUNT_DTL"."YEAR" = "FACTS"."UNIT_DTL"."YEAR"
        AND "FACTS"."AMOUNT_DTL"."EMPR_ID" = "FACTS"."UNIT_DTL"."EMPR_ID")

    LEFT JOIN "FACTS"."ACCT_DUE"
        ON "FACTS"."ACCT_DUE"."EMPR_ID" = "FACTS"."EMPR_HDR"."EMPR_ID"
        AND "FACTS"."ACCT_DUE"."QUARTER" = "FACTS"."EMPR_HDR"."QUARTER"
        AND "FACTS"."ACCT_DUE"."YEAR" = "FACTS"."EMPR_HDR"."YEAR")  
        
    LEFT JOIN "FACTS"."AMOUNT_DTL"
        ON "FACTS"."AMOUNT_DTL"."T_SEQ_NU" = "FACTS"."ACCT_DUE"."T_SEQ_NU"      

WHERE ...

As you can see, apparently the last join would be superfluous as it's a LEFT JOIN joined with a subset which is a result of another LEFT, and you already have an INNER join involving AMOUNT_DTL. I think that you will come to the same conclusion if you examine the execution plan of this query. That means -I guess- that that last join doesn't make any difference in the results of the query. LLBLGen has query optimization routines to emit the best possible query, so I think that last join is removed due to redundancy.