Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5731

Recursive Query Common Table Expressions (CTE) PostgreSQL

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Not a Bug
    • Icon: Critical Critical
    • None
    • 12.1.1
    • JDBC Driver
    • None
    • Hide

      WITH cte AS (
      – initialization
      SELECT o.id
      o.name,
      o.cs AS CS,
      o.cd,
      1 AS Level,
      o.name AS H_Name,
      o.cs AS H_CS
      FROM VBL.table AS o WHERE o.id_parent IS NULL
      UNION ALL
      – recursive execution
      SELECT ore.id,
      ore.name,
      ore.cs,
      ore.cd,
      cte.Level + 1 AS Level,
      CAST(cte.H_Name || '/' || ore.name AS VARCHAR(2000)) AS H_Name,
      CAST(cte.CS || '/' || CAST(ore.cs AS VARCHAR(255)) AS VARCHAR(255)) AS H_CS
      FROM VBL.table AS ore INNER JOIN cte AS cte ON ore.id_parent = cte.id
      )
      SELECT * FROM cte

      Show
      WITH cte AS ( – initialization SELECT o.id o.name, o.cs AS CS, o.cd, 1 AS Level, o.name AS H_Name, o.cs AS H_CS FROM VBL.table AS o WHERE o.id_parent IS NULL UNION ALL – recursive execution SELECT ore.id, ore.name, ore.cs, ore.cd, cte.Level + 1 AS Level, CAST(cte.H_Name || '/' || ore.name AS VARCHAR(2000)) AS H_Name, CAST(cte.CS || '/' || CAST(ore.cs AS VARCHAR(255)) AS VARCHAR(255)) AS H_CS FROM VBL.table AS ore INNER JOIN cte AS cte ON ore.id_parent = cte.id ) SELECT * FROM cte

      I'm trying to a recursive Common Table Expressions on my organization's VBL. I know it runs on top of PostgreSQL. I'm not responsible for the implementation, I'm just trying to access the data for Data Analysis. When I try to create the Recursive Query it gives me the error: Remote org.postgresql.util.PSQLException: ERROR: recursive query "cte" column 6 has type character varying(255) in non-recursive term but type character varying overall.

      The problem is with the Null value. I can't CAST Null to VARCHAR.

              rhn-engineering-shawkins Steven Hawkins
              kafran Kolmar Vasconcelos (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Created:
                Updated:
                Resolved: