Unable to use order by clause with MINUS operator

[ronnie]

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

[jmodic]

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);

[ronnie]

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

[jmodic]

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)