Outer join in the where clause

Posts   
 
    
PAZIMLI
User
Posts: 82
Joined: 01-May-2008
# Posted on: 17-Nov-2009 13:08:16   

v2.6, Oracle, Adapter, C#.Net 2008

SELECT COUNT(LR.SONRAKI_ISLEM_NO), COUNT(LE.IS_EMRI_NO), LP.IS_EMRI_NO, LP.SIRA_NO, LP.PARCA_NO FROM nis.LY_IS_EMRI_PARCA LP, nis.LY_IE_GERCEKLESEN_ISLEM LE, nis.LY_IE_PARCA_ISLEM_ROTA LR where LE.DURUMU(+) = 2 AND LP.IS_EMRI_NO = LE.IS_EMRI_NO(+) AND LP.SIRA_NO = LE.SIRA_NO(+) AND LP.PARCA_NO = LE.PARCA_NO(+) AND LP.IS_EMRI_NO = LR.IS_EMRI_NO AND LP.SIRA_NO = LR.SIRA_NO AND LP.PARCA_NO = LR.PARCA_NO GROUP BY LP.IS_EMRI_NO, LP.SIRA_NO, LP.PARCA_NO

how can i code the colored text in the llblgen.

MTrinder
User
Posts: 1461
Joined: 08-Oct-2008
# Posted on: 17-Nov-2009 21:46:01   

This does not need to be an outer join as it is not joining two tables. It is simply a WHERE clause on the SQL statement and can be created using the PredicateExpression property of the RelationPredicateBucket



RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Add(LY_IE_GERCEKLESEN_ISLEMFields.DURUMU==2);


Matt

PAZIMLI
User
Posts: 82
Joined: 01-May-2008
# Posted on: 18-Nov-2009 07:47:16   

if it is a normal join, why the data is different when i write LE.DURUMU = 2 instead of LE.DURUMU(+) = 2, There may not be data at all time for related records in the LY_IE_GERCEKLESEN_ISLEM table.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 18-Nov-2009 08:50:08   

To specify an outer join, use overloads of the Relations.Add() method to pass a JoinHint.

PAZIMLI
User
Posts: 82
Joined: 01-May-2008
# Posted on: 18-Nov-2009 16:33:34   

if i use the overloads of add Relation with JoinHint like;

PredicateExpression filter = new PredicateExpression(); filter.Add(LyIeGerceklesenIslemFields.Durumu == (decimal)2); RelationPredicateBucket rpb = new RelationPredicateBucket(); rpb.Relations.Add(LyIsEmriParcaEntity.Relations.LyIeGerceklesenIslemEntityUsingIsEmriNoSiraNoParcaNo, JoinHint.Left);

it only add the following LP.IS_EMRI_NO = LE.IS_EMRI_NO(+) AND LP.SIRA_NO = LE.SIRA_NO(+) AND LP.PARCA_NO = LE.PARCA_NO(+) to the query but it does not add "LE.DURUMU(+) = 2"to the query it adds "LE.DURUMU = 2", and the data that it retrieves change.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 18-Nov-2009 16:45:59   

Maybe you need to add this filter as a custom flter on the presented relation, rather than an independant filter.

Otherwise you might try expanding it such as (pseudo code):

WHERE LE.DURUMU = 2 OR LE.DURUMU = null
PAZIMLI
User
Posts: 82
Joined: 01-May-2008
# Posted on: 18-Nov-2009 17:22:31   

Did you mean

RelationPredicateBucket rpb = new RelationPredicateBucket(filter); as the custom filter? i only write it wrong in the previous message.

and also if i try LE.DURUMU = 2 and LE.DURUMU == NULL instead of LE.DURUMU(+) = 2; it does not retrieve the same data.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 19-Nov-2009 03:47:23   

Please try this code (CustomFilter and CustomFilterReplaceOnClause):

IPredicateExpression customFilter = new PredicateExpression(LyIeGerceklesenIslemFields.Durumu == (decimal)2);

IEntityRelation relationToAdd = LyIsEmriParcaEntity.Relations.LyIeGerceklesenIslemEntityUsingIsEmriNoSiraNoParcaNo;
relationToAdd.CustomFilter = customFilter;
relationToAdd.CustomFilterReplacesOnClause = true;

rpb.Relations.Add(relationToAdd, JoinHint.Left)
David Elizondo | LLBLGen Support Team
PAZIMLI
User
Posts: 82
Joined: 01-May-2008
# Posted on: 20-Nov-2009 08:52:24   

Altough i add the

IEntityRelation relationToAdd = LyIsEmriParcaEntity.Relations.LyIeGerceklesenIslemEntityUsingIsEmriNoSiraNoParcaNo; IPredicateExpression customFilter = new PredicateExpression(LyIeGerceklesenIslemFields.Durumu != (decimal)2); relationToAdd.CustomFilter = customFilter; relationToAdd.CustomFilterReplacesOnClause = true; rpb.Relations.Add(relationToAdd, JoinHint.Left); to the query it could not generate the exact result that i want, it generates a query like this;

WHERE
"NIS"."LY_IS_EMRI_PARCA"."IS_EMRI_NO"="NIS"."LY_IS_EMRI_PARCA_OZELLIK"."IS_EMRI_NO"(+) AND "NIS"."LY_IS_EMRI_PARCA"."SIRA_NO"="NIS"."LY_IS_EMRI_PARCA_OZELLIK"."SIRA_NO"(+) AND "NIS"."LY_IS_EMRI_PARCA"."PARCA_NO"="NIS"."LY_IS_EMRI_PARCA_OZELLIK"."PARCA_NO"(+) AND "NIS"."ITEM"."ITEM_KODU"(+)="NIS"."LY_IS_EMRI_PARCA"."ITEM_KODU" AND "NIS"."ITEM"."ITEM_KODU"="NIS"."ITEM_OZELLIK"."ITEM_KODU"(+) AND "NIS"."ITEM"."ITEM_KODU"="NIS"."SERI_NO"."ITEM_KODU"(+) AND "NIS"."LY_IS_EMRI_PARCA"."IS_EMRI_NO"="NIS"."LY_IE_PARCA_ISLEM_ROTA"."IS_EMRI_NO" AND "NIS"."LY_IS_EMRI_PARCA"."SIRA_NO"="NIS"."LY_IE_PARCA_ISLEM_ROTA"."SIRA_NO" AND "NIS"."LY_IS_EMRI_PARCA"."PARCA_NO"="NIS"."LY_IE_PARCA_ISLEM_ROTA"."PARCA_NO" AND "NIS"."LY_ACILAN_IS_EMRI"."IS_EMRI_NO"="NIS"."LY_IS_EMRI_PARCA"."IS_EMRI_NO" AND "NIS"."LY_ACILAN_IS_EMRI"."SIRA_NO"="NIS"."LY_IS_EMRI_PARCA"."SIRA_NO" AND ( ( "NIS"."LY_IE_GERCEKLESEN_ISLEM"."DURUMU" <> 2)) AND "NIS"."IS_MERKEZI"."DEPO_KODU"(+)="NIS"."SERI_NO"."DEPO_KODU" AND ( ( "NIS"."LY_IS_EMRI_PARCA"."IS_EMRI_NO" = 120 AND "NIS"."LY_IS_EMRI_PARCA"."SIRA_NO" = 1 AND ( "NIS"."SERI_NO"."LOT_MIKTAR" > 0 OR "NIS"."SERI_NO"."LOT_MIKTAR" IS NULL) AND "NIS"."LY_ACILAN_IS_EMRI"."IS_EMRI_DURUMU" IN (0, 1, 2) AND "NIS"."LY_IE_PARCA_ISLEM_ROTA"."IS_MERKEZI_FLAG" <> 0 AND "NIS"."LY_IS_EMRI_PARCA"."URETILDI_FLAG" = 'H')) GROUP BY "NIS"."LY_IS_EMRI_PARCA"."ITEM_KODU", "NIS"."LY_IS_EMRI_PARCA"."MIKTAR", "NIS"."LY_IS_EMRI_PARCA"."IS_EMRI_NO", "NIS"."LY_IS_EMRI_PARCA"."SIRA_NO", "NIS"."LY_IS_EMRI_PARCA"."PARCA_NO", "NIS"."LY_IS_EMRI_PARCA"."DEPO_KODU", "NIS"."LY_IS_EMRI_PARCA"."STOK_ALAN_KODU", "NIS"."SERI_NO"."DEPO_KODU", "NIS"."SERI_NO"."STOK_ALAN_KODU", "NIS"."LY_IE_PARCA_ISLEM_ROTA"."ISLETME_ORG_FIRMA", "NIS"."LY_IE_PARCA_ISLEM_ROTA"."ISLETME_KODU", "NIS"."LY_IE_PARCA_ISLEM_ROTA"."IS_MERKEZI_KODU".

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 20-Nov-2009 10:22:57   

Why are you using not equal?