function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
p1 force developerp1 force developer 

hierarchical query: using SOQL / APEX

The following example code demonstrates how to migrate a simple hierarchical query:

Oracle
======

SELECT "NAME", "PARENT", LEVEL
  FROM COMPANY
 START WITH ("NAME" = 'Company Ltd')
 CONNECT BY ("PARENT" = PRIOR "NAME");
 

SQL Server
==========

WITH
   h$cte AS
   (
      SELECT COMPANY.NAME, COMPANY.PARENT, 1 AS LEVEL, CAST(row_number() OVER(
         ORDER BY @@spid) AS varchar(max)) AS path
      FROM dbo.COMPANY
      WHERE ((COMPANY.NAME = 'Company Ltd'))
       UNION ALL
      SELECT COMPANY.NAME, COMPANY.PARENT, h$cte.LEVEL + 1 AS LEVEL, path + ',' + CAST(row_number() OVER(
         ORDER BY @@spid) AS varchar(max)) AS path
      FROM dbo.COMPANY, h$cte
      WHERE ((COMPANY.PARENT = h$cte.NAME))
   )
 
   SELECT h$cte.NAME, h$cte.PARENT, h$cte.LEVEL
   FROM h$cte
   ORDER BY h$cte.path
 
Note   The ROW_NUMBER() function evaluates the path column to provide Oracle nodes ordering.

 

Above code is for Oracle and SQL Server.

 

I need to implement it in SFDC using SOQL and APEX. Can some one please help!!!!

 

thanks

 

 

 

 

GowthamSenGowthamSen

Hi,

 

Have you got any response or idea on how to do this?

 

I am in need of similar thing.

 

Could you please let me know ?

 

Best regards

Gowtham
JoshRivJoshRiv
CTEs are not a supported feature of SOQL to my knowledge and this is 7 yrs later and still the case.