How to write Hierarchical queries in Vertica - Any inbuilt function

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!

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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

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

  • bharathwaj1993bharathwaj1993 - Select Field -

    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?

  • marcothesanemarcothesane - Select Field - Administrator

    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
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file