community.borland.com

Article #25201: Merging heterogeneous columns in a UNION Select

Problem:
I am trying to use a select statement with a UNION clause.  A field from one table has no counterpart
 in the second table and I wish for it to be blank for all rows from the second table.  When I try to
 execute the select statement, I get this error: 'Dynamic SQL Error -SQL error code = -104 -Invalid
 command  -Data type unknown' .   What's the correct syntax?

Solution:
Given the following schema:

Create table Employee (First_name Char(15), Last_name Char(25), Title Char(30));
Create table Friends( First_name Char(15), Last_name Char(25), Birth_date Date);

and trying to perform this query:

Select First_name, Last_name, Title from Employee
UNION
Select First_name, Last_name, '  ' as Title from Friends

gives this error:

Dynamic SQL Error
-SQL error code = -104
-Invalid command
-Data type unknown

The solution is to cast the values so that the datatypes in both sub-selects match.  
Here's the corrected version of the query:

Select First_name, Last_name, Title from Employee
UNION
Select First_name, Last_name, Cast('  ' as Char(30)) as Title from Friends

Last Modified: 29-SEP-00