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

Recursive Query Common Table Expressions (CTE) PostgreSQL

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Critical
    • Resolution: Explained
    • Affects Version/s: 12.1.1
    • Fix Version/s: None
    • Component/s: JDBC Driver
    • Labels:
      None
    • Steps to Reproduce:
      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

      Description

      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.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                doutorchefe Kolmar Vasconcelos
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: