HQL Left outer join with temp tables

0
votes

I'm trying to create a HQL query with a left outer join on tables that are created at runtime.

the following code is the simplified HQL query, which is basically the same.. whatever is in the left outer join clause is created at runtime, and is unlimited by the number of selections in the union

select distinct  r.Param1,  r.Param2,  r.Param3
from Classpath.Classname r 
left outer join ( 
    select something1, something2 from somewhere where something1 in (1,2,3)
    union all 
    select something1, something2 from somewhere where something1 not in (5,8,9)
    union all 
    select something1, something2 from somewhere where something1 = 10
) tblName on tblName.something = r.Param1
where other conditions

How can I make this work?

1
sqlnhibernatehql

1 Answers

0
votes

You cannot. A FROM and JOIN statements are always related to mapped entities... because NHiberante is ORM tool.

If you need such complex SQL Statement, use:

session.CreateSQLQuery(" ... any raw SQL ...")

That is not about HQL but pure SQL.

9.3.5. Queries in native SQL

You may express a query in SQL, using CreateSQLQuery(). You must enclose SQL aliases in braces.

IList<Cat> cats = session.CreateSQLQuery(
    "SELECT {cat.*} FROM CAT {cat} WHERE ROWNUM<10",
    "cat",
    typeof(Cat)
).List<Cat>();

IList<Cat> cats = session.CreateSQLQuery(
    "SELECT {cat}.ID AS {cat.Id}, {cat}.SEX AS {cat.Sex}, " +
           "{cat}.MATE AS {cat.Mate}, {cat}.SUBCLASS AS {cat.class}, ... " +
    "FROM CAT {cat} WHERE ROWNUM<10",
    "cat",
    typeof(Cat)
).List<Cat>()

SQL queries may contain named and positional parameters, just like NHibernate queries.