topic Help needed with this SQL in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/help-needed-with-this-sql/m-p/301503#M254633 <HTML><HEAD></HEAD><BODY><SPAN>I have a parent Node ID and i want to get all its children and few properties of child nodes, this query is not performing for me</SPAN><BR /><SPAN>can someone review it and tell me there is a better way to do the same thing or can be this be improved so that it performs</SPAN><BR /><BR /><SPAN>&nbsp; WITH&nbsp; nodes as ( </SPAN><BR /><SPAN>SELECT </SPAN><BR /><SPAN>childNode.id as id,</SPAN><BR /><SPAN>childNode.uuid AS uuid</SPAN><BR /><SPAN>FROM alf_child_assoc assoc</SPAN><BR /><SPAN>JOIN alf_node parentNode ON (parentNode.id = assoc.parent_node_id)</SPAN><BR /><SPAN>JOIN alf_node childNode ON (childNode.id = assoc.child_node_id)</SPAN><BR /><SPAN>WHERE parentNode.id = 13232985),</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; properties as (</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select nodes.id, nodes.uuid, anp.string_value, anp.long_value, aq.local_name</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from nodes nodes </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; join alf_node_properties anp on (anp.node_id = nodes.id) </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; join&nbsp; alf_qname aq on (aq.id = anp.qname_id)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where aq.local_name in ('status', 'assignedTo', 'assignedBY', 'notes')),</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_status as (</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select p.id, p.string_value as status</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from properties p</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where p.local_name in ('status')),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_assignedTo as (</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select p.id, p.string_value as assignedTo</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from properties p</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where p.local_name = 'assignedTo'),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_assignedBy as (</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select p.id, p.string_value as assignedBy</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from properties p</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where p.local_name = 'assignedBY') ,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p_notes as (</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select p.id, p.string_value as notes</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from properties p</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where p.local_name = 'notes') </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select&nbsp; </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; status.status,</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; assignedTo.assignedTo,</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; assignedBy.assignedBY,</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; notes.notes</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from&nbsp;&nbsp; prop_status status</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; join p_assignedTo assignedTo on (assignedTo.id = status.id)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; join p_assignedBy assignedBy on (assignedBy.id = assignedTo.id)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; join p_notes notes on (notes.id = status.id);</SPAN></BODY></HTML> Sat, 14 Mar 2015 16:54:25 GMT arslan 2015-03-14T16:54:25Z Help needed with this SQL https://connect.hyland.com/t5/alfresco-archive/help-needed-with-this-sql/m-p/301503#M254633 I have a parent Node ID and i want to get all its children and few properties of child nodes, this query is not performing for mecan someone review it and tell me there is a better way to do the same thing or can be this be improved so that it performs&nbsp; WITH&nbsp; nodes as ( SELECT childNode.id as id,chi Sat, 14 Mar 2015 16:54:25 GMT https://connect.hyland.com/t5/alfresco-archive/help-needed-with-this-sql/m-p/301503#M254633 arslan 2015-03-14T16:54:25Z Re: Help needed with this SQL https://connect.hyland.com/t5/alfresco-archive/help-needed-with-this-sql/m-p/301504#M254634 <HTML><HEAD></HEAD><BODY><SPAN>Hi,</SPAN><BR /><SPAN>It is not recommended to write queries directly against the Alfresco database. </SPAN><BR /><SPAN>In the future, alfresco might add/remove few fields during upgrade or service pack / hot-fix</SPAN><BR /><SPAN>I would recommend always read the data by using Alfresco API and manipulate the date in your code.</SPAN><BR /><BR /></BODY></HTML> Wed, 18 Mar 2015 05:04:15 GMT https://connect.hyland.com/t5/alfresco-archive/help-needed-with-this-sql/m-p/301504#M254634 muralidharand 2015-03-18T05:04:15Z