How to write Hierarchical queries in Vertica - Any inbuilt function

Comments

• Jim_Knicely - Select Field - Administrator

  April 2018

  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

  0
• abhiashu85

  April 2018

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

  0
• bharathwaj1993 - Select Field -

  March 2022

  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?

  0
• marcothesane - Select Field - Administrator

  March 2022

  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
  

  0