Ken -
Thank you very much for your help! The missing piece was really the
AddEntity() method call!
I also had to remove the {t.Priority} from the CASE WHEN clause.
Here is my code if anyone else ever comes across a similiar need.
ISQLQuery qry = NHibernateSession.Instance.Current.CreateSQLQuery
("SELECT CASE WHEN Priority ='High' THEN 0 ELSE 1 END AS
BatchPrioritySort, {t.*} FROM TranslationBatch t ORDER BY
BatchPrioritySort ASC").AddEntity("t",typeof(TranslationBatch));
IList<TranslationBatch> listBatches = qry.List<TranslationBatch>();
Thanks again Ken, have a good day!
Rob
On Feb 12, 4:17 pm, Ken Egozi <[email protected]> wrote:
> > ISQLQuery qry = NHibernateSession.Instance.Current.CreateSQLQuery ("SELECT
> > CASE WHEN {t.Priority} ='High' THEN 0 ELSE 1 END AS BatchPrioritySort, {t.*}
> > FROM TranslationBatch AS {t} ORDER BY BatchPrioritySort
> > ASC").AddEntity(typeof(TranslationBatch));
>
> two fixes:
>
> 1. FROM TranslationBatch AS {t} => FROM TranslationBatch t
> 2. .AddEntity(typeof(TranslationBatch)); =>
> .AddEntity("t",typeof(TranslationBatch));
>
>
>
> On Thu, Feb 12, 2009 at 11:43 PM, RDot <[email protected]> wrote:
>
> > Hi Ken -
>
> > I'm feeling like an Nhibernate loser! I can't figure this out...
>
> > I have been trying variations of what you sent me and nothing
> > works. Here is some sample code.
>
> > ISQLQuery qry = NHibernateSession.Instance.Current.CreateSQLQuery
> > ("SELECT CASE WHEN {t.Priority} ='High' THEN 0 ELSE 1 END AS
> > BatchPrioritySort, {t.*} FROM TranslationBatch AS {t} ORDER BY
> > BatchPrioritySort ASC").AddEntity(typeof(TranslationBatch));
>
> > I have tried putting the sql in exactly as you have typed it. I have
> > modified the alias syntax several different ways. By alias syntax I
> > mean the {t.*} in the select clause and the {t} when declaring the
> > table alias. I've reviewed the NHibernate PDF and it appears i'm
> > doing this correctly.
>
> > Whenever I do a .list() on the qry object I get the following
> > Exception..
> > --"could not execute query\r\n[ SELECT CASE WHEN {t.Priority}
> > ='High' THEN 0 ELSE 1 END AS BatchPrioritySort, {t.*} FROM
> > TranslationBatch AS {t} ORDER BY
> > BatchPrioritySort ASC ]\r\n[SQL: SELECT CASE WHEN {t.Priority}
> > ='High' THEN 0 ELSE 1 END AS BatchPrioritySort, {t.*} FROM
> > TranslationBatch AS {t} ORDER
> > BY BatchPrioritySort ASC]"
>
> > --inner exception is - "Incorrect syntax near '.'."
>
> > Obviously i'm not building the SQL text appropriately because what
> > Nhibernate is generating is blowing up. Any other thoughts?
>
> > Heck I can't even get the following code to work...
>
> > ISQLQuery qry = NHibernateSession.Instance.Current.CreateSQLQuery
> > ("SELECT * FROM TranslationBatch").AddEntity(typeof
> > (TranslationBatch));
> > qry.List() throws an Exception...
> > --"could not execute query\r\n[ SELECT * FROM TranslationBatch ]\r\n
> > [SQL: SELECT * FROM TranslationBatch]:'
> > --Inner Exception - {"Value cannot be null.\r\nParameter name:
> > fieldName"}
>
> > I figured this would be easy. Any insight you can provide would
> > be appreciated. Good Karma you way. :-)
>
> > Thanks -
>
> > On Feb 12, 8:46 am, Ken Egozi <[email protected]> wrote:
> > > This mean that NH will expand the {t.*] bit to the fields that are mapped
> > > on TranslationBatch.
>
> > > On Thu, Feb 12, 2009 at 4:37 PM, RDot <[email protected]>
> > wrote:
>
> > > > Hi Ken -
>
> > > > Thanks for your response - I need to understand the expansion syntax
> > > > more. I will search around to understand this syntax.
>
> > > > On Feb 12, 12:56 am, Ken Egozi <[email protected]> wrote:
> > > > > you can use SQL for this one, with the expansion syntax:
> > > > > SELECT
> > > > > CASE
> > > > > WHEN Priority = 'High' THEN 0
> > > > > ELSE 1
> > > > > END AS BatchPrioritySort,
> > > > > *{t.*}*
> > > > > FROM
> > > > > TranslationBatch *AS t*
> > > > > ORDER BY BatchPrioritySort ASC
>
> > > > > On Thu, Feb 12, 2009 at 12:59 AM, John Rayner <
> > [email protected]
> > > > >wrote:
>
> > > > > > Don't think I follow your reasons why you can't have a formula
> > field.
> > > > > > It looks to me like that would be the way forward here.
>
> > > > > > On Feb 11, 2:47 pm, RDot <[email protected]> wrote:
> > > > > > > Hi -
>
> > > > > > > nhibernate version: 1.2.1.4000
> > > > > > > SQL Server 2005
> > > > > > > C# 2.0
>
> > > > > > > If you use Projections when using the Criteria API can you
> > still
> > > > > > > populate the regular entity object?
>
> > > > > > > I want to add a new field in my select statement which I can
> > then
> > > > > > > order by but I also want to populate the entity object.
>
> > > > > > > I have read the documentation and browsed the web and it appears
> > > > > > > whenever you use projections you are now working with an IList.
>
> > > > > > > The sample SQL I need is something along this line...
>
> > > > > > > select case when Priority = 'High' then 0 else 1
> > > > > > > end AS BatchPrioritySort, *
> > > > > > > from TranslationBatch
> > > > > > > order by BatchPrioritySort ASC
>
> > > > > > > So I want a List<TranslationBatch> objects returned. I
> > can't
> > > > use
> > > > > > > a formula field because sometimes the 'case when' clause will
> > sort
> > > > > > > differently based on different priority values. I want to use
> > the
> > > > > > > Criteria API because I build a long Where clause based on fields
> > the
> > > > > > > user has filtered on.
>
> > > > > > > I have researched and tried to make the SetResultTransformer
> > work
> > > > > > > to no avail.
>
> > > > > > > This seems very simple, am I missing something?
>
> > > > > > > Thanks -
>
> > > > > --
> > > > > Ken Egozi.
> > > >http://www.kenegozi.com/bloghttp://www.delver.comhttp://www.musicglue.
> > ..
>
> > > --
> > > Ken Egozi.
> >http://www.kenegozi.com/bloghttp://www.delver.comhttp://www.musicglue...
>
> --
> Ken
> Egozi.http://www.kenegozi.com/bloghttp://www.delver.comhttp://www.musicglue.comhttp://www.castleproject.orghttp://www.gotfriends.co.il
--~--~---------~--~----~------------~-------~--~----~
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=en
-~----------~----~----~----~------~----~------~--~---
