SQL & PL/SQL

Hi

I use Database Version: Oracle 9.2.0.1.0
OCI version 9.2

I try to use order by decode while i have a select base on union and I get en error:

ORA-01785: ORDER BY item must be the number of a SELECT-list expression

Here is my code:

select catalog_type, catalog_id, item_id, item_name
from items_catalog
union all
select catalog_type, catalog_id, organisation_unit_id, organisation_unit_name
from units_catalog
order by decode(catalog_type, 1, item_id, organisation_unit_id)

If I use the decode on a select statement without union, it works.

What I have to do in order to make it work on union all select?

Tnx