Hi Henson, > Still deferred: > - B7 Recursive CTE XXX: pending community input on the > ISO/IEC 19075-5 6.17.5 / 4.18.5 interpretation.
ISO/IEC 19075-5 6.17.5 stats that "Row pattern matching is prohibited in recursive queries. For example, the following is a syntax error: CREATE RECURSIVE VIEW Problem... SELECT Kolo, Xoro FROM Ticker... WINDOW W AS... MEASURES..." ISO/IEC 19075-5 4.18.5 stats that "Row pattern matching is prohibited in recursive queries. For example, the following is a syntax error: CREATE RECURSIVE VIEW Problem... SELECT Kolo, Xoro FROM t... MATCH_REZOGNIZE..." >From these it is apparent that CREATE RECURSIVE VIEW cannot be used with RPR in both R010 and R020. Question is, what about CTE queries? I looked into ISO/IEC 9075-2:2016 (I don't have access to 2023) and found this in "7.17 <query expression>". in "Syntax Rule" 3) If <with clause> WC is specified, then: a) Let n be the number of <with list element>s. : : 3) If <with clause> WC is specified, then: : : e) If WC immediately contains RECURSIVE, then WC, its <with list>, and its <with list element>s are said to be potentially recursive. Otherwise, they are said to be non-recursive. f) A potentially recursive <with list element> shall not contain a <row pattern measures> or <row pattern common syntax>. So I think at least SQL:2016 explicitly prohibits using RPR within recursive CTE. I would appreciate if anybody confirms this in SQL:2023. Regards, -- Tatsuo Ishii SRA OSS K.K. English: http://www.sraoss.co.jp/index_en/ Japanese:http://www.sraoss.co.jp
