Unable to use order by clause with MINUS operator

Hi,

I am facing a wierd situation here.

I have a stored procedure with a cursor defined as

cursor AllGlobalCursor is select region_name,region_id from region where region_id <> p_selectedRegionId and rank is null and region_type = 'Global'
MINUS
select a.region_name,a.region_id from region a, regionhierarchy b
where b.region_child_id=a.region_id and b.region_parent_id = p_selectedRegionId and a.rank is null and region_type = 'Global'
order by region_name;

The procedure is compiling perfectly and is working.

but when i change the cursor to

cursor AllGlobalCursor is select region_name,region_id from region where region_id <> p_selectedRegionId and rank is null and region_type = 'Global'
MINUS
select a.region_name,a.region_id from region a, regionhierarchy b
where b.region_child_id=a.region_id and b.region_parent_id = p_selectedRegionId and a.rank is null and region_type = 'Global'
order by upper(region_name);

and try to compile the procedure it fails with the error

SQL> @C:\Companydb\codb_linkregions

Package created.

No errors.

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY CODB_LINKREGIONS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
725/27 PL/SQL: SQL Statement ignored
731/17 PLS-00201: identifier 'REGION_NAME' must be declared

For the sake of simplicity of this post I have mentioned the UPPER function here. What I am trying is decode so that I can display certain records first in the order by clause.

Why is it failing with upper.

Thanks
Ron

With MINUS, UNION and INTERSECT you can only use order by clause in two ways:

- by positional notation, like ".... ORDER BY 1"
- by listing the column name or an alias from the selected column list. So if you use UPPER(region_name) in ORDER BY, then you must also include this expression in the select list, assign it an alias and use that alias in your ORDER BY, eg:

select region_name,region_id, UPPER(region_name) upper_name
from region
where region_id <> p_selectedRegionId
and rank is null
and region_type = 'Global'
MINUS
select a.region_name,a.region_id, UPPER(region_name) upper_name
from region a, regionhierarchy b
where b.region_child_id=a.region_id
and b.region_parent_id = p_selectedRegionId
and a.rank is null
and region_type = 'Global'
order by upper_name;

If you don't want that UPPER() expression in your select list, you can change your cursor to something like:

SELECT region_name,region_id FROM
(
select region_name,region_id
from region
where region_id <> p_selectedRegionId
and rank is null
and region_type = 'Global'
MINUS
select a.region_name,a.region_id
from region a, regionhierarchy b
where b.region_child_id=a.region_id
and b.region_parent_id = p_selectedRegionId
and a.rank is null
and region_type = 'Global'
)
order by upper(region_name);

Quote:

---

Originally posted by jmodic

If you don't want that UPPER() expression in your select list, you can change your cursor to something like:

SELECT region_name,region_id FROM
(
select region_name,region_id
from region
where region_id <> p_selectedRegionId
and rank is null
and region_type = 'Global'
MINUS
select a.region_name,a.region_id
from region a, regionhierarchy b
where b.region_child_id=a.region_id
and b.region_parent_id = p_selectedRegionId
and a.rank is null
and region_type = 'Global'
)
order by upper(region_name);

---

Hi Jurij,

I tried one of your solutions and it works fine with upper but when i use it with decode it gives me a Invalid Number error.


1 SELECT region_name,region_id FROM
2 (
3 select region_name,region_id
4 from region
5 where region_id <> 55615
6 and rank is null
7 and region_type = 'Global'
8 MINUS
9 select a.region_name,a.region_id
10 from region a, regionhierarchy b
11 where b.region_child_id=a.region_id
12 and b.region_parent_id = 55615
13 and a.rank is null
14 and region_type = 'Global'
15 )
16* order by decode(region_name,'Worldwide ',1,region_name)
SQL> /
SELECT region_name,region_id FROM
*
ERROR at line 1:
ORA-01722: invalid number

Why is that

Thanks

Because your first result argument (1) in DECODE is NUMBER, so it tries to convert any subsequent result arguments to that type. And obviously the values of your REGION_NAME column can not be converted to NUMBERS. So change your decode to:

order by decode(region_name,'Worldwide ','1',region_name)