Hibernate Books

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: SQL Server common table expressions
PostPosted: Thu Sep 16, 2010 3:16 pm 
Newbie

Joined: Wed May 26, 2010 1:23 pm
Posts: 2
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>();


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.