时间戳

首页 > 解决方案 > 错误：使用 UNION 和 CASE postgresql 的表缺少 FROM 子句条目

sql - 错误：使用 UNION 和 CASE postgresql 的表缺少 FROM 子句条目

问题描述

我正在尝试VIEW使用UNION，我想要column动态，为此我正在使用CASE.

关注代码：

CREATE VIEW exportU
  AS
    SELECT
      email_user
      cast(null as int) as blabla1
      cast(null as int) as blabla2,
      cast(null as varchar) as blabla3
    FROM table1 a
UNION
    SELECT
      cast(null as varchar) as email_user
      cast(null as int) as blabla1
      cast(null as int) as blabla2,
      cast(null as varchar) as blabla3
    FROM table2 b
UNION
    SELECT
      cast(null as varchar) as email_user
      intColumn,
      intColumn2,
      new_position_id,
      (CASE WHEN a.email_user = c.filled_by THEN a.email_user ELSE c.filled_by END) as "fill"
    FROM table3 c

我收到那个错误：

ERROR:  missing FROM-clause entry for table "a"
LINE 156:    (CASE WHEN a.email_user = c.filled_by THEN a.email_user ELSE c.filled...

我不知道为什么，我alias在表名之后使用..

有人能帮我吗 ？

标签： sqlpostgresql

解决方案

您必须小心逗号以及列的类型和数量。他们必须匹配所有工会。

这是我如何使其工作的示例：

我必须在第一个选择上创建一个虚拟列，因为最后一个选择有一个额外的列。此外，每一列都需要是相同的类型

SQL 演示

CREATE VIEW exportU
  AS
    SELECT
      cast(null as varchar) as email_user,
      cast(null as int) as blabla1,
      cast(null as int) as blabla2,
      cast(null as varchar) as blabla3,
      0 as dummy
    FROM table1 a
UNION
    SELECT
      cast(null as varchar) as email_user,
      cast(null as int) as blabla1,
      cast(null as int) as blabla2,
      cast(null as varchar) as blabla3,
      0 as dummy
    FROM table1 b
UNION
    SELECT
      cast(null as varchar) as email_user,
      cast(null as int) as blabla1,
      cast(null as int) as blabla2,
     '0' as new_position_id, -- blabla3
      (CASE WHEN 0 = 0 THEN 1 ELSE 2 END) as "fill"
    FROM table1 c

而且这种情况是多余的。您可以将其替换为只是c.filled_by因为如果它们相等，则分配哪一个并不重要

 CASE WHEN a.email_user = c.filled_by THEN a.email_user ELSE c.filled_by END

推荐阅读