'Multiple 'with' in HANA query

I have the following query:

with cte as (#large query),
cte2 as (#query that uses cte multiple times)
select * from (#large query) where field in (cte2.field)

I had to use this method that seems to me low performance to be able to filter values that come from complex calculations, I do not have permissions to generate tables and temporary tables.

I get syntax error messages just before cte as

sqlcommon-table-expressionhana


Solution 1:[1]

Multiple WITH aliases are allowed on recent versions of SAP HANA. That's not the issue here. Following example query works:

WITH va AS (SELECT 1 one FROM dummy), vb AS (SELECT 2 two FROM dummy)
SELECT *
FROM va, vb

As it is correctly mentioned by @astentx in the comments the syntax issue seem to be, that you are asking if something is in (cte2.field). in works on sets that are returned by sub-queries.

e.g.

WITH va AS (SELECT 1 one FROM dummy), vb AS (SELECT 2 two FROM dummy)
SELECT *
FROM va
WHERE va.one IN (SELECT two FROM vb)

Solution 2:[2]

Your question is quite "schematic" - a little more specific information would make it a lot easier to understand what you're trying to do. Like this, it's not clear at all which objects are being accesses and what cte stands for.

From the HANA SQL Reference Guide WITH clause doesn't support recursive query expressions, if that is part of the queries.

Cheers, The Crew

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mathias Kemeter
Solution 2

Related Questions