Hello, I've the following oracle query to implement SELECT RECH_TOEST_CD FROM TVN_WRK_RECH_TOEST W WHERE LID_PARTIJ_ID = p_lid_partij_id AND DEF_ID IS NOT NULL AND NVL(DT_GLDG_EINDE, SYSDATE + 1) > SYSDATE AND BEHANDELEN_IND = 1 AND EXISTS ( SELECT 1 FROM TVN_RECH_TOEST WHERE ID = W.DEF_ID AND BEHANDELEN_IND = 0 ); I tried with this code but it doesn't work because I don't know how to add the W alias to the main table. The NVL stuff is still a problem for me. I'm using version 2.5 and Oracle 10g ODP.

EntityCollection<TvnWrkRechToestEntity> entities = new EntityCollection<TvnWrkRechToestEntity>();

IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(TvnWrkRechToestFields.LidPartijId == partijId);
filter.PredicateExpression.Add(TvnWrkRechToestFields.DefId != DBNull.Value);
filter.PredicateExpression.Add(TvnWrkRechToestFields.BehandelenInd == 1);
FieldCompareSetPredicate pred = new FieldCompareSetPredicate
                    (null, null, TvnWrkRechToestFields.Id, null, SetOperator.Exist, (TvnWrkRechToestFields.BehandelenInd == 0) & TvnWrkRechToestFields.Id == TvnWrkRechToestFields.DefId.SetObjectAlias("W"), false);
filter.PredicateExpression.Add(pred);

Any help is welcom. Thanks

Could you please post the generated sql and the exception (if any) you got?

SELECT "TVN_WRK_RECH_TOEST"."ID" AS "Id", "TVN_WRK_RECH_TOEST"."DT_VANAF" AS "DtVanaf", "TVN_WRK_RECH_TOEST"."DT_TOT" AS "DtTot", "TVN_WRK_RECH_TOEST"."DT_GLDG_BEGIN" AS "DtGldgBegin", "TVN_WRK_RECH_TOEST"."DT_GLDG_EINDE" AS "DtGldgEinde", "TVN_WRK_RECH_TOEST"."RECH_TOEST_CD" AS "RechToestCd", "TVN_WRK_RECH_TOEST"."OPM" AS "Opm", "TVN_WRK_RECH_TOEST"."OPM_SAMENST" AS "OpmSamenst", "TVN_WRK_RECH_TOEST"."DEF_ID" AS "DefId", "TVN_WRK_RECH_TOEST"."LID_PARTIJ_ID" AS "LidPartijId", "TVN_WRK_RECH_TOEST"."USID_WIJZ" AS "UsidWijz", "TVN_WRK_RECH_TOEST"."DT_WIJZ" AS "DtWijz", "TVN_WRK_RECH_TOEST"."BEHANDELEN_IND" AS "BehandelenInd", "TVN_WRK_RECH_TOEST"."RSVZ_IND" AS "RsvzInd", "TVN_WRK_RECH_TOEST"."NVT_IND" AS "NvtInd", "TVN_WRK_RECH_TOEST"."DW_IND" AS "DwInd" FROM "TVN_WRK_RECH_TOEST" WHERE ( ( "TVN_WRK_RECH_TOEST"."LID_PARTIJ_ID" = :LidPartijId1 AND "TVN_WRK_RECH_TOEST"."DEF_ID" IS NOT NULL AND "TVN_WRK_RECH_TOEST"."BEHANDELEN_IND" = :BehandelenInd2 AND EXISTS (SELECT "TVN_WRK_RECH_TOEST"."ID" AS "Id" FROM "TVN_WRK_RECH_TOEST" WHERE ( "TVN_WRK_RECH_TOEST"."BEHANDELEN_IND" = :BehandelenInd3 AND "TVN_WRK_RECH_TOEST"."ID" = "[b]W[/b]"."DEF_ID"))))

Thanks for replying. As you can see the from clause should be like this FROM "TVN_WRK_RECH_TOEST" W The exception is the following ORA-00904: "W"."DEF_ID": invalid identifier.

Don't add the alias to the main table add it to the one in the sub query:

EXISTS (
                SELECT 1
                FROM    TVN_RECH_TOEST W
                WHERE W.ID = DEF_ID
                AND  BEHANDELEN_IND = 0
                 );

I changed the predicat like this but it fails because I don't know how to add the alias on from clause. FROM TVN_RECH_TOEST W => ??

FieldCompareSetPredicate pred = new FieldCompareSetPredicate(null, null, TvnWrkRechToestFields.Id, null, SetOperator.Exist, (TvnWrkRechToestFields.BehandelenInd == 0) & TvnWrkRechToestFields.Id.SetObjectAlias("W") == TvnWrkRechToestFields.DefId, "W");

This generates the following statement : AND EXISTS (SELECT "TVN_WRK_RECH_TOEST"."ID" AS "Id" FROM "TVN_WRK_RECH_TOEST" WHERE ( "TVN_WRK_RECH_TOEST"."BEHANDELE N_IND" = :BehandelenInd3 AND "W"."ID" = "TVN_WRK_RECH_TOEST"."DEF_ID"))))

FieldCompareSetPredicate pred = new FieldCompareSetPredicate(null, null, TvnWrkRechToestFields.Id.SetObjectAlias("W"), null, SetOperator.Exist, ...);

Thanks