AnsweredAssumed Answered

Help needed with this SQL

Question asked by arslan on Mar 14, 2015
Latest reply on Mar 18, 2015 by muralidharand
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
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

  WITH  nodes as (
SELECT as id,
childNode.uuid AS uuid
FROM alf_child_assoc assoc
JOIN alf_node parentNode ON ( = assoc.parent_node_id)
JOIN alf_node childNode ON ( = assoc.child_node_id)
WHERE = 13232985),
             properties as (
            select, nodes.uuid, anp.string_value, anp.long_value, aq.local_name
              from nodes nodes
                  join alf_node_properties anp on (anp.node_id =
                  join  alf_qname aq on ( = anp.qname_id)
            where aq.local_name in ('status', 'assignedTo', 'assignedBY', 'notes')),
        p_status as (
            select, p.string_value as status
              from properties p
            where p.local_name in ('status')),       
       p_assignedTo as (
            select, p.string_value as assignedTo
              from properties p
            where p.local_name = 'assignedTo'),       
       p_assignedBy as (
            select, p.string_value as assignedBy
              from properties p
            where p.local_name = 'assignedBY') ,       
       p_notes as (
            select, p.string_value as notes
              from properties p
            where p.local_name = 'notes')
        from   prop_status status
               join p_assignedTo assignedTo on ( =
               join p_assignedBy assignedBy on ( =
               join p_notes notes on ( =;