+ 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.