登录
当前位置： 首页 > 知识库问答 >
问题：

OracleSQL-ORA-00604和ORA-00904错误-带子句和UNION两个查询

裴威
2023-03-14 
WITH SubQry AS
(SELECT DISTINCT
TBL1."IDNUM"

FROM
"LIBRARY"."SALES" TBL4 INNER JOIN "LIBRARY"."CUSTOMER" TBL1 ON TBL4."CUSTOMERKEY" = TBL1."CUSTOMERKEY"
INNER JOIN  "LIBRARY"."TIME" TBL3 ON TBL4."DATEKEY" = TBL3."DATEKEY"
INNER JOIN "LIBRARY"."PRODUCT" TBL2 ON    TBL4."PRODUCTKEY" = TBL2."PRODUCTKEY"

WHERE
TBL1."COUNTRY" IN ('Austria', 'Denmark',  'Belgium')
AND TBL3."FISCALYEAR" BETWEEN '2011' AND '2016')


SELECT
T1."REGION"
, T1."COUNTRY"
, SubQry."IDNUM" AS "BOOKING_IDNUM"
, (CASE WHEN SubQry."IDNUM" IS NULL Then 'New' ELSE 'Existing' END) "BOOKING_FLAG"
, T1."IDNUM"
, T1."CUSTOMER"
, T1. "BUSSINESS"
, T1."PROJECTNUM"
, T1."PROJECTNAME"
, T1."PROJECTOWNER"
, T1."PROJECT_STATUS"
, SUM(T1."PROJECT_VALUE")
, COUNT(*) "TRANSACTION_COUNT"
, TO_CHAR(trunc(T1."CREATEDATE" / 100)) AS "YEARMONTH_CALENDAR"
, (CASE
WHEN T1."BUSS_ID" IN ('ABC', 'DEF')
THEN 'MID' ELSE NULL END) "DRIVE_TYPE"
, (CASE
WHEN T1."IDNUM" IN ('901023','1401516','2401056','2101444')
THEN 'VIP' ELSE 'Standard' END) AS "ACC_TYPE"
, T1."BUSS_ID"

FROM
"LIBRARY"."PROJECT_DETAILS" T1 LEFT JOIN SubQry  ON  T1."IDNUM" =  SubQry."IDNUM"

WHERE
T1."COUNTRY" IN ('Austria', 'Denmark',  'Belgium')
AND T1."BUSS_ID" IN ('ABC', 'DEF')
AND T1."CREATEDATE" >= '20160101'

GROUP BY
T1."REGION"
, T1."COUNTRY"
, SubQry."IDNUM"
, T1."IDNUM"
, T1."CUSTOMER"
, T1. "BUSSINESS"
, T1."PROJECTNUM"
, T1."PROJECTNAME"
, T1."PROJECTOWNER"
, T1."PROJECT_STATUS"
, T1."BUSS_ID"
, T1."CREATEDATE"

HAVING
SUM(T1."PROJECT_VALUE") > 0


UNION


SELECT
T1."REGION"
, T1."COUNTRY"
, SubQry."IDNUM" AS "BOOKING_IDNUM"
, (CASE WHEN SubQry."IDNUM" IS NULL Then 'New' ELSE 'Existing' END) "BOOKING_FLAG"
, T1."IDNUM"
, T1."CUSTOMER"
, T1."BUSSINESS"
, T1."PROJECTNUM"
, T1."PROJECTNAME"
, T1."PROJECTOWNER"
, T1."PROJECT_STATUS"
, SUM(T1."PROJECT_VALUE")
, COUNT(*) "TRANSACTION_COUNT"
, TO_CHAR(trunc(T1."CREATEDATE" / 100)) AS "CREATEDATE_YEARMONTH_CALENDAR"
, (CASE
WHEN T1."BUSS_ID" IN ('ABC', 'JKL', 'PQR')
THEN 'PRO'ELSE NULL END) "DRIVE_TYPE"
, (CASE
WHEN T1."IDNUM" IN ('901023','1401516','2401056','2101444')
THEN 'VIP' ELSE 'Standard' END) AS "ACC_TYPE"
, T1."BUSS_ID"

FROM
"LIBRARY"."PROJECT_DETAILS" T1 LEFT JOIN SubQry  ON  T1."IDNUM" =  SubQry."IDNUM"

WHERE
T1."COUNTRY" IN ('Austria', 'Denmark',  'Belgium')
AND T1."BUSS_ID" IN ('ABC', 'JKL', 'PQR')
AND T1."CREATEDATE" >= '20160101'

GROUP BY
T1."REGION"
, T1."COUNTRY"
, SubQry."IDNUM"
, T1."IDNUM"
, T1."CUSTOMER"
, T1."BUSSINESS"
, T1."PROJECTNUM"
, T1."PROJECTNAME"
, T1."PROJECTOWNER"
, T1."PROJECT_STATUS"
, T1."BUSS_ID"
, T1."CREATEDATE"

HAVING
SUM(T1."PROJECT_VALUE") > 0

你好

上面的查询包括:

  1. WITH子句-本条款的目的是从2011年至2016年间购买物品的客户中识别不同的“IDNUM”。
  2. 第一次查询-查询的主要目的是检查上述WITH子句中是否存在任何选定的“IDNUM”。此外，该查询还应检查所选的“IDNUM”是否与特定的的“BUSS_ID”匹配，并在名为、“DRIVE_TYPE”的列中将其标记为'MID'
  3. 第二次查询-此查询执行的操作与第一次查询几乎相同。唯一的区别是，它在名为“DRIVE_TYPE”的列中将的“IDNUM”标记为'PRO'

您可能已经注意到，在这两个查询中，都有一个通用的"BUSS_ID"：'ABC'

  • 第一个查询-，（CASE WHEN T1."BUSS_ID"IN（'ABC'，'DEF'）THEN'MID'ELSE NULL END）"DRIVE_TYPE"
  • 第二个查询-，（CASE WHEN T1."BUSS_ID"IN（'ABC'，'JKL'，'PQR'）THEN'PRO'ELSE NULL END）"DRIVE_TYPE"

我尝试联合这些查询以将属于两个“BUSS_ID”的每一行显示为两行独立的结果。换句话说，如果一行具有'ABC'的"BUSS_ID"，那么select语句将显示两次。一行带有"DRIVE_TYPE"标记的'MID'和另一行标记的'PRO'。

问题是，当我运行查询时，我得到以下错误：

ORA-00604：递归SQL级别1发生错误

ORA-00904：“从$_。“DATEKEY_0_0”：无效标识符

更让我感到困惑的是，我可以毫无问题地运行 WHERE 条款 1st Query 或 WITH 条款 2nd Query 的组合。但是，当我尝试运行使用子句 1st 查询 UNION 2nd Query 时，我收到错误。


共有1个答案

沈开畅
2023-03-14

这就是CTE的伟大之处。尝试用3个CTE重写，我敢打赌，如果您仍然得到一个，错误消息会更好

WITH SubQry AS
(SELECT DISTINCT
TBL1."IDNUM"

FROM
"LIBRARY"."SALES" TBL4 INNER JOIN "LIBRARY"."CUSTOMER" TBL1 ON TBL4."CUSTOMERKEY" = TBL1."CUSTOMERKEY"
INNER JOIN  "LIBRARY"."TIME" TBL3 ON TBL4."DATEKEY" = TBL3."DATEKEY"
INNER JOIN "LIBRARY"."PRODUCT" TBL2 ON    TBL4."PRODUCTKEY" = TBL2."PRODUCTKEY"

WHERE
TBL1."COUNTRY" IN ('Austria', 'Denmark',  'Belgium')
AND TBL3."FISCALYEAR" BETWEEN '2011' AND '2016'), Q1 AS (
   SELECT
   T1."REGION"
   , T1."COUNTRY"
   , SubQry."IDNUM" AS "BOOKING_IDNUM"
   , (CASE WHEN SubQry."IDNUM" IS NULL Then 'New' ELSE 'Existing' END) "BOOKING_FLAG"
   , T1."IDNUM"
   , T1."CUSTOMER"
   , T1. "BUSSINESS"
   , T1."PROJECTNUM"
   , T1."PROJECTNAME"
   , T1."PROJECTOWNER"
   , T1."PROJECT_STATUS"
   , SUM(T1."PROJECT_VALUE")
   , COUNT(*) "TRANSACTION_COUNT"
   , TO_CHAR(trunc(T1."CREATEDATE" / 100)) AS "YEARMONTH_CALENDAR"
   , (CASE
   WHEN T1."BUSS_ID" IN ('ABC', 'DEF')
   THEN 'MID' ELSE NULL END) "DRIVE_TYPE"
   , (CASE
   WHEN T1."IDNUM" IN ('901023','1401516','2401056','2101444')
   THEN 'VIP' ELSE 'Standard' END) AS "ACC_TYPE"
   , T1."BUSS_ID"

   FROM
   "LIBRARY"."PROJECT_DETAILS" T1 LEFT JOIN SubQry  ON  T1."IDNUM" =  SubQry."IDNUM"

   WHERE
   T1."COUNTRY" IN ('Austria', 'Denmark',  'Belgium')
   AND T1."BUSS_ID" IN ('ABC', 'DEF')
   AND T1."CREATEDATE" >= '20160101'

   GROUP BY
   T1."REGION"
   , T1."COUNTRY"
   , SubQry."IDNUM"
   , T1."IDNUM"
   , T1."CUSTOMER"
   , T1. "BUSSINESS"
   , T1."PROJECTNUM"
   , T1."PROJECTNAME"
   , T1."PROJECTOWNER"
   , T1."PROJECT_STATUS"
   , T1."BUSS_ID"
   , T1."CREATEDATE"

   HAVING
   SUM(T1."PROJECT_VALUE") > 0
), Q2 AS
(
  SELECT
  T1."REGION"
  , T1."COUNTRY"
  , SubQry."IDNUM" AS "BOOKING_IDNUM"
  , (CASE WHEN SubQry."IDNUM" IS NULL Then 'New' ELSE 'Existing' END) "BOOKING_FLAG"
  , T1."IDNUM"
  , T1."CUSTOMER"
  , T1."BUSSINESS"
  , T1."PROJECTNUM"
  , T1."PROJECTNAME"
  , T1."PROJECTOWNER"
  , T1."PROJECT_STATUS"
  , SUM(T1."PROJECT_VALUE")
  , COUNT(*) "TRANSACTION_COUNT"
  , TO_CHAR(trunc(T1."CREATEDATE" / 100)) AS "CREATEDATE_YEARMONTH_CALENDAR"
  , (CASE
  WHEN T1."BUSS_ID" IN ('ABC', 'JKL', 'PQR')
  THEN 'PRO'ELSE NULL END) "DRIVE_TYPE"
  , (CASE
  WHEN T1."IDNUM" IN ('901023','1401516','2401056','2101444')
  THEN 'VIP' ELSE 'Standard' END) AS "ACC_TYPE"
  , T1."BUSS_ID"

  FROM
  "LIBRARY"."PROJECT_DETAILS" T1 LEFT JOIN SubQry  ON  T1."IDNUM" =  SubQry."IDNUM"

  WHERE
  T1."COUNTRY" IN ('Austria', 'Denmark',  'Belgium')
  AND T1."BUSS_ID" IN ('ABC', 'JKL', 'PQR')
  AND T1."CREATEDATE" >= '20160101'

  GROUP BY
  T1."REGION"
  , T1."COUNTRY"
  , SubQry."IDNUM"
  , T1."IDNUM"
  , T1."CUSTOMER"
  , T1."BUSSINESS"
  , T1."PROJECTNUM"
  , T1."PROJECTNAME"
  , T1."PROJECTOWNER"
  , T1."PROJECT_STATUS"
  , T1."BUSS_ID"
  , T1."CREATEDATE"

  HAVING
  SUM(T1."PROJECT_VALUE") > 0
)
SELECT * FROM Q1
UNION
SELECT * FROM Q2

还可以考虑将第一个CTE更改为使用不同于IDNUM的名称，比如BOOKING_IDNUM

WITH SubQry AS
(SELECT DISTINCT
TBL1.BOOKING_IDNUM
-- etc
 类似资料：
  • ORA-00604-ORA-00904的原因：在线重新定义

    我在一个表上在线重新定义了表。它工作得很好，但后来当我试图截断表时，我得到了这个错误。 当我移动表格时，我也会得到同样的错误： 错误的原因是什么？

  • SQL Oracle错误：ORA-00904

    我正在创建一个表，我输入了这个命令： 我得到了这个错误： 有人能告诉我错误在哪里以及如何纠正吗？

  • mysql查询两个表，UNION和where子句

    问题内容： 我有两张桌子。 我这样查询： 我使用UNION是因为如果用户在第一个表和第二个表中都存在，我想为每个用户获取不同的值。 例如： 如果peter在任何一张桌子上，我应该一次获得名称，因为它在两个桌子上都存在。 我仍然从第一张桌子得到一排，从第二张桌子得到第二排。怎么了？ 任何帮助表示赞赏。 问题答案： 您的SQL有两个问题： （这是 不是 问题，但应考虑）使用过，而不是表，您创建性能噩梦

  • ORA-00904:无效标识符错误

    Oracle 11g Express Edition..创建表时出错 ORA-00904:无效标识符

  • ORA 00904错误：标识符无效

    我已经在虚拟XP中安装了Oracle 10g，并使用 并且成功地创建了表。但是当我试图通过简单的查询获取值时，如 我得到的错误就像 ORA-00904："bbb"：无效标识符 我不能理解我在这里做错了什么。

  • ORA-22992/ORA-06502+ORA-06512错误

    我做了一些研究，有一个建议可以使用双重 但是现在我得到了ORA-06502/ORA-06512错误： ORA-06502:PL/SQL:erreur numérique ou erreur sur une valeur ORA-06512:àsys.xmltype“，ligne 272 ORA-06512:àligne 1 06502。00000-“PL/SQL：数值或值错误%s” *原因：出现算

相关问答

ORA-22992/ORA-06502+ORA-06512错误SQL错误： ORA-00904："TIMESTAMP"：无效的IDENTIFIERSQL错误：ORA-00904:标识符无效使用带union和CLOB字段的select时出现错误ORA-00932SQL错误：ORA-00904:：标识符3无效

相关文章

ORA-00904“无效标识符”，用于group by子句中的标识符ORACLE出现错误1033和错误ORA-00600的解决方法两个查询的SQL UNION，重复的列名错误带有WHERE子句的UNION-Android Eclipseoracle ORA-00988 missing or invalid password 错误

相关阅读

JDBC Statements, PreparedStatement和CallableStatement语句MariaDB From子句MariaDB Distinct子句MariaDB Like子句MariaDB Where子句

相关工具

录音和播放每日金句图片下载和保存图片下载和浏览文件解压和压缩Demo

相关文档

fbreader 和 移动 DM 和 加解密Android 开机 和 编译系统 和 binder理解 WebKit 和 ChromiumReact 和 Webpack 小书常见 HTTP / FTP / WebSocket 错误代码大全
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.