-
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
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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);
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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)
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|