- Home
- LLBLGen Pro
- Bugs & Issues
Can't get the SQL query working in llblgen
Joined: 29-Sep-2011
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.
Joined: 28-Nov-2005
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
Joined: 29-Sep-2011
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.
Joined: 29-Sep-2011
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'))
Joined: 28-Nov-2005
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.