CODEWITHSUNDEEP
pythonsqlsnowflake-cloud-data-platform
Karthik
Karthik

Reputation: 481

Alternative to WITH RECURSIVE CLAUSE

Snowflake DB does not support recursive with clause function , Need help me on how to achieve below query . Below query works well in Teradata

If any one also can help me to achieve using Python that would be great

WITH RECURSIVE RECURTEMP(ID,KCODE,LVL)
AS(SELECT ID, MIN(KCODE) AS KCODE,1
FROM TABLE_A
GROUP BY 1
UNION ALL
SELECT b.ID, trim(a.KCODE)|| ';'||trim(b.KCODE), LVL+1
FROM TABLE_A a
INNER JOIN RECURTEMP b ON a.ID = b.ID AND a.KCODE > b.KCODE
)
SELECT * FROM RECURTEMP

![Result]: https://i.sstatic.net/9PsbK.jpg

CREATE TABLE MYTABLE (
ID VARCHAR2(50),
KCODE VARCHAR2(50)
);

INSERT INTO MYTABLE VALUES  ('ABCD','K10');
INSERT INTO MYTABLE VALUES  ('ABCD','K53');
INSERT INTO MYTABLE VALUES  ('ABCD','K55');
INSERT INTO MYTABLE VALUES  ('ABCD','K56');

COMMIT;

OUTPUT as below

ID               KCODE          LEVEL  
--------------------------------------
ABCD             K10               1
ABCD             K53;K10           2
ABCD             K55;K10           2
ABCD             K56;K10           2
ABCD             K55;K53;K10       3
ABCD             K56;K53;K10       3
ABCD             K56;K55;K10       3
ABCD             K56;K55;K53;K10   4

Upvotes: 1

Views: 1052

Answers (1)

PaulHoran
PaulHoran

Reputation: 59

Recursive WITH is now supported in Snowflake.

Your query 

WITH RECURSIVE RECURTEMP(ID,KCODE,LVL) AS(
SELECT 
  ID, 
  MIN(KCODE) AS KCODE,
  1
FROM 
  TABLE_A
GROUP BY
  1
UNION ALL
SELECT 
  b.ID, 
  trim(a.KCODE) || ';' || trim(b.KCODE)  AS KCODE, 
  LVL+1
FROM 
  TABLE_A a
  INNER JOIN RECURTEMP b ON (a.ID = b.ID AND a.KCODE > b.KCODE)
)
SELECT * FROM RECURTEMP

Link to article is below.

https://docs.snowflake.net/manuals/user-guide/queries-cte.html#overview-of-recursive-cte-syntax

Upvotes: 1

Related Questions