Oracle替代MySQL的ADDDATE()？UNION查询遇ORA-00923错误如何解决？

针对你遇到的两个问题，直接给出解决办法：

1. 替代MySQL的ADDDATE函数 #

MySQL的ADDDATE('1970-01-01', N)用于给指定日期添加N天，Oracle中无需额外函数，直接对日期类型值进行数值加法即可：

DATE '1970-01-01' + (t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i)

这里DATE '1970-01-01'是Oracle标准的日期字面量写法，确保类型准确，日期加数字默认就是添加对应天数。

2. 解决ORA-00923错误 #

Oracle对SELECT语句有两个硬性要求：

• 任何SELECT必须包含FROM子句（可使用虚拟表DUAL）
• UNION联合的每个分支必须保证列名/别名一致

原MySQL的子查询写法不符合Oracle规则，需做两处修改：

• 给每个SELECT的列添加明确别名（如AS i）
• 每个SELECT后补充FROM DUAL
• 优先用UNION ALL替代UNION（无重复值时效率更高）

完整适配后的Oracle SQL #

SELECT 
    DATE '1970-01-01' + (t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) AS selected_date
FROM
    (
        SELECT 0 AS i FROM DUAL UNION ALL
        SELECT 1 AS i FROM DUAL UNION ALL
        SELECT 2 AS i FROM DUAL UNION ALL
        SELECT 3 AS i FROM DUAL UNION ALL
        SELECT 4 AS i FROM DUAL UNION ALL
        SELECT 5 AS i FROM DUAL UNION ALL
        SELECT 6 AS i FROM DUAL UNION ALL
        SELECT 7 AS i FROM DUAL UNION ALL
        SELECT 8 AS i FROM DUAL UNION ALL
        SELECT 9 AS i FROM DUAL
    ) t0,
    (
        SELECT 0 AS i FROM DUAL UNION ALL
        SELECT 1 AS i FROM DUAL UNION ALL
        SELECT 2 AS i FROM DUAL UNION ALL
        SELECT 3 AS i FROM DUAL UNION ALL
        SELECT 4 AS i FROM DUAL UNION ALL
        SELECT 5 AS i FROM DUAL UNION ALL
        SELECT 6 AS i FROM DUAL UNION ALL
        SELECT 7 AS i FROM DUAL UNION ALL
        SELECT 8 AS i FROM DUAL UNION ALL
        SELECT 9 AS i FROM DUAL
    ) t1,
    (
        SELECT 0 AS i FROM DUAL UNION ALL
        SELECT 1 AS i FROM DUAL UNION ALL
        SELECT 2 AS i FROM DUAL UNION ALL
        SELECT 3 AS i FROM DUAL UNION ALL
        SELECT 4 AS i FROM DUAL UNION ALL
        SELECT 5 AS i FROM DUAL UNION ALL
        SELECT 6 AS i FROM DUAL UNION ALL
        SELECT 7 AS i FROM DUAL UNION ALL
        SELECT 8 AS i FROM DUAL UNION ALL
        SELECT 9 AS i FROM DUAL
    ) t2,
    (
        SELECT 0 AS i FROM DUAL UNION ALL
        SELECT 1 AS i FROM DUAL UNION ALL
        SELECT 2 AS i FROM DUAL UNION ALL
        SELECT 3 AS i FROM DUAL UNION ALL
        SELECT 4 AS i FROM DUAL UNION ALL
        SELECT 5 AS i FROM DUAL UNION ALL
        SELECT 6 AS i FROM DUAL UNION ALL
        SELECT 7 AS i FROM DUAL UNION ALL
        SELECT 8 AS i FROM DUAL UNION ALL
        SELECT 9 AS i FROM DUAL
    ) t3,
    (
        SELECT 0 AS i FROM DUAL UNION ALL
        SELECT 1 AS i FROM DUAL UNION ALL
        SELECT 2 AS i FROM DUAL UNION ALL
        SELECT 3 AS i FROM DUAL UNION ALL
        SELECT 4 AS i FROM DUAL UNION ALL
        SELECT 5 AS i FROM DUAL UNION ALL
        SELECT 6 AS i FROM DUAL UNION ALL
        SELECT 7 AS i FROM DUAL UNION ALL
        SELECT 8 AS i FROM DUAL UNION ALL
        SELECT 9 AS i FROM DUAL
    ) t4

额外说明 #

• UNION ALL替代UNION：原查询分支都是0-9的唯一值，UNION会做额外去重检查，UNION ALL直接合并结果，性能更优。
• 日期字面量：DATE '1970-01-01'比字符串转日期更可靠，避免因会话NLS_DATE_FORMAT配置不同导致的转换错误。

内容的提问来源于stack exchange，提问作者Wail