Chuck Belanger wrote:

> Hi, all:
>
> Is there any way to do a UNION and use the string operator || ?
>
> I found another way to do this. Its just to fill a combobox, but still
> wondering why the problem.
>
> Each half of the UNION works fine on its own.
>
> I keep getting
> SQL error code -104
> Invalid command
> Data type unknown
>
> for the following (its the field with || in it)
>
> select v1.ml_item || '/' || v2.ml_item as Root_Node ,
> v1.ml_id as id,
> v2.ml_parentid ,
> v1.root_id
> from masterlibrary v1, masterlibrary v2
> where v1.root_id = v2.ml_parentid
> and v1.ml_parentid = -1
> UNION
> select v3.ml_item || '/' as Root_Node ,
> v3.ml_id as id,
> v3.ml_parentid ,
> v3.root_id
> from masterlibrary v3
> where v3.ml_id = v3.root_id
> order by 1

Try to cast both expessions to the same type.

select cast(v1.ml_item || '/' || v2.ml_item as varchar(xxx)) Root_Node,
...
union
...
select cast(v3.ml_item || '/' as varchar(xxx)) Root_Node,
...

- maybe you need some parentheseses(!) around the expressions.


--
Aage J.