REPLACE VIEW Failed. 6922: Illegal or unsupported use of subquery/derived table inside a recursive query/view.
I have my query something like the below
REPLACE RECURSIVE VIEW <DB>.<VIEWNAME> (COL1, COL2, COL3,ROW_NUM,DEPTH) AS ( SELECT COLUMNS.. FROM X LEFT OUTER JOIN K2 ON X.COL1=Y.COL1 AND COL3='ANYWHERELAST' WHERE COL2 IS NULL QUALIFY ROW_NUM=1 UNION ALL SELECT COLUMNS.. FROM <VIEWNAME> LEFT OUTER JOIN K2 ON CONDITIONS AND .... WHERE K2.COLUMN IS NOT NULL AND NEWDEPTH <= (SELECT COUNT(*) FROM Z WHERE COL2='SOME CONDITION') );
The problem is with the subquery in the second part of UNION ALL.How to resolve this part?
Thanks in advance