sql - ORA-00903: 使用三引号的多行 spark sql 字符串的无效表名错误
问题描述
我有一个像这样的火花 SQL 字符串:
val sql_1= """SELECT TG.TG_ID,
| TG.ORG_NO,
| TO_CHAR(VC.DATA_DATE, 'yyyymmdd') AS DATA_DATE,
| EDP.METER_ID,
| EDP.CONS_SORT,
| EDP.WIRING_MODE,
| VC.PHASE_FLAG,
| VC.U1,
| VC.U2,
| VC.U3,
| VC.U4,
| VC.U5,
| VC.U6,
| VC.U7,
| VC.U8,
| VC.U9,
| VC.U10,
| VC.U11,
| VC.U12,
| VC.U13,
| VC.U14,
| VC.U15,
| VC.U16,
| VC.U17,
| VC.U18,
| VC.U19,
| VC.U20,
| VC.U21,
| VC.U22,
| VC.U23,
| VC.U24,
| VC.U25,
| VC.U26,
| VC.U27,
| VC.U28,
| VC.U29,
| VC.U30,
| VC.U31,
| VC.U32,
| VC.U33,
| VC.U34,
| VC.U35,
| VC.U36,
| VC.U37,
| VC.U38,
| VC.U39,
| VC.U40,
| VC.U41,
| VC.U42,
| VC.U43,
| VC.U44,
| VC.U45,
| VC.U46,
| VC.U47,
| VC.U48,
| VC.U49,
| VC.U50,
| VC.U51,
| VC.U52,
| VC.U53,
| VC.U54,
| VC.U55,
| VC.U56,
| VC.U57,
| VC.U58,
| VC.U59,
| VC.U60,
| VC.U61,
| VC.U62,
| VC.U63,
| VC.U64,
| VC.U65,
| VC.U66,
| VC.U67,
| VC.U68,
| VC.U69,
| VC.U70,
| VC.U71,
| VC.U72,
| VC.U73,
| VC.U74,
| VC.U75,
| VC.U76,
| VC.U77,
| VC.U78,
| VC.U79,
| VC.U80,
| VC.U81,
| VC.U82,
| VC.U83,
| VC.U84,
| VC.U85,
| VC.U86,
| VC.U87,
| VC.U88,
| VC.U89,
| VC.U90,
| VC.U91,
| VC.U92,
| VC.U93,
| VC.U94,
| VC.U95,
| VC.U96
| FROM E_MP_VOL_CURVE VC
| INNER JOIN E_DATA_MP EDP
| ON VC.ID = EDP.ID
| INNER JOIN G_TG TG
| ON EDP.TG_ID = TG.TG_ID
| WHERE TG.PUB_PRIV_FLAG = '01'
| AND VC.DATA_DATE >= TO_DATE('20200801', 'yyyymmdd')
| AND VC.DATA_DATE <= TO_DATE('20200831', 'yyyymmdd')
| AND EDP.CONS_SORT NOT LIKE '06'
| AND VC.PHASE_FLAG IN (1, 2, 3)
| AND TG.ORG_NO LIKE '134010902'
| --AND TG.TG_ID IN (1646178, 1646179)
| ORDER BY TG.ORG_NO, TG.TG_ID, EDP.CONS_SORT, VC.DATA_DATE, VC.PHASE_FLAG""".stripMargin
,这会给我“ORA-00903:无效的表名”错误。但是,原来的 SQL 字符串,即如下:
SELECT TG.TG_ID,
TG.ORG_NO,
TO_CHAR(VC.DATA_DATE, 'yyyymmdd') AS DATA_DATE,
EDP.METER_ID,
EDP.CONS_SORT,
EDP.WIRING_MODE,
VC.PHASE_FLAG,
VC.U1,
VC.U2,
VC.U3,
VC.U4,
VC.U5,
VC.U6,
VC.U7,
VC.U8,
VC.U9,
VC.U10,
VC.U11,
VC.U12,
VC.U13,
VC.U14,
VC.U15,
VC.U16,
VC.U17,
VC.U18,
VC.U19,
VC.U20,
VC.U21,
VC.U22,
VC.U23,
VC.U24,
VC.U25,
VC.U26,
VC.U27,
VC.U28,
VC.U29,
VC.U30,
VC.U31,
VC.U32,
VC.U33,
VC.U34,
VC.U35,
VC.U36,
VC.U37,
VC.U38,
VC.U39,
VC.U40,
VC.U41,
VC.U42,
VC.U43,
VC.U44,
VC.U45,
VC.U46,
VC.U47,
VC.U48,
VC.U49,
VC.U50,
VC.U51,
VC.U52,
VC.U53,
VC.U54,
VC.U55,
VC.U56,
VC.U57,
VC.U58,
VC.U59,
VC.U60,
VC.U61,
VC.U62,
VC.U63,
VC.U64,
VC.U65,
VC.U66,
VC.U67,
VC.U68,
VC.U69,
VC.U70,
VC.U71,
VC.U72,
VC.U73,
VC.U74,
VC.U75,
VC.U76,
VC.U77,
VC.U78,
VC.U79,
VC.U80,
VC.U81,
VC.U82,
VC.U83,
VC.U84,
VC.U85,
VC.U86,
VC.U87,
VC.U88,
VC.U89,
VC.U90,
VC.U91,
VC.U92,
VC.U93,
VC.U94,
VC.U95,
VC.U96
FROM E_MP_VOL_CURVE VC
INNER JOIN E_DATA_MP EDP
ON VC.ID = EDP.ID
INNER JOIN G_TG TG
ON EDP.TG_ID = TG.TG_ID
WHERE TG.PUB_PRIV_FLAG = '01'
AND VC.DATA_DATE >= TO_DATE('20200801', 'yyyymmdd')
AND VC.DATA_DATE <= TO_DATE('20200831', 'yyyymmdd')
AND EDP.CONS_SORT NOT LIKE '06'
AND VC.PHASE_FLAG IN (1, 2, 3)
AND TG.ORG_NO LIKE '134010902'
--AND TG.TG_ID IN (1646178, 1646179)
ORDER BY TG.ORG_NO, TG.TG_ID, EDP.CONS_SORT, VC.DATA_DATE, VC.PHASE_FLAG
在 Navicat 中运行良好。三引号语法来自这个问题(How to execute multi line sql in spark sql)。任何建议表示赞赏!
解决方案
现在使用以下语法,它可以工作:
val sql_user= s"(SELECT TG.TG_ID, TG.ORG_NO, TO_CHAR(VC.DATA_DATE, 'yyyymmdd') AS DATA_DATE, EDP.METER_ID, EDP.CONS_SORT, EDP.WIRING_MODE," +
s"VC.PHASE_FLAG,VC.U1, VC.U2, VC.U3, VC.U4, VC.U5, VC.U6, VC.U7, VC.U8, VC.U9, VC.U10, VC.U11, VC.U12,VC.U13, VC.U14, VC.U15, VC.U16, VC.U17," +
s"VC.U18, VC.U19, VC.U20, VC.U21, VC.U22, VC.U23, VC.U24, VC.U25, VC.U26, VC.U27, VC.U28, VC.U29, VC.U30, VC.U31, VC.U32, VC.U33,VC.U34, VC.U35, " +
s"VC.U36, VC.U37, VC.U38, VC.U39, VC.U40, VC.U41, VC.U42, VC.U43, VC.U44, VC.U45, VC.U46, VC.U47, VC.U48, VC.U49, VC.U50, VC.U51, VC.U52, VC.U53, " +
s"VC.U54,VC.U55, VC.U56, VC.U57, VC.U58, VC.U59, VC.U60, VC.U61, VC.U62, VC.U63, VC.U64, VC.U65, VC.U66, VC.U67, VC.U68, VC.U69, VC.U70, VC.U71, " +
s"VC.U72, VC.U73, VC.U74, VC.U75,VC.U76, VC.U77, VC.U78, VC.U79, VC.U80, VC.U81, VC.U82, VC.U83, VC.U84, VC.U85, VC.U86, VC.U87, VC.U88, VC.U89, " +
s"VC.U90, VC.U91, VC.U92, VC.U93, VC.U94, VC.U95, VC.U96 FROM E_MP_VOL_CURVE VC INNER JOIN E_DATA_MP EDP ON VC.ID = EDP.ID INNER JOIN G_TG TG " +
s"ON EDP.TG_ID = TG.TG_ID WHERE TG.PUB_PRIV_FLAG = '01'AND VC.DATA_DATE >= TO_DATE('20200801', 'yyyymmdd') AND VC.DATA_DATE <= TO_DATE('20200831', 'yyyymmdd')" +
s"AND EDP.CONS_SORT NOT LIKE '06' AND VC.PHASE_FLAG IN (1, 2, 3) AND TG.ORG_NO LIKE '134010902' ORDER BY TG.ORG_NO, TG.TG_ID, EDP.CONS_SORT, VC.DATA_DATE, VC.PHASE_FLAG)"
推荐阅读
- function - 在类中调用反应本机函数
- javascript - 右键单击时在 div 菜单的标题中显示所选几何图形的名称
- ansible - Ansible 处理程序被赋予启动特定服务的任务。如果服务在被调用时已经启动,Ansible 处理程序会做什么?
- ffmpeg - 电子包装器的电子包装错误
- javascript - Javascript:优化 iFrame 的重新排序,如 DOM 节点
- android - 重启应用后Zxing条码扫描器不工作
- android - 带有 GridLayout 的 ScrollView 不会从顶部开始
- react-native - 在 React Native 中的 Icon 中创建一个包含名字和姓氏首字母的图标
- java - 为什么我需要指定数据类型?
- scala - 在scala spark中为两个不同的数据框创建视图