title: Create recursive query in Snowflake with a Left Join condition?
body: <p>I am trying to create a recursive query that relies on a <code>LEFT JOIN</code> condition, but I am not sure if it is possible, especially in Snowflake.</p>
  <p>I have three tables: <code>ITEM</code>, <code>ITEMHIERARCHY</code>, and <code>ITEMVALUE</code></p>
  <pre><code>CREATE TABLE ITEM
  (
    NAME STRING
  );
  
  INSERT INTO ITEM(NAME)
  VALUES
  ('Item1'),('Item2'),('Item3'),('Item4'),('Item5'),('Item6');
  
  CREATE TABLE ITEMHIERARCHY
  (
   ITEM STRING,
   SUBITEM STRING 
  );
  
  INSERT INTO ITEMHIERARCHY(ITEM,SUBITEM)
  VALUES
  ('Item2','Item3'),('Item2','Item4'),('Item4','Item5'),('Item6','Item4');
  
  CREATE TABLE ITEMVALUE
  (
    ITEM STRING,
    VALUE NUMERIC(25,10)
  );
  
  INSERT INTO ITEMVALUE(ITEM,VALUE)
  VALUES
  ('Item1',34.2),('Item3',40.5),('Item5',20.3),('Item6',77.7);
  </code></pre>
  <p>My goal is to return a list of all <code>ITEMs</code> with values and sub-item values rolled-up:</p>
  <pre><code>Item1, 34.2
  Item2, 60.8 //roll-up of Item3 + Item4
  Item3, 40.5
  Item4, 20.3 //roll-up of Item5
  Item5, 20.3
  Item6, 77.7 //since Item6 value is given, dont roll-up from Item4
  </code></pre>
  <p>Note that even though <code>Item6</code> is a roll-up from <code>Item4</code> because there is already a given value of <code>77.7</code> on the <code>ITEMVALUE</code> table, the roll-up is ignored.</p>
  <p>Here is my attempt at a failing recursive query due to the <code>LEFT JOIN</code> in the <code>UNION ALL</code> clause:</p>
  <pre><code>WITH RECURSIVE ITEMHIERARCHYFULL
    -- Column names for the &quot;view&quot;/CTE
    (ITEM,SUBITEM,VALUE) 
  AS
    -- Common Table Expression
    (
  
      -- Anchor Clause
      SELECT it.NAME ITEM, ih.SUBITEM, iv.VALUE
        FROM ITEM it
        --These left-joins work
        LEFT JOIN ITEMVALUE iv ON iv.ITEM = it.NAME 
        LEFT JOIN ITEMHIERARCHY ih ON ih.ITEM = it.ITEM
                                   AND iv.VALUE IS NULL
  
      UNION ALL
  
      -- Recursive Clause
      SELECT  ihf.ITEM, ih.SUBITEM,  
        IFF(ihf.VALUE IS NOT NULL,ihf.VALUE,iv.VALUE)
        FROM ITEMHIERARCHYFULL ihf
        LEFT JOIN ITEMVALUE iv ON iv.ITEM = ihf.SUBITEM
        LEFT JOIN ITEMHIERARCHY ih ON ih.ITEM = ihf.SUBITEM
                                      AND iv.VALUE IS NULL 
    )
  
   -- This is the &quot;main select&quot;.
   SELECT ITEM, SUM(VALUE) AS VALUE
   FROM ITEMHIERARCHYFULL
   GROUP BY ITEM
   ORDER BY ITEM
   ;
  </code></pre>
  <p>The goal of the query is to first get all top level <code>ITEMs</code> from the <code>ITEM</code> table, search for a corresponding value on the <code>ITEMVALUE</code> table, and, if none is found, join to the <code>ITEMHIERARCHY</code> Table to retrieve all <code>SUBITEMs</code> that compose the top level <code>ITEMs</code>. I would then like to recursively search on the <code>ITEMVALUE</code> table for a <code>SUBITEM-VALUE</code> match, or, if none is found, retrieve the <code>SUBITEMs</code> from the <code>ITEMHIERARCHY</code> table.</p>
  <p>The first set of <code>LEFT-JOINs</code> work, but not the ones under the <code>UNION ALL</code> giving me the error:</p>
  <pre><code>SQL compilation error: OUTER JOINs with a self reference are not allowed in a recursive CTE.
  </code></pre>
  <p>Is there a better way to do what I am trying to do in <code>Snowflake</code> or am I not thinking about this correctly?</p>
  <p>Currently I manually wrote out the recursive layers to 5 levels meaning I have to add a level if the <code>ITEMHIERARCHY</code> table becomes more complex.</p>