How to write Hierarchical queries in Vertica - Any inbuilt function

abhiashu85

Hello Everyone,

I am looking for function in Vertica which can write Hierarchical queries like in Oracle we have CONNECT_BY_PATH function.

Appreciate any pointers or help here. Thank You!

Jim_Knicely

There is an extension that might be able to help you out in the Vertica github repository:

https://github.com/vertica/Vertica-Extension-Packages/tree/master/compatlib_functions

abhiashu85

Thank you for your quick response. Will try at my end.

bharathwaj1993

This Vertica extension supports only integer inputs and does not work if the parent & child relationship is established as a STRING. is there a way to resolve this?

marcothesane

Since Version 10.1, we do have the recursive query capability

See here:
Data Preparation

DROP TABLE IF EXISTS rec;
CREATE TABLE rec(id,first_name,last_name,reports_to) AS
SELECT  1,'Arthur','Dent'           ,NULL UNION ALL 
SELECT  2,'Ford'  ,'Prefect'        ,1    UNION ALL 
SELECT  3,'Zaphod','Beeblebrox'     ,1    UNION ALL 
SELECT  4,'Tricia','McMillan'       ,1    UNION ALL 
SELECT  5,'Gag','Halfrunt'          ,2    UNION ALL 
SELECT  6,'Prostetnic Vogon','Jeltz',2    UNION ALL 
SELECT  7,'Lionel','Prosser'        ,4    UNION ALL 
SELECT  8,'Benji','Mouse'           ,4    UNION ALL 
SELECT  9,'Frankie','Mouse'         ,4    UNION ALL 
SELECT 10,'Svlad','Cjelli'          ,3
;     

Demo Test

WITH  
RECURSIVE hier AS (
  SELECT 
      id 
    , first_name
    , last_name
    , first_name||' '||last_name AS repchain
    FROM rec
    WHERE reports_to IS NULL
    UNION ALL
    SELECT
      chi.id
    , chi.first_name
    , chi.last_name
    , par.repchain||'<-'||chi.first_name||' '||chi.last_name
    FROM rec chi
    JOIN hier par ON chi.reports_to = par.id
)
SELECT * FROM hier;
-- out  id |    first_name    | last_name  |                     repchain                      
-- out ----+------------------+------------+---------------------------------------------------
-- out   1 | Arthur           | Dent       | Arthur Dent
-- out   2 | Ford             | Prefect    | Arthur Dent<-Ford Prefect
-- out   3 | Zaphod           | Beeblebrox | Arthur Dent<-Zaphod Beeblebrox
-- out   4 | Tricia           | McMillan   | Arthur Dent<-Tricia McMillan
-- out   5 | Gag              | Halfrunt   | Arthur Dent<-Ford Prefect<-Gag Halfrunt
-- out   6 | Prostetnic Vogon | Jeltz      | Arthur Dent<-Ford Prefect<-Prostetnic Vogon Jeltz
-- out  10 | Svlad            | Cjelli     | Arthur Dent<-Zaphod Beeblebrox<-Svlad Cjelli
-- out   7 | Lionel           | Prosser    | Arthur Dent<-Tricia McMillan<-Lionel Prosser
-- out   8 | Benji            | Mouse      | Arthur Dent<-Tricia McMillan<-Benji Mouse
-- out   9 | Frankie          | Mouse      | Arthur Dent<-Tricia McMillan<-Frankie Mouse