oracle - 从关键字在 PL-SQL 中未找到预期的位置
问题描述
我有以下代码:
v_SQL := '
-- ==========================================================
-- Main Unpivot Table - To move years from column headings
-- to rows values
-- ==========================================================
SELECT
CAST(FieldYear AS Number) FieldYear
, FieldType
, CASE FieldValue
WHEN '''' THEN NULL
ELSE CAST (FieldValue AS Number(38,8))
END FieldValue
FROM
(SELECT ' ||
v_InsertType ||' AS FieldType
,Y1 as ' || CAST(v_FY0 - 5 - v_OffSet AS CHAR) || '
,Y2 as ' || CAST(v_FY0 - 4 - v_OffSet AS CHAR) || '
,Y3 as ' || CAST(v_FY0 - 3 - v_OffSet AS CHAR) || '
,Y4 as ' || CAST(v_FY0 - 2 - v_OffSet AS CHAR) || '
,Y5 as ' || CAST(v_FY0 - 1 - v_OffSet AS CHAR) || '
,Y6 as ' || CAST(v_FY0 - v_OffSet AS CHAR) || '
,Y7 as ' || CAST(v_FY0 + 1 - v_OffSet AS CHAR) || '
,Y8 as ' || CAST(v_FY0 + 2 - v_OffSet AS CHAR) || '
,Y9 as ' || CAST(v_FY0 + 3 - v_OffSet AS CHAR) || '
,Y10 as ' || CAST(v_FY0 + 4 - v_OffSet AS CHAR) || '
,Y11 as ' || CAST(v_FY0 + 5 - v_OffSet AS CHAR) || '
,Y12 as ' || CAST(v_FY0 + 6 - v_OffSet AS CHAR) || '
,Y13 as ' || CAST(v_FY0 + 7 - v_OffSet AS CHAR) || '
,Y14 as ' || CAST(v_FY0 + 8 - v_OffSet AS CHAR) || '
,Y15 as ' || CAST(v_FY0 + 9 - v_OffSet AS CHAR) || '
,Y16 as ' || CAST(v_FY0 + 10 - v_OffSet AS CHAR) || '
,Y17 as ' || CAST(v_FY0 + 11 - v_OffSet AS CHAR) || '
,Y18 as ' || CAST(v_FY0 + 12 - v_OffSet AS CHAR) || '
,Y19 as ' || CAST(v_FY0 + 13 - v_OffSet AS CHAR) || '
,Y20 as ' || CAST(v_FY0 + 14 - v_OffSet AS CHAR) || '
,Y21 as ' || CAST(v_FY0 + 15 - v_OffSet AS CHAR) || '
,Y22 as ' || CAST(v_FY0 + 16 - v_OffSet AS CHAR) || '
,Y23 as ' || CAST(v_FY0 + 17 - v_OffSet AS CHAR) || '
,Y24 as ' || CAST(v_FY0 + 18 - v_OffSet AS CHAR) || '
,Y25 as ' || CAST(v_FY0 + 19 - v_OffSet AS CHAR) || '
,Y26 as ' || CAST(v_FY0 + 20 - v_OffSet AS CHAR) || '
,Y27 as ' || CAST(v_FY0 + 21 - v_OffSet AS CHAR) || '
,Y28 as ' || CAST(v_FY0 + 22 - v_OffSet AS CHAR) || '
,Y29 as ' || CAST(v_FY0 + 23 - v_OffSet AS CHAR) || '
,Y30 as ' || CAST(v_FY0 + 24 - v_OffSet AS CHAR) || '
,Y31 as ' || CAST(v_FY0 + 25 - v_OffSet AS CHAR) || '
,Y32 as ' || CAST(v_FY0 + 26 - v_OffSet AS CHAR) || '
,Y33 as ' || CAST(v_FY0 + 27 - v_OffSet AS CHAR) || '
,Y34 as ' || CAST(v_FY0 + 28 - v_OffSet AS CHAR) || '
,Y35 as ' || CAST(v_FY0 + 29 - v_OffSet AS CHAR) || '
FROM ' ||
v_tblInsertType || ') UP
UNPIVOT
(FieldValue FOR FieldYear IN
(' || CAST(v_FY0 - 5 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 - 4 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 - 3 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 - 2 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 - 1 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 1 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 2 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 3 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 4 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 5 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 6 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 7 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 8 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 9 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 10 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 11 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 12 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 13 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 14 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 15 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 16 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 17 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 18 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 19 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 20 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 21 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 22 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 23 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 24 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 25 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 26 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 27 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 28 - v_OffSet AS CHAR) || '
,' || CAST(v_FY0 + 29 - v_OffSet AS CHAR) || '
)
)AS unpvt
Execute immediate v_SQL;
其中 v_FY0 是 NUMBER(10);v_SQL NVarchar2(4000),v_InsertType VARCHAR2(4000); 和 v_OffSet 编号(10);v_tblInsertType VARCHAR2(4000);
执行后,我得到: v_FY0 =2018 and v_OffSet = 0;
执行后,我收到错误 - ORA-00923: FROM Keyword not found where expected.
如何解决此问题以及如何使其正常工作。
解决方案
您不能as
在from
子句中使用。
这个查询:
select * from (select dummy from dual) as tab
产生错误ORA-00933
。所以只需删除as
:
select * from (select dummy from dual) tab
推荐阅读
- linq - 如何使用包含在实体框架中的串联字符串上进行查询
- java - RabbitListener 在 Spring Boot 应用程序中一遍又一遍地使用相同的线程
- r - 从 R 中的 YYYYMM 列中提取月份
- kubernetes - 面向最终用户的 kubernaties 中的隔离应用程序
- deep-learning - 关于 caffe caffe.paramspec 没有名为压缩的参数
- redhat - 无法在 redhat 7.4 上注册订阅管理器 - “NoneType”对象没有属性“__getitem__”
- mysql - 根据表B中的年龄计算从表A中选择行
- android - 获取 imageView 颜色并设置补色
- tableau-api - Tableau 中的连接和数据源有什么区别?
- python - Scrapy:忽略响应 403