I've been using NHibernate for a while now but mostly with convention based auto persistence model generation and then performing basic CRUD operations on the objects.
I now have that need to execute a rather nasty query against a legacy system along with a hand full of parameters (most of the where clause needs to be parameterized). I'm not clear on whether I should be defining the individual objects and then trying to implement the complex query OR simply using native SQL to define the query. I'm also not clear on the correct syntax to define the query in an hbm.xml file. The native SQL query follows. Thanks to anyone who can help me sort this out.
WITH CTE AS ( SELECT
substr(KEY1||KEY2||KEY3||'-'||digits(KEY4)||'-'||digits(KEY5),1,17) KEY_ID,
SUM(CASE WHEN BATCH between 200801001 and 200812999 THEN TRANSACTION_AMOUNT ELSE 0 END) AS _2008AMOUNT,
SUM(CASE WHEN BATCH between 200901001 and 200912999 THEN TRANSACTION_AMOUNT ELSE 0 END) AS _2009AMOUNT,
SUM(CASE WHEN BATCH between 201001001 and 201012999 THEN TRANSACTION_AMOUNT ELSE 0 END) AS _2010AMOUNT
FROM _SCHEMA.TRANSACTIONS
WHERE OWNER='02'
AND TRANSACTION_TYPE in ('A','B')
AND BATCH between 200801000 AND 201012999
GROUP BY KEY1, KEY2, KEY3, KEY4, KEY5
HAVING SUM(CASE WHEN BATCH between 200801001 and 200812999 THEN TRANSACTION_AMOUNT ELSE 0 END) 0
OR SUM(CASE WHEN BATCH between 200901001 and 200912999 THEN TRANSACTION_AMOUNT ELSE 0 END) 0
OR SUM(CASE WHEN BATCH between 201001001 and 201012999 THEN TRANSACTION_AMOUNT ELSE 0 END) 0
)
SELECT
B.OWNER
,CTE1.MAX_ITEM_AMOUNT
,CTE1.SUM_ITEM_AMOUNT
,B.PRIMARY_SECONDARY_IDENTITY
,B.PRIMARY_ID
,B.PRIMARY_NAME
,B.KEY_ID
,B.KEY_AMOUNT
,CASE(B.KEYCLASS) WHEN 'C' THEN 'CLASS1' WHEN 'S' THEN 'CLASS2' WHEN 'M' THEN 'CLASS3' WHEN 'F' THEN 'CLASS4' END AS KEYCLASS
,NA.PRIMARY_CT AS PRIMARY_CITY
,NA.PRIMARY_ST AS PRIMARY_STATE
,B.KEY_EFFECTIVE_DATE
,B.KEY_ENTRY_DATE
,CTE._2008AMOUNT
,CTE._2009AMOUNT
,CTE._2010AMOUNT
,B.EMPLOYEE_NAME
FROM CTE
JOIN _SCHEMA.TABLE2 B ON CTE.KEY_ID = B.KEY_ID
JOIN (
SELECT
PRIMARY_SECONDARY_IDENTITY
, MAX(ITEM_AMOUNT) AS MAX_ITEM_AMOUNT
, SUM(ITEM_AMOUNT) AS SUM_TTEM_AMOUNT
FROM CTE
JOIN _SCHEMA.TABLE2 B ON CTE.KEY_ID = B.KEY_ID
GROUP BY PRIMARY_SECONDARY_IDENTITY
) CTE1 ON CTE1.PRIMARY_SECONDARY_IDENTITY = B.PRIMARY_SECONDARY_IDENTITY
JOIN _SCHEMA.TABLE3 NA ON B.PRIMARY_ID = NA.KEY1 || DIGITS(NA.KEY2)
ORDER BY MAX_ITEM_AMOUNT DESC, PRIMARY_SECONDARY_IDENTITY
