Hi All,

I'm trying to construct a custom sql to fetch a list of entities that are part of a tree structure. These entities are also part of an inheritance hierarchy. The sql statement runs and returns records, and I have used the hibernate mapping 'act.name as {activity.name}' type insertions to map the columns of the return result to hibernate's expected field names.

I have resolved all kinds of naming issues up to this point and I think the basics of the query are now correct, the only problem is that fails to run the query because it doesn't start with SELECT or SELECT DISTINCT. The common table expression requires that my statement begins with WITH.

Any ideas? It seems that to return the proper map of records using the common table expression my sql must start with the 'WITH' statement, but hibernate will not run my query unless its starts with 'SELECT'

example sql:
WITH findallsubnodes (id, parent_node_id, LEVEL)
AS (SELECT sn.id,
sn.parent_node_id,
0 AS LEVEL
FROM s_nodes sn
WHERE sn.id = :nodeId
UNION ALL
SELECT sn.id,
sn.parent_node_id,
LEVEL - 1
FROM s_nodes sn
JOIN findallsubnodes ld
ON sn.parent_node_id = ld.id)
SELECT a.id AS {action.id},
ac.id AS {comments.id},
ac.comment_text AS {comments.commentText},
t.id AS {task.id},
t.status AS {task.status},
ans.id AS {sel.id},
ans.answer_text AS {sel.answerText},
ae.id AS {evidence.id},
ae.file_name AS {evidence.fileName},
f.id AS {finding.id},
f.name AS {finding.name}
FROM activities a
JOIN actions_nodes asn
ON asn.activity_id = a.id
JOIN findallsubnodes subnodes
ON subnodes.id = asn.s_node_id
LEFT OUTER JOIN actions_comments ac
ON a.id = ac.id
LEFT OUTER JOIN tasks t
ON a.id = t.id
LEFT OUTER JOIN actions_selections ans
ON a.id = ans.id
LEFT OUTER JOIN actions_evidence ae
ON a.id = ae.id
LEFT OUTER JOIN findings f
ON a.id = f.id
WHERE a.entity_id = :assessmentId

and the code which invokes it :

return session.CreateSQLQuery(SQL_STATEMENT)
.AddEntity("action", typeof(Activity))
.AddEntity("task", typeof(Task))
.AddEntity("sel", typeof(AnswerSelection))
.AddEntity("comments", typeof(AssessmentComment))
.AddEntity("evidence", typeof(AssessmentEvidence))
.AddEntity("finding", typeof(Finding))
.SetParameter("assessmentId", assessmentId)
.SetParameter("nodeId", nodeId)
.SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize).List<Activity>();