Richard Richard - 3 months ago 9
SQL Question

SQL - Nested Select in From claus with Count

When I use the following code (it is from an Oracle guy and he says there is no reason for it not to work...) I am notified that there is incorrect syntax located at the last ')'. Any idea how I can change this to be "SQL Appropriate"? My thoughts are that it is not liking the last select statement.

select
*
from
CPINInvest
where
[Case ID||] not in
(
select [Case ID||]
from
(
select [Case ID||], count(*)
from CPINComm140
where [Role CD||]='PRI||'
group by [Case ID||]
having count(*)=1
)
)

Answer

It can be shortened.

select * from CPINInvest 
where [Case ID||] not in (
        select [Case ID||]
        from CPINComm140 
        where [Role CD||]='PRI||' 
        group by [Case ID||] 
        having count(*)=1
    );

But seriously, pipes in fieldnames? Yuk!

The reason why the original query would fail is:
1) the count(*) needs an alias. For example [total]
2) tsql has that odd requirement that some subqueries require an alias