sql - 尝试在 Oracle SQL 中取消透视列时出错
问题描述
我通过每个表中的同一列(称为 fema_wo)将多个表连接在一起。我想将所有选定的列放在一个列中,然后有另一列说明它来自哪个表(基本上与 R 的pivot_longer()相同)。No more data to read from socket
Vendor Code 17410 Error at line: 1
当我尝试运行它时出现错误;运行版本CORE 11.2.0.4.0 Production
的 Oracle
任何人都知道为什么会发生这种情况以及可能的修复/建议?
请参阅此处的代码:
select * from (
select dpd.objectid as id1, ss.objectid as id2, ohp.objectid as id3 , ohs.objectid as id4, ugp.objectid as id5, ugs.objectid as id6,
s.objectid as id7, t.objectid as id8
from mvdynamicprotectivedevice dpd
FULL OUTER JOIN mvsupportstructure ss ON (dpd.fema_wo = ss.fema_wo)
FULL OUTER JOIN mvoverheadprimaryline ohp ON (dpd.fema_wo = ohp.fema_wo)
FULL OUTER JOIN overheadsecondaryline ohs ON (dpd.fema_wo = ohs.fema_wo)
FULL OUTER JOIN mvundergroundprimaryline ugp ON (dpd.fema_wo = ugp.fema_wo)
FULL OUTER JOIN undergroundsecondaryline ugs ON (dpd.fema_wo = ugs.fema_wo)
FULL OUTER JOIN mvswitch s ON (dpd.fema_wo = s.fema_wo)
FULL OUTER JOIN mvtransformer t ON (dpd.fema_wo = t.fema_wo)
WHERE dpd.fema_wo = 'WBS.F.81109.1.1.2'
)
unpivot (
objectid
for objectid
in (id1, id2, id3, id4, id5, id6, id7, id8)
);
解决方案
UNION ALL
看起来更简单:
select dpd.objectid as id,
from mvdynamicprotectivedevice dpd
where dpd.fema_wo = 'WBS.F.81109.1.1.2'
union all
select ss.objectid
from mvsupportstructure ss join
mvdynamicprotectivedevice dpd
on dpd.fema_wo = ss.fema_wo and dpd.fema_wo = 'WBS.F.81109.1.1.2'
union all
select ohp.objectid
from mvoverheadprimaryline ohp join
mvdynamicprotectivedevice dpd
on dpd.fema_wo = ohp.fema_wo and dpd.fema_wo = 'WBS.F.81109.1.1.2'
. . . ;
我的猜测是,由于每个表中有多个匹配项,您的外连接大大增加了行数。
推荐阅读
- ios - 创建 Mac 版本的 iOS 应用程序,构建错误
- javascript - 为什么 GET ajax 调用返回错误代码 500?
- r - ggplot2 box_plot 添加观察次数
- google-maps - 如何在 Google 地图中的标记点击上打开地图叠加层
- python - 如何模拟 psycopg2 连接方法?
- aws-iot - AWS IoT STM32L4 卡在写证书上
- python - 无法检查使用导入模块创建的 Spyder 中的对象
- c# - 使用 Entity Framework Core 获取数据时填充嵌套子集合
- javascript - React Hooks:无法识别汉堡菜单onClick的useCallback返回值
- mysql - 删除 MySQL 字段中的值