Hi,

I am looking to execute a query similar to the following:

SELECT d11.image_number, d11.subject_descriptor
  FROM tbldescriptor d11, tbldescriptor d12
 WHERE   d11.subject_descriptor = 'Graduation Ceremonies'
       AND d12.subject_descriptor = 'Vice-Chancellors'
       AND d11.image_number = d12.image_number;

So the C# code I used is:

IRelationPredicateBucket bucket = new RelationPredicateBucket();
                             bucket.Relations.Add(TbldescriptorEntity.Relations.TblpixprojectEntityUsingImageNumber, "d11");
                bucket.Relations.Add(TbldescriptorEntity.Relations.TblpixprojectEntityUsingImageNumber, "d12");
            bucket.PredicateExpression.Add((TbldescriptorFields.SubjectDescriptor.SetObjectAlias("d11") == "Graduation Ceremonies") &
                     (TbldescriptorFields.SubjectDescriptor.SetObjectAlias("d12") == "Vice-Chancellors"));

                EntityCollection descriptors = new EntityCollection(new TbldescriptorEntityFactory());
                DataAccessAdapter adapter = new DataAccessAdapter();

                adapter.FetchEntityCollection(descriptors, bucket);

When I run above get the following error:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled by user code Message=An exception was caught during the execution of a retrieval query: ORA-00904: "TBL02"."SUBJECT_DESCRIPTOR": invalid identifier. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. Source=SD.LLBLGen.Pro.ORMSupportClasses.NET20 RuntimeBuild=06242008 RuntimeVersion=2.6.0.0 QueryExecuted= Query: SELECT DISTINCT "MONPIX"."TBLDESCRIPTOR"."IMAGE_NUMBER" AS "ImageNumber", "MONPIX"."TBLDESCRIPTOR"."SUBJECT_DESCRIPTOR" AS "SubjectDescriptor" FROM (( "MONPIX"."TBLPIXPROJECT" "LPA_T1" INNER JOIN "MONPIX"."TBLDESCRIPTOR" ON "LPA_T1"."IMAGE_NUMBER"="MONPIX"."TBLDESCRIPTOR"."IMAGE_NUMBER") INNER JOIN "MONPIX"."TBLPIXPROJECT" "LPA_T2" ON "LPA_T2"."IMAGE_NUMBER"="MONPIX"."TBLDESCRIPTOR"."IMAGE_NUMBER") WHERE ( ( ( "TBL01"."SUBJECT_DESCRIPTOR" = :SubjectDescriptor1 AND "TBL02"."SUBJECT_DESCRIPTOR" = :SubjectDescriptor2))) Parameter: :SubjectDescriptor1 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "Graduation Ceremonies". Parameter: :SubjectDescriptor2 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "Vice-Chancellors".

StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket) at Monpix41Public.SundryServices.Sundries.GetRecords(IDataAccessAdapter _daa, ImageInfoSearch iis, Int32 pageNumber, Int32& totalRecords) in D:\Documents and Settings\kkrishna\my documents\visual studio 2010\Projects\Monpix41Public\Monpix41Public\SundryServices\Sundries.cs:line 202 at Monpix41Public.Controllers.SearchController.ImageInfoSearchResults(ImageInfoSearch iis, Int32 page) in D:\Documents and Settings\kkrishna\my documents\visual studio 2010\Projects\Monpix41Public\Monpix41Public\Controllers\SearchController.cs:line 146 at lambda_method(Closure , ControllerBase , Object[] ) at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41() at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass81.<BeginSynchronous>b__7(IAsyncResult _) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.End() at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33() at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49() InnerException: Oracle.DataAccess.Client.OracleException Message=ORA-00904: "TBL02"."SUBJECT_DESCRIPTOR": invalid identifier Source=Oracle Data Provider for .NET ErrorCode=-2147467259 DataSource=MPIXDEV.WORLD Number=904 Procedure="" StackTrace: at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.DataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) InnerException:

The table (Tbldescriptor) is poorly designed, has no PK, no unique identity etc. Has just two columns: ImageNumber and SubjectDescriptor:

Sample data from the table:

60 Graduation Ceremonies 60 Vice-Chancellors 61 Graduation Ceremonies 61 Vice-Chancellors 62 Vice-Chancellors 63 Buildings - Medical 64 Libraries - Law 65 Buildings - Law 66 Education 67 Education 68 Administrators 69 Slavic Languages 70 Administrators 71 Buildings - Law

I am using LLBLGen Pro Final 2.6 Adapter, C#, Oracle 11g.

Thanks.

RuntimeBuild=06242008

Please use the latest release of v.2.6

I have installed the latest LLBLGen version 2.6, generated the code etc., but the problem still persists. The new stack trace is below. In my last stack trace, when you read please replace the TBL01 with d11 and TBL02 with d12. Sorry for posting a misleading stack trace.

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled by user code Message=An exception was caught during the execution of a retrieval query: ORA-00904: "d12"."SUBJECT_DESCRIPTOR": invalid identifier. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. Source=SD.LLBLGen.Pro.ORMSupportClasses.NET20 RuntimeBuild=11052011 RuntimeVersion=2.6.0.0 QueryExecuted= Query: SELECT DISTINCT "MONPIX"."TBLDESCRIPTOR"."IMAGE_NUMBER" AS "ImageNumber", "MONPIX"."TBLDESCRIPTOR"."SUBJECT_DESCRIPTOR" AS "SubjectDescriptor" FROM (( "MONPIX"."TBLPIXPROJECT" "LPA_d1" INNER JOIN "MONPIX"."TBLDESCRIPTOR" ON "LPA_d1"."IMAGE_NUMBER"="MONPIX"."TBLDESCRIPTOR"."IMAGE_NUMBER") INNER JOIN "MONPIX"."TBLPIXPROJECT" "LPA_d2" ON "LPA_d2"."IMAGE_NUMBER"="MONPIX"."TBLDESCRIPTOR"."IMAGE_NUMBER") WHERE ( ( ( "d11"."SUBJECT_DESCRIPTOR" = :SubjectDescriptor1 AND "d12"."SUBJECT_DESCRIPTOR" = :SubjectDescriptor2))) Parameter: :SubjectDescriptor1 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "Graduation Ceremonies". Parameter: :SubjectDescriptor2 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "Vice-Chancellors".

StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket) at Monpix41Public.SundryServices.Sundries.GetRecords(IDataAccessAdapter _daa, ImageInfoSearch iis, Int32 pageNumber, Int32& totalRecords) in D:\Documents and Settings\kkrishna\my documents\visual studio 2010\Projects\Monpix41Public\Monpix41Public\SundryServices\Sundries.cs:line 200 at Monpix41Public.Controllers.SearchController.ImageInfoSearchResults(ImageInfoSearch iis, Int32 page) in D:\Documents and Settings\kkrishna\my documents\visual studio 2010\Projects\Monpix41Public\Monpix41Public\Controllers\SearchController.cs:line 146 at lambda_method(Closure , ControllerBase , Object[] ) at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41() at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass81.<BeginSynchronous>b__7(IAsyncResult _) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.End() at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33() at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49() InnerException: Oracle.DataAccess.Client.OracleException Message=ORA-00904: "d12"."SUBJECT_DESCRIPTOR": invalid identifier Source=Oracle Data Provider for .NET ErrorCode=-2147467259 DataSource=MPIXDEV.WORLD Number=904 Procedure="" StackTrace: at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.DataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) InnerException:

Thanks.

Hi, I think the way I have set up the self join relation is probably wrong?

IRelationPredicateBucket bucket = new RelationPredicateBucket();
                             bucket.Relations.Add(TbldescriptorEntity.Relations.TblpixprojectEntityUsingImageNumber, "d11");
                bucket.Relations.Add(TbldescriptorEntity.Relations.TblpixprojectEntityUsingImageNumber, "d12");

Thanks.

Krish wrote:

Hi, I think the way I have set up the self join relation is probably wrong?

IRelationPredicateBucket bucket = new RelationPredicateBucket();
                             bucket.Relations.Add(TbldescriptorEntity.Relations.TblpixprojectEntityUsingImageNumber, "d11");
                bucket.Relations.Add(TbldescriptorEntity.Relations.TblpixprojectEntityUsingImageNumber, "d12");

Thanks.

That code aliases TblpixprojectEntity two times, which is what you want. This is also done properly in the FROM clause of the query.

What's wrong is the where clause:

SELECT DISTINCT 
    "MONPIX"."TBLDESCRIPTOR"."IMAGE_NUMBER" AS "ImageNumber", 
    "MONPIX"."TBLDESCRIPTOR"."SUBJECT_DESCRIPTOR" AS "SubjectDescriptor" 
FROM (( "MONPIX"."TBLPIXPROJECT" "LPA_d1" 
    INNER JOIN "MONPIX"."TBLDESCRIPTOR" ON "LPA_d1"."IMAGE_NUMBER"="MONPIX"."TBLDESCRIPTOR"."IMAGE_NUMBER") 
    INNER JOIN "MONPIX"."TBLPIXPROJECT" "LPA_d2" ON "LPA_d2"."IMAGE_NUMBER"="MONPIX"."TBLDESCRIPTOR"."IMAGE_NUMBER") 
WHERE ( ( ( "d11"."SUBJECT_DESCRIPTOR" = :SubjectDescriptor1 AND "d12"."SUBJECT_DESCRIPTOR" = :SubjectDescriptor2)))

As you can see, the tables aliased use proper aliases LPA. The where clauses use the literal aliases specified in the code. This is a signal they're wrong as the framework couldn't find any LPA alias given out for the literal alias you specified. (It does that to make sure subtypes aliased with e.g. X get their alias distributed over the multiple tables the subtype is mapped on, as aliasing all these tables with X won't work ).

Looking at your predicates, I see you use TbldescriptorFields.SubjectDescriptor.SetObjectAlias("d11")

but you alias Tblpixproject. So either you used the wrong field in the where clause (e.g. you should use a Tblpixproject field) or you should remove the SetObjectAlias as tbldescriptor isn't aliased. But in that case the filter doesn't make sense, so I suspect you used the wrong fields, e.g. you should use TblpixprojectFields instead (and keep the .SetObjectAlias() of course)

Thank you very much for that. I will only be able to try this after I return from leave on 27 August. I will post again after 27 August.