title: Union causing an ORA-01790: expression must have same datatype as corresponding expression
body: <p>With the following query as an example:</p>
  
  <pre><code>  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
  </code></pre>
  
  <p>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. </p>
  
  <p>Is it possible to cast product_type on the first query to be of type my_type?</p>
  
  <p>I tried changing the first query as shown below but with no luck. </p>
  
  <pre><code>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
  </code></pre>
  
  <h2>Edit</h2>
  
  <p>my_type is defined as <code>'TYPE "my_type" AS TABLE OF varchar2(4000)'</code></p>