Create recursive query in Snowflake with a Left Join condition?

I am trying to create a recursive query that relies on a LEFT JOIN condition, but I am not sure if it is possible, especially in Snowflake.

I have three tables: ITEM, ITEMHIERARCHY, and ITEMVALUE

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);

My goal is to return a list of all ITEMs with values and sub-item values rolled-up:

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

Note that even though Item6 is a roll-up from Item4 because there is already a given value of 77.7 on the ITEMVALUE table, the roll-up is ignored.

Here is my attempt at a failing recursive query due to the LEFT JOIN in the UNION ALL clause:

WITH RECURSIVE ITEMHIERARCHYFULL
  -- Column names for the "view"/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 "main select".
 SELECT ITEM, SUM(VALUE) AS VALUE
 FROM ITEMHIERARCHYFULL
 GROUP BY ITEM
 ORDER BY ITEM
 ;

The goal of the query is to first get all top level ITEMs from the ITEM table, search for a corresponding value on the ITEMVALUE table, and, if none is found, join to the ITEMHIERARCHY Table to retrieve all SUBITEMs that compose the top level ITEMs. I would then like to recursively search on the ITEMVALUE table for a SUBITEM-VALUE match, or, if none is found, retrieve the SUBITEMs from the ITEMHIERARCHY table.

The first set of LEFT-JOINs work, but not the ones under the UNION ALL giving me the error:

SQL compilation error: OUTER JOINs with a self reference are not allowed in a recursive CTE.

Is there a better way to do what I am trying to do in Snowflake or am I not thinking about this correctly?

Currently I manually wrote out the recursive layers to 5 levels meaning I have to add a level if the ITEMHIERARCHY table becomes more complex.

Answer

Here’s a working example that gives you the results you expected. You can also view it on SQLFiddle.

WITH CTE AS
  (
    SELECT 
        i.NAME
        , IH.SUBITEM AS descendant        
        , CASE WHEN IV.VALUE IS NULL THEN 1 ELSE 0 END AS LEVEL
    FROM ITEM AS i
    LEFT JOIN ITEMHIERARCHY AS IH
        ON i.NAME = IH.ITEM
    LEFT JOIN ITEMVALUE AS IV
        ON I.NAME = IV.ITEM
    UNION ALL
    SELECT 
        CTE.NAME
        , sIH.SUBITEM
        , 1 AS LEVEL
    FROM CTE
      INNER JOIN ITEM AS si
        ON CTE.descendant = si.NAME
      INNER JOIN ITEMHIERARCHY AS sIH
        ON si.NAME = sIH.ITEM
  ), CTE2 AS 
(
SELECT 
    CTE.NAME     
    , LEVEL
    , SUM(IV.VALUE) AS VALUE
    , ROW_NUMBER()OVER(PARTITION BY CTE.NAME ORDER BY CTE.LEVEL ASC) AS RNK    
FROM CTE
LEFT JOIN ITEMVALUE AS IV
    ON (CTE.LEVEL=0 AND CTE.NAME = IV.ITEM)
    OR (CTE.LEVEL <> 0 AND CTE.descendant = IV.ITEM)    
GROUP BY CTE.NAME, CTE.LEVEL
) 
SELECT 
    NAME
    , VALUE
FROM CTE2
WHERE RNK = 1
ORDER BY 
    NAME
;

RESULTS:

NAME    VALUE
Item1   34.2000000000
Item2   60.8000000000
Item3   40.5000000000
Item4   20.3000000000
Item5   20.3000000000
Item6   77.7000000000