ORA-01790:表达式必须具有与UNPIVOT查询中相应表达式相同的数据类型
ORA-01790:表达式必须具有与UNPIVOT查询中相应表达式相同的数据类型
提问于 2021-12-09 15:47:51
我有一个不枢轴查询，当从一个表中提取时，这个查询工作得很好：
WITH test
(SELECT field1,field2,field3,field4
FROM Table1
WHERE id = 1234)
SELECT * FROM test
UNPIVOT
(Value FOR Field IN (field1 as 'Field 1', field2 as 'Field 2', field3 as 'Field 3', field4 as 'Field 4'))将数据显示为：
Field 1 Value1
Field 2 Value2
Field 3 Value3
Field 4 Value4既然我已经将查询更改为
WITH test
(SELECT t.field1,t.field2,t.field3,t.field4, x.qty1, x.qty2, (x.qty1 + x.qty2) qty3
FROM Table1 t
,(select id, field1, Function1(field1, field3) qty1, Function2(field1, field3) qty2 FROM Table1) x
WHERE t.id = x.id
AND id = 1234)
SELECT * FROM test
UNPIVOT
(Value FOR Field IN (field1 as 'Field 1',
field2 as 'Field 2',
field3 as 'Field 3',
field4 as 'Field 4', <-- ORA-01790: expression must have same datatype as corresponding expression
qty1 as 'Quantity 1',
qty2 as 'Quantity 2',
qty3 as 'Total'))不知道为什么会发生这种事，我怎样才能解决它？
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-12-09 16:19:22
您的field1到field4值是(或似乎是)字符串，而qty1到qty3值是数字。正如错误消息所述，它们都需要相同的数据类型。
您可能会感到困惑，消息似乎与第一个查询中的一个原始列field4有关，而不是一个新列。这是因为语句通常是向后解析的，所以解析器首先看到数值列，然后抱怨第一个非数字列。
无论如何，您只需要在内部查询中将数字转换为字符串，例如：
WITH test
(SELECT t.field1,t.field2,t.field3,t.field4,
to_char(x.qty1) as qty1, to_char(x.qty2) as qty2, to_char(x.qty1 + x.qty2) as qty3
...或全部：
WITH test AS
(SELECT t.field1,t.field2,t.field3,t.field4,
to_char(x.qty1) as qty1, to_char(x.qty2) as qty2, to_char(x.qty1 + x.qty2) as qty3
FROM Table1 t
,(select id, field1, Function1(field1, field3) qty1, Function2(field1, field3) qty2 FROM Table1) x
WHERE t.id = x.id
AND id = 1234)
SELECT * FROM test
UNPIVOT
(Value FOR Field IN (field1 as 'Field 1',
field2 as 'Field 2',
field3 as 'Field 3',
field4 as 'Field 4',
qty1 as 'Quantity 1',
qty2 as 'Quantity 2',
qty3 as 'Total'))db<>fiddle demo使用来自dual的虚拟值，而不是真实的表。
当然，您可以在这些to_char()调用中应用显式格式--我只是简单地演示了这个问题。
您可能会遇到其他错误--例如，AND id = 1234将是模棱两可的--并且最好使用现代的联接语法。不过，我不知道您为什么要加入--您可以将内部查询简化为：
WITH test AS (
SELECT field1, field2, field3, field4,
to_char(qty1) as qty1, to_char(qty2) as qty2, to_char(qty1 + qty2) as qty3
FROM (
SELECT field1, field2, field3, field_4,
Function1(field1, field3) as qty1, Function2(field1, field3) as qty2
FROM Table1
WHERE id = 1234
)
)添加了嵌套查询版本的db<>fiddle。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/70292765
复制相关文章
相似问题
腾讯云开发者
