DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Unable to use order by clause with MINUS operator

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577

    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.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  


Click Here to Expand Forum to Full Width