时间戳

首页 > 解决方案 > 是什么导致我的联合声明从未知到文本的转换错误失败？

postgresql - 是什么导致我的联合声明从未知到文本的转换错误失败？

问题描述

编辑：当我在底部进行联合时，我现在收到一个错误“无法找到从未知到文本的转换函数”，但是这些表具有相同的列，所以我无法弄清楚是什么导致了问题。有什么建议么？我看过的一切都表明这应该有效。当我更改两列的 ID 号和 LEG 名称时，问题是否可能是？

WITH RECURSIVE "child" AS (
    SELECT "ConsultantDisplayID",
           "FirstName", 
           "LastName" ,
           "ParentPersonDisplayID"
    FROM "public"."flight_export_consultant"
    WHERE "ConsultantDisplayID" = '4019'
UNION 
    SELECT c."ConsultantDisplayID", 
           c."FirstName" ,
           c."LastName" ,
           c."ParentPersonDisplayID"
    FROM "public"."flight_export_consultant" AS c 
    JOIN "child" AS cd 
    ON c."ParentPersonDisplayID" = cd."ConsultantDisplayID"),
    
"sponsor" AS (
    SELECT 
        "child".*,
--      c1."ConsultantDisplayID",
        c1."FirstName" AS "Sponsor FirstName",
        c1."LastName" AS "Sponsor LastName",
        'JJones' AS "LEG"
    FROM "public"."flight_export_consultant" AS c1
    LEFT JOIN "child" 
    ON c1."ConsultantDisplayID" = "child"."ParentPersonDisplayID"), 
    
"promo" AS (
    SELECT "sponsor"."ConsultantDisplayID",
           "sponsor"."FirstName", 
           "sponsor"."LastName" ,
           pr."Rank" AS "First Time Promo Consultant New Rank",
           pr."EffectiveDate",
           "sponsor"."ParentPersonDisplayID",
           "sponsor"."Sponsor FirstName",
           "sponsor"."Sponsor LastName",
           "sponsor"."LEG"
    FROM "sponsor"
    LEFT JOIN "all_time_first_promotion" as pr
    ON "sponsor"."ConsultantDisplayID" = pr."PersonDisplayID"
),

"child2" AS (
    SELECT "ConsultantDisplayID",
           "FirstName", 
           "LastName" ,
           "ParentPersonDisplayID"
    FROM "public"."flight_export_consultant"
    WHERE "ConsultantDisplayID" = '3030'
UNION 
    SELECT c."ConsultantDisplayID", 
           c."FirstName" ,
           c."LastName" ,
           c."ParentPersonDisplayID"
    FROM "public"."flight_export_consultant" AS c 
    JOIN "child2" AS cd2 
    ON c."ParentPersonDisplayID" = cd2."ConsultantDisplayID"),

"sponsor2" AS (
    SELECT 
        "child2".*,
--      c1."ConsultantDisplayID",
        c1."FirstName" AS "Sponsor FirstName",
        c1."LastName" AS "Sponsor LastName",
        'JJones' AS "LEG"
    FROM "public"."flight_export_consultant" AS c1
    LEFT JOIN "child2" 
    ON c1."ConsultantDisplayID" = "child2"."ParentPersonDisplayID"),
    
"promo2" AS (
    SELECT "sponsor2"."ConsultantDisplayID",
           "sponsor2"."FirstName", 
           "sponsor2"."LastName" ,
           pr."Rank" AS "First Time Promo Consultant New Rank",
           pr."EffectiveDate",
           "sponsor2"."ParentPersonDisplayID",
           "sponsor2"."Sponsor FirstName",
           "sponsor2"."Sponsor LastName",
           "sponsor2"."LEG"
    FROM "sponsor2"
    LEFT JOIN "all_time_first_promotion" as pr
    ON "sponsor2"."ConsultantDisplayID" = pr."PersonDisplayID"
)
    
SELECT * 
FROM "promo"
--WHERE "promo"."First Time Promo Consultant New Rank" IS NOT NULL 

UNION

SELECT *
FROM "promo2"

标签： postgresqlrecursionunion

解决方案

你的语法不正确。您的 CTE 应位于顶部，而您的 CTE 应位于UNION下方。

您将需要重命名至少一组递归 CTE 才能完成这项工作。它应该如下所示：

WITH RECURSIVE "child" AS (
    SELECT "ConsultantDisplayID",
           "FirstName", 
           "LastName" ,
           "ParentPersonDisplayID"
    FROM "public"."flight_export_consultant"
    WHERE "ConsultantDisplayID" = '4019'
UNION 
    SELECT c."ConsultantDisplayID", 
           c."FirstName" ,
           c."LastName" ,
           c."ParentPersonDisplayID"
    FROM "public"."flight_export_consultant" AS c 
    JOIN "child" AS cd 
    ON c."ParentPersonDisplayID" = cd."ConsultantDisplayID"),
    
"sponsor" AS (
    SELECT 
        "child".*,
        c1."FirstName" AS "Sponsor FirstName",
        c1."LastName" AS "Sponsor LastName",
        'JJones' AS "LEG"
    FROM "public"."flight_export_consultant" AS c1
    LEFT JOIN "child" 
    ON c1."ConsultantDisplayID" = "child"."ParentPersonDisplayID"), 
    
"promo" AS (
    SELECT "sponsor"."ConsultantDisplayID",
           "sponsor"."FirstName", 
           "sponsor"."LastName" ,
           pr."Rank" AS "First Time Promo Consultant New Rank",
           pr."EffectiveDate",
           "sponsor"."ParentPersonDisplayID",
           "sponsor"."Sponsor FirstName",
           "sponsor"."Sponsor LastName",
           "sponsor"."LEG"
    FROM "sponsor"
    LEFT JOIN "all_time_first_promotion" as pr
    ON "sponsor"."ConsultantDisplayID" = pr."PersonDisplayID"
),
"child2" AS (
    SELECT "ConsultantDisplayID",
           "FirstName", 
           "LastName" ,
           "ParentPersonDisplayID"
    FROM "public"."flight_export_consultant"
    WHERE "ConsultantDisplayID" = '3030'
UNION 
    SELECT c."ConsultantDisplayID", 
           c."FirstName" ,
           c."LastName" ,
           c."ParentPersonDisplayID"
    FROM "public"."flight_export_consultant" AS c 
    JOIN "child2" AS cd 
    ON c."ParentPersonDisplayID" = cd."ConsultantDisplayID"),
    
"sponsor2" AS (
    SELECT 
        "child".*,
        c1."FirstName" AS "Sponsor FirstName",
        c1."LastName" AS "Sponsor LastName",
        'CKeck' AS "LEG"
    FROM "public"."flight_export_consultant" AS c1
    LEFT JOIN "child2" as child
    ON c1."ConsultantDisplayID" = "child"."ParentPersonDisplayID"), 
    
"promo2" AS (
    SELECT "sponsor"."ConsultantDisplayID",
           "sponsor"."FirstName", 
           "sponsor"."LastName" ,
           pr."Rank" AS "First Time Promo Consultant New Rank",
           pr."EffectiveDate",
           "sponsor"."ParentPersonDisplayID",
           "sponsor"."Sponsor FirstName",
           "sponsor"."Sponsor LastName",
           "sponsor"."LEG"
    FROM "sponsor2" as sponsor
    LEFT JOIN "all_time_first_promotion" as pr
    ON "sponsor"."ConsultantDisplayID" = pr."PersonDisplayID"
)
  
SELECT * FROM "promo"
WHERE "First Time Promo Consultant New Rank" is not null

UNION

    
SELECT * FROM "promo2"
WHERE "First Time Promo Consultant New Rank" is not null

推荐阅读