oracle 联合导致 ORA-01790: 表达式必须与相应的表达式具有相同的数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14879364/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Union causing an ORA-01790: expression must have same datatype as corresponding expression
提问by ziggy
With the following query as an example:
以以下查询为例：
select p.product_id, p.product_name,
product_type as product_type,
from products
group by p.product_id, p.product_name
union
select p.product_id, p.product_name,
cast(collect(coalesce(product_type, decode(product_description,null,'DESCR' || '-' product_description) as my_type) as product_type,
from products
group by p.product_id, p.product_name
The select statement in the first query returns product_type as a varchar and on the second query product_type is of type my_type. This is causing and ORA-01790: expression must have same datatype as corresponding expression because the data types are not the same.
第一个查询中的 select 语句将 product_type 作为 varchar 返回，而在第二个查询中 product_type 的类型为 my_type。这导致 ORA-01790：表达式必须与相应的表达式具有相同的数据类型，因为数据类型不相同。
Is it possible to cast product_type on the first query to be of type my_type?
是否可以在第一个查询中将 product_type 转换为 my_type 类型？
I tried changing the first query as shown below but with no luck.
我尝试更改第一个查询，如下所示，但没有运气。
select p.product_id, p.product_name,
cast(product_type as my_type) as product_type,
decode(product_source_location, null, 'NO_SOURCE', product_source_location)
from products
group by p.product_id, p.product_name
Edit
编辑
my_type is defined as 'TYPE "my_type" AS TABLE OF varchar2(4000)'
my_type 定义为 'TYPE "my_type" AS TABLE OF varchar2(4000)'
采纳答案by Art
I think you cannot do such casting in SQL. But in PL/SQL you can:
我认为你不能在 SQL 中进行这样的转换。但是在 PL/SQL 中，您可以：
CREATE OR REPLACE TYPE STRARRAY AS TABLE OF VARCHAR2 (255)
/
DECLARE
tab STRARRAY;
cnt NUMBER:= 0;
BEGIN
SELECT COUNT(*)
INTO cnt
FROM TABLE(CAST(tab AS strarray));
dbms_output.put_line(cnt);
END;
/
I think I was wrong in my assumptions above. I did not delete that as it is still valid example. Below example casting existing table column (emp table) with COLLECT as type of table_type:
我认为我上面的假设是错误的。我没有删除它，因为它仍然是有效的例子。下面的示例使用 COLLECT 作为 table_type 类型转换现有表列（emp 表）：
CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
/
SELECT deptno
, CAST(COLLECT(ename) AS varchar2_ntt) AS emps
FROM scott.emp
GROUP BY deptno
/
-- This is dumb but works:
SELECT deptno
, CAST(COLLECT(ename) AS varchar2_ntt) AS emps
FROM scott.emp
GROUP BY deptno
UNION ALL
SELECT deptno
, CAST(COLLECT(ename) AS varchar2_ntt) AS emps
FROM scott.emp
GROUP BY deptno
/
