Vanilla SQL with correlated subquery works in T-SQL, fails in PL/SQL

Question

I would like to use essentially the same query in both T-SQL (SQL Server 2000/2005) and PL/SQL (Oracle 10g). Though it is not trivial, it is reasonably vanilla code, but it works in SQL Server yet fails in Oracle. My goal is to generate a count of rows for each unique upper/lower case combination of a particular field, omitting any field values where there is only one such combination. This sample output, for example, indicates that the token "two words" appears in four different combinations of upper/lower case, with the most prevalent use being the all-uppercase version (121 such rows).

  alias      rows affected
  phrase           25
  Phrase            3
  Two words        12
  Two Words         9
  TWO words         3
  TWO WORDS       121

As a reminder, SQL Server is case insensitive by default so the COLLATE clause lets me work with it case-sensitively. Oracle, on the other hand, needs no massaging since it is case-sensitive.

This query works fine in T-SQL:

select
    description COLLATE SQL_Latin1_General_CP1_CS_AS as alias,
    count(*) as "Rows Affected"
from dbo.svcs t1 (nolock) 
where (
    select count(upper(alias)) as "Variation Count"
    from
    ( -- list of unique spellings for each alias
        select
            description COLLATE SQL_Latin1_General_CP1_CS_AS as alias,
            count(*) as count
        from dbo.svcs (nolock)
        where description = t1.description
        group by description COLLATE SQL_Latin1_General_CP1_CS_AS 
    ) combos
    group by upper(alias)
    having count(upper(alias)) > 1
) > 1
group by description COLLATE SQL_Latin1_General_CP1_CS_AS
order by description COLLATE SQL_Latin1_General_CP1_CS_AS

This query fails in Oracle; it does not recognize the outer reference, complaining it uses an "invalid identifier":

select alias, count(*) as "Rows Affected"
from dev1.svcs t1
where (
    select count(upper(alias)) as "Variation Count"
    from
    ( -- list of unique spellings for each alias
        select alias, count(*) as count
        from dev1.svcs
        where upper(alias) = upper(t1.alias)     -- <<<<< Does not like outer reference to 't1.alias' here
        group by alias
    ) combos
    group by upper(alias)
    having count(upper(alias)) > 1
) > 1
group by alias
order by alias

So is there a workaround for Oracle PL/SQL?

2010.02.16 Update

After testing on my full data set, I accepted Gary's answer. He eliminated the correlated subquery to get a more compact solution, which is great, but I am still curious why the correlated subquery attempt fails, if anyone happens to have thoughts on that...

Answer 1

I think you may get away with

select alias, upper(alias), count(*) cnt_alias
from svcs m
where upper(alias) in
    (select upper(alias) up_a
    from svcs
    group by upper(alias)
    having count(distinct alias) > 1) 
group by alias
order by upper(alias), count(*) desc, alias;

Tested with:

create table svcs (id number, alias varchar2(10));

insert into svcs select level, 'phrase' from dual connect by level <= 25;
insert into svcs select level, 'Phrase' from dual connect by level <= 3;
insert into svcs select level, 'Two words' from dual connect by level <= 12;
insert into svcs select level, 'Two Words' from dual connect by level <= 9;
insert into svcs select level, 'TWO words' from dual connect by level <= 3;
insert into svcs select level, 'TWO WORDS' from dual connect by level <= 121;
insert into svcs select level, 'Only' from dual connect by level <= 121;