I'm using CONNECT BY to generate an exploded BOM. I have everything working, except the Quantity Per. This is because for each "Child" part, I need to know how many times its "Parent" part is being used, so I can multiply the Child's Qty Per times the Parent's Qty Per.
In Oracle, it looks like you can use "PRIOR [fieldname]" in the SELECT statement of a CONNECT BY, but when I try to do that in Snowflake, it fails (telling me "PRIOR" is unexpected). What I'd like to use is something like this:
SELECT Level, Parent, Child, Quantity_Per * PRIOR Quantity_Per
FROM bom
START WITH Type = 'BOM'
CONNECT BY
Parent = PRIOR Child
Does anyone know (short of writing my own subquery in the SELECT) how to get a "prior" value in Snowflake's CONNECT BY?
Well, it appears there isn't a built-in function just to get the previous value, but it is still possible.
After I posted this, I worked out a method that involved using SYS_CONNECT_BY_PATH. This generates a string showing the values for a given field at each level of the hierarchy (i.e. "Part 1 > Part 2 > Part 3" etc). So I generated the path for Qtr Per and then used SPLIT_PART to get the element in the array before the current one.
SPLIT_PART(SYS_CONNECT_BY_PATH(Qty_Per, ','), -1)
However, it was then that I realized that I don't need just one value from the level before the current level. I need every value from every level above the current one, so I can multiply the Qtr Per for each Child by its Parent's Qtr Per at each step of the hierarchy. If I don't do that, then a multiplication from Level 2 (for example) wouldn't be reflected at Level 8 (or whatever).
For this, I used SYS_CONNECT_BY_PATH again, with an asterisk as the delimiter (for multiplication):
SYS_CONNECT_BY_PATH(Qty_Per, '*')
Then I used a JavaScript UDF that split the string into elements in an array and then multiplied each one by the previous one, which I got here.
The end result looked like:
MULTIPLY(SYS_CONNECT_BY_PATH(Qty_Per, '*')) AS Quantity_Per