I have been trying to get results using HQL for two classes. It works for one table but does not work for two tables.<\/p>\n
The two entities below;<\/p>\n
public class A_CUSTOMER\n {\n public virtual string CUSTOMER_NO { get; set; }\n public virtual string ADDRESS_LINE { get; set; }\n\n public virtual IList ACCOUNTS { get; set; }\n }\n\npublic class A_ACCOUNT\n {\n public virtual string AC_NO { get; set; }\n public virtual string CUST_NO { get; set; }\n public virtual string CCY { get; set; } \n public virtual string AVL_BAL { get; set; } \n\n\n public virtual A_CUSTOMER CUSTOMER { get; set; }\n\n }\n<\/code><\/pre>\nMappings for the classes below;<\/p>\n
public class A_CUSTOMERMap:ClassMap\n {\n public A_CUSTOMERMap()\n {\n Id(x => x.CUSTOMER_NO);\n Map(x => x.ADDRESS_LINE);\n\n HasMany(x => x.ACCOUNTS).KeyColumns.Add(\"CUST_NO\");\n }\n }\n\n\npublic class A_CUST_ACCOUNTMap:ClassMap\n {\n public A_CUST_ACCOUNTMap()\n {\n Id(x => x.AC_NO);\n Map(x=>x.CUST_NO);\n Map(x => x.AVL_BAL);\n Map(x => x.CCY);\n References(x => x.CUSTOMER,\"CUST_NO\");\n\n }\n }\n<\/code><\/pre>\nNow, i have a code like this in my controller;<\/p>\n
string myString = \"1001,1002,1003,1004,1005\";\nvar indNumbers = myString.Split(',');\nstring qry = @\"select h.AC_NO,h.BRANCH_CODE,h.CUST_NO,h.CCY,h.AVL_BAL ,g.ADDRESS_LINE\"+\n \"from A_ACCOUNT as h,A_CUSTOMER g \"+ \n \"where h.CUST_NO=g.CUSTOMER_NO AND h.AC_NO IN (:list)\";\n ;\nres = session.CreateQuery(qry)\n .SetParameterList(\"list\", indNumbers)\n .List();\n<\/code><\/pre>\nI have also tried different query string<\/p>\n
string qry = @\"select h.AC_NO,h.BRANCH_CODE,h.CUST_NO,h.CCY,h.AVL_BAL ,g.ADDRESS_LINE\"+\n \"from A_ACCOUNT as h left join A_CUSTOMER g on \"+\n \"h.CUST_NO=g.CUSTOMER_NO\"+ \n \"where h.AC_NO IN (:list)\";\n<\/code><\/pre>\nThe error encountered below, any help will be much appreciated.<\/p>\n
NHibernate.Hql.Ast.ANTLR.QuerySyntaxException was unhandled by user code\n HResult=-2146232832\n Message=Exception of type 'Antlr.Runtime.NoViableAltException' was thrown. near line 1, column 83\n Source=NHibernate\n StackTrace:\n at NHibernate.Hql.Ast.ANTLR.ErrorCounter.ThrowQueryException()\n at NHibernate.Hql.Ast.ANTLR.HqlParseEngine.Parse()\n at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(String queryString, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)\n at NHibernate.Engine.Query.HQLStringQueryPlan.CreateTranslators(String hql, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)\n at NHibernate.Engine.Query.HQLStringQueryPlan..ctor(String hql, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)\n at NHibernate.Engine.Query.HQLStringQueryPlan..ctor(String hql, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)\n at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(String queryString, Boolean shallow, IDictionary`2 enabledFilters)\n at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(String query, Boolean shallow)\n at NHibernate.Impl.AbstractSessionImpl.CreateQuery(String queryString)\n at MvcApplication2.Controllers.CustAccountController.GetCustData(jQueryDataTableParamModel param) in ...\n at lambda_method(Closure , ControllerBase , Object[] )\n at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)\n at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)\n at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)\n at System.Web.Mvc.Async.AsyncControllerActionInvoker.c__DisplayClass42.b__41()\n at System.Web.Mvc.Async.AsyncResultWrapper.c__DisplayClass8`1.b__7(IAsyncResult _)\n at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.End()\n at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)\n at System.Web.Mvc.Async.AsyncControllerActionInvoker.c__DisplayClass37.c__DisplayClass39.b__33()\n at System.Web.Mvc.Async.AsyncControllerActionInvoker.c__DisplayClass4f.b__49()\n InnerException: \n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"I have been trying to get results using HQL for two classes. It works for one table but does not work for two tables. The two entities below; public class A_CUSTOMER { public virtual string CUSTOMER_NO { get; set; } public virtual string ADDRESS_LINE { get; set; } public virtual IList ACCOUNTS { get; set; […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-6664","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/6664","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=6664"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/6664\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=6664"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=6664"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=6664"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}