导致ORA-01790：表达式的联合必须具有与对应表达式相同的数据类型 [英] Union causing an ORA-01790: expression must have same datatype as corresponding expression

问题描述

以下列查询为例：

  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

第一个查询中的select语句返回product_type作为varchar，第二个查询的product_type的类型为my_type。
导致和ORA-01790：表达式必须具有与对应表达式相同的数据类型，因为数据类型不同。

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.

可以将第一个查询的product_type类型my_type？

Is it possible to cast product_type on the first query to be of 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

编辑

my_type定义为'TYPEmy_typeAS TABLE OF varchar2（4000）'

推荐答案

我想你不能在SQL中做这样的转换。但是在PL / SQL中你可以：

I think you cannot do such casting in SQL. But in PL/SQL you can:

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;
/

我认为我错在上面的假设。我没有删除它，因为它仍然是有效的例子。下面的示例使用COLLECT作为table_type的类型来转换现有表列（emp表）：

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:

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
 /

这篇关于导致ORA-01790：表达式的联合必须具有与对应表达式相同的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！