Using CTE in Retool Query JSON with SQL

Queries and Resources
1

I am getting the below error while trying to use recursive CTE to fetch data that meets a certain condition.

Parse error on line 1: WITH RECURSIVE EquivalentParts AS ( ---------------^ Expecting 'AS', got 'LITERAL'

What I actually want is the query to output all parts that are transitively equivalent to PN0001 through the recursive process. Here is the content of table:

part_id equivalent_part_id
PN0001 PN0002
PN0002 PN0003
PN0003 PN0004
PN0004 PN0005
PN0005 PN0006
PN0006 PN0007
PN0007 PN0008
PN0008 PN0009
PN0009 PN0010
PN00013 PN00011
PN00015 PN00012
PN00016 PN00014
PN0007 PN00018

Here is the query I am using:

WITH RECURSIVE EquivalentParts AS (
    SELECT eqvP.part_id, eqvP.equivalent_part_id
    FROM {{ getPartsEquivalency.data }} AS eqvP
    WHERE eqvP.part_id = 'PN0001'
    
    UNION ALL
    
    SELECT pe.part_id, pe.equivalent_part_id
    FROM {{ getPartsEquivalency.data }} AS pe
    INNER JOIN EquivalentParts AS ep 
      ON pe.part_id = ep.equivalent_part_id
)
SELECT part_id, equivalent_part_id
FROM EquivalentParts;

I think CTEs don’t work in Retool the same way they do in databases. Can anyone help me with this?

Thanks!

2

Retool uses AlaSQL (https://alasql.org) to support the "Query JSON with SQL" functionality.

While AlaSQL does support Common Table Expressions (CTEs) in general, it does not seem to support self-referencing CTEs like the one in your query above.

From the 9 year old With · AlaSQL/alasql Wiki · GitHub :

In current version of AlaSQL CTE canot be self-referencing.

There's also this (CTE should be self-referencing · Issue #200 · AlaSQL/alasql · GitHub) open issue for recursive CTE support to be added to AlaSQL.

1 Like
3

What type of resource is getPartsEquivalency? Is this a query you could run directly on your database? Typically we recommend Query JSON with SQL if you are combining data from multiple different resources (Rest api data joined with Postgres data, for example)