I had a scenario in Oracle where i neeed to match a substring part of
column with a list of values. i was using sqlfunction projection for
applying the substring on the required column , and included that
projection as part of an In Clause Restriction. Below is the
simplified criteria i wrote for that.
ICriteria criteriaQuery = session.CreateCriteria<Meeting>()
.Add(Restrictions.In(
Projections.SqlFunction(
"substring",
NHibernateUtil.String,
Projections.Property("Code"),
Projections.Constant(1),
Projections.Constant(3)),
new string[] { "D01", "D02" }))
.Add(Restrictions.In("TypeId", meetingTypes));
The problem that i had with this was that the generated SQL was wrong,
where the number of parameters registered for the statement are more
than what the statement actually uses and some paramaters are repeated
even though they are not used. This causes the statement to fail with
the message - ORA-01036: illegal variable name/number.
Generated Query
SELECT this_.Meeting_id as Meeting1_0_2_, .....
WHERE substr(this_.Mcs_Main, :p0, :p1) in (:p2, :p3)
and this_.Meeting_Type_Id in (:p4, :p5);
:p0 = 1, :p1 = 3, :p2 = 1, :p3 = 3, :p4 = 'D02', :p5 = 'D03', :p6 =
101, :p7 = 102
p2 and p3 are generated again and are duplicates of p0, p1 because of
which the entire query is failing.
I was able to temporarily resolve this by mapping a a new property
with a formula, but i dont think that is the right approach since the
formula will be executed always even when i dont need the substring to
be evaluated.
Any suggestions on whether projections work fine when used with the
combination of In clause, the same projection works fine when i use
Equal Restriction and not In. Is the Criteria i used correct.
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=.
