Re: Multiple selects

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 5 Sep 2003 16:45:53 GMT
Message-ID: <bjaek1$gb9tu$1@ID-82536.news.uni-berlin.de>

> Hi there,
>
> I'm new to the world of Oracle, so maybe someone out there can offer
> some suggestions here:
>
> I currently execute 3 SQL statement like this in my Java class:
> ------------------------------------
> SELECT count(*) as count FROM myTable
> where this="that" and that = "the other";
>
> SELECT count(*) as count FROM myTable
> where this="this" and that = "that";
>
> SELECT count(*) as count FROM myTable
> where this="confusing";
> ------------------------------------
>
> I am wondering if it's possible to execute these 3 SQL statement with
> one query and get a resultSet of e.g. 3,3,3?
>
> Any snippet, or URL would be helpful!!!
>
> Thanks in advance,
>
> Leigh
>
> PS: http://www.google.com/search?q=oracle+multiple+select didn't help much!
 

create table myTable (
  this Varchar2(10),
  that Varchar2(10)
);

insert into myTable values ('that'     , 'the other');
insert into myTable values ('confusing', 'the other');
insert into myTable values ('this'     , 'that'     );
insert into myTable values ('this'     , 'that'     );
insert into myTable values ('that'     , 'confusing');
insert into myTable values ('confusing', 'that'     );
insert into myTable values ('confusing', 'confusing');
insert into myTable values ('that'     , 'the other');
insert into myTable values ('this'     , 'that'     );
insert into myTable values ('confusing', 'the other');

select
  count(
    case when

      this = 'that' and 
      that = 'the other'

    then 1
    else null end) "Count 1",
  count(
    case when

      this = 'this' and 
      that = 'that'

    then 1
    else null end) "Count 2",
  count(
    case when
      this = 'confusing'
    then 1
    else null end) "Count 3"
from
  myTable;                      

hth,
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Fri Sep 05 2003 - 11:45:53 CDT