All 7 messages in topic - view as tree
From: Merlin Ran - view profile
Date: Wed, Dec 6 2006 5:29 pm
Email: "Merlin Ran"
I have following sql to run on an IDS 9.40:
select case
when serv_type_id in (select serv_type_id from s_serv_type where type_name
like "A%") then "Class A"
when serv_type_id in (select serv_type_id from s_serv_type where type_name
like "B%") then "Class B"
when serv_type_id in (1021,2021) then "Class C"
end type, count(*) users from ...
The result is curious:
type users
Class C 10
34250
After replace the subquery with the actual serv_type_ids queried by the
inner select statement , the result is as expected.
type users
Class A 1231
Class B 15690
Class C 10
//others
Does it mean that IDS 9.40 doesn't support subquery in the CASE expression?
Reply » Rate this post: Text for clearing space
From: bozon - view profile
Date: Wed, Dec 6 2006 8:28 pm
Email: "bozon"
I don't think that it does. 10 didn't work correctly with this query
either. I think it is trying to compare a list with integer so it
doesn't work.
I am not sure why you would want to do this that way the below seems
simpler and faster. I included the whole example including the tables I
created to test my work.
create table s_serv_type(
serv_type_id serial,
type_name varchar(20)
);
create table serv_instances(
serv_instances_id serial,
serv_type_id integer
) ;
insert into s_serv_type values (0, "A0");
insert into s_serv_type values (0, "A1");
insert into s_serv_type values (0, "B0");
insert into s_serv_type values (0, "B1");
insert into s_serv_type values (0, "C0");
insert into s_serv_type values (0, "C1");
insert into serv_instances values (0,1);
insert into serv_instances values (0,2);
insert into serv_instances values (0,2);
insert into serv_instances values (0,3);
insert into serv_instances values (0,3);
insert into serv_instances values (0,3);
insert into serv_instances values (0,4);
insert into serv_instances values (0,4);
insert into serv_instances values (0,4);
insert into serv_instances values (0,4);
insert into serv_instances values (0,5);
insert into serv_instances values (0,5);
insert into serv_instances values (0,5);
insert into serv_instances values (0,5);
insert into serv_instances values (0,5);
insert into serv_instances values (0,6);
insert into serv_instances values (0,6);
insert into serv_instances values (0,6);
insert into serv_instances values (0,6);
insert into serv_instances values (0,6);
insert into serv_instances values (0,6);
select
case
when st.type_name like "A%" then "CLASS A"
when st.type_name like "B%" then "CLASS B"
when st.type_name like "C%" then "CLASS C"
end type,
count(*)
from
serv_instances si,
s_serv_type st
where
si.serv_type_id = st.serv_type_id
group by
1
;
- Hide quoted text -
- Show quoted text -
Merlin Ran wrote:
> I have following sql to run on an IDS 9.40:
> select case
> when serv_type_id in (select serv_type_id from s_serv_type where type_name
> like "A%") then "Class A"
> when serv_type_id in (select serv_type_id from s_serv_type where type_name
> like "B%") then "Class B"
> when serv_type_id in (1021,2021) then "Class C"
> end type, count(*) users from ...
> The result is curious:
> type users
> Class C 10
> 34250
> After replace the subquery with the actual serv_type_ids queried by the
> inner select statement , the result is as expected.
> type users
> Class A 1231
> Class B 15690
> Class C 10
> //others
> Does it mean that IDS 9.40 doesn't support subquery in the CASE expression?
Reply » Rate this post: Text for clearing space
From: Art S. Kagel - view profile
Date: Wed, Dec 6 2006 11:40 pm
Email: "Art S. Kagel"
- Hide quoted text -
- Show quoted text -
Merlin Ran wrote:
> I have following sql to run on an IDS 9.40:
> select case
> when serv_type_id in (select serv_type_id from s_serv_type where type_name
> like "A%") then "Class A"
> when serv_type_id in (select serv_type_id from s_serv_type where type_name
> like "B%") then "Class B"
> when serv_type_id in (1021,2021) then "Class C"
> end type, count(*) users from ...
> The result is curious:
> type users
> Class C 10
> 34250
> After replace the subquery with the actual serv_type_ids queried by the
> inner select statement , the result is as expected.
> type users
> Class A 1231
> Class B 15690
> Class C 10
> //others
> Does it mean that IDS 9.40 doesn't support subquery in the CASE expression?
Don't know, but I would have used a UNION ALL for that anyway. Should be
faster:
select 'Class A' as type, count(*) as users
from
join s_serv_type lookup
on main.serv_type_id = lookup.serv_type_id and type_name like 'A%'
UNION ALL
select 'Class B' as type, count(*) as users
from
join s_serv_type lookup
on main.serv_type_id = lookup.serv_type_id and type_name like 'B%'
UNION ALL
select 'Class C' as type, count(*) as users
from
join s_serv_type lookup
on main.serv_type_id = lookup.serv_type_id and serv_type_id in (1021,2021)
GROUP BY type
ORDER BY type;
Art S. Kagel
Reply » Rate this post: Text for clearing space
From: bozon - view profile
Date: Thurs, Dec 7 2006 1:47 am
Email: "bozon"
Really faster than the single join with a case statement? I would have
to see it to believe it. :-)
- Hide quoted text -
- Show quoted text -
Art S. Kagel wrote:
> Merlin Ran wrote:
> > I have following sql to run on an IDS 9.40:
> > select case
> > when serv_type_id in (select serv_type_id from s_serv_type where type_name
> > like "A%") then "Class A"
> > when serv_type_id in (select serv_type_id from s_serv_type where type_name
> > like "B%") then "Class B"
> > when serv_type_id in (1021,2021) then "Class C"
> > end type, count(*) users from ...
> > The result is curious:
> > type users
> > Class C 10
> > 34250
> > After replace the subquery with the actual serv_type_ids queried by the
> > inner select statement , the result is as expected.
> > type users
> > Class A 1231
> > Class B 15690
> > Class C 10
> > //others
> > Does it mean that IDS 9.40 doesn't support subquery in the CASE expression?
> Don't know, but I would have used a UNION ALL for that anyway. Should be
> faster:
> select 'Class A' as type, count(*) as users
> from
> join s_serv_type lookup
> on main.serv_type_id = lookup.serv_type_id and type_name like 'A%'
> UNION ALL
> select 'Class B' as type, count(*) as users
> from
> join s_serv_type lookup
> on main.serv_type_id = lookup.serv_type_id and type_name like 'B%'
> UNION ALL
> select 'Class C' as type, count(*) as users
> from
> join s_serv_type lookup
> on main.serv_type_id = lookup.serv_type_id and serv_type_id in (1021,2021)
> GROUP BY type
> ORDER BY type;
> Art S. Kagel
Reply » Rate this post: Text for clearing space
From: Merlin Ran - view profile
Date: Thurs, Dec 7 2006 3:35 am
Email: "Merlin Ran"
Thank you! I just haven't think of this way. It is much simpler than
the workaround I use.
- Hide quoted text -
- Show quoted text -
"bozon wrote:"
> I don't think that it does. 10 didn't work correctly with this query
> either. I think it is trying to compare a list with integer so it
> doesn't work.
> I am not sure why you would want to do this that way the below seems
> simpler and faster. I included the whole example including the tables I
> created to test my work.
> create table s_serv_type(
> serv_type_id serial,
> type_name varchar(20)
> );
> create table serv_instances(
> serv_instances_id serial,
> serv_type_id integer
> ) ;
> insert into s_serv_type values (0, "A0");
> insert into s_serv_type values (0, "A1");
> insert into s_serv_type values (0, "B0");
> insert into s_serv_type values (0, "B1");
> insert into s_serv_type values (0, "C0");
> insert into s_serv_type values (0, "C1");
> insert into serv_instances values (0,1);
> insert into serv_instances values (0,2);
> insert into serv_instances values (0,2);
> insert into serv_instances values (0,3);
> insert into serv_instances values (0,3);
> insert into serv_instances values (0,3);
> insert into serv_instances values (0,4);
> insert into serv_instances values (0,4);
> insert into serv_instances values (0,4);
> insert into serv_instances values (0,4);
> insert into serv_instances values (0,5);
> insert into serv_instances values (0,5);
> insert into serv_instances values (0,5);
> insert into serv_instances values (0,5);
> insert into serv_instances values (0,5);
> insert into serv_instances values (0,6);
> insert into serv_instances values (0,6);
> insert into serv_instances values (0,6);
> insert into serv_instances values (0,6);
> insert into serv_instances values (0,6);
> insert into serv_instances values (0,6);
> select
> case
> when st.type_name like "A%" then "CLASS A"
> when st.type_name like "B%" then "CLASS B"
> when st.type_name like "C%" then "CLASS C"
> end type,
> count(*)
> from
> serv_instances si,
> s_serv_type st
> where
> si.serv_type_id = st.serv_type_id
> group by
> 1
> ;
> Merlin Ran wrote:
> > I have following sql to run on an IDS 9.40:
> > select case
> > when serv_type_id in (select serv_type_id from s_serv_type where type_name
> > like "A%") then "Class A"
> > when serv_type_id in (select serv_type_id from s_serv_type where type_name
> > like "B%") then "Class B"
> > when serv_type_id in (1021,2021) then "Class C"
> > end type, count(*) users from ...
> > The result is curious:
> > type users
> > Class C 10
> > 34250
> > After replace the subquery with the actual serv_type_ids queried by the
> > inner select statement , the result is as expected.
> > type users
> > Class A 1231
> > Class B 15690
> > Class C 10
> > //others
> > Does it mean that IDS 9.40 doesn't support subquery in the CASE expression?
Reply » Rate this post: Text for clearing space
From: bozon - view profile
Date: Thurs, Dec 7 2006 2:07 pm
Email: "bozon"
You are very welcome.
In general don't use nested subselects in the select statement as a
first pass. SQL didn't have them for years and we were able to write
almost ever query you could think of. So, if you come up with a nested
query in the select or the from there is almost certainly another way
to do it. And it is probably better.
- Hide quoted text -
- Show quoted text -
Merlin Ran wrote:
> Thank you! I just haven't think of this way. It is much simpler than
> the workaround I use.
> "bozon wrote:"
> > I don't think that it does. 10 didn't work correctly with this query
> > either. I think it is trying to compare a list with integer so it
> > doesn't work.
> > I am not sure why you would want to do this that way the below seems
> > simpler and faster. I included the whole example including the tables I
> > created to test my work.
> > create table s_serv_type(
> > serv_type_id serial,
> > type_name varchar(20)
> > );
> > create table serv_instances(
> > serv_instances_id serial,
> > serv_type_id integer
> > ) ;
> > insert into s_serv_type values (0, "A0");
> > insert into s_serv_type values (0, "A1");
> > insert into s_serv_type values (0, "B0");
> > insert into s_serv_type values (0, "B1");
> > insert into s_serv_type values (0, "C0");
> > insert into s_serv_type values (0, "C1");
> > insert into serv_instances values (0,1);
> > insert into serv_instances values (0,2);
> > insert into serv_instances values (0,2);
> > insert into serv_instances values (0,3);
> > insert into serv_instances values (0,3);
> > insert into serv_instances values (0,3);
> > insert into serv_instances values (0,4);
> > insert into serv_instances values (0,4);
> > insert into serv_instances values (0,4);
> > insert into serv_instances values (0,4);
> > insert into serv_instances values (0,5);
> > insert into serv_instances values (0,5);
> > insert into serv_instances values (0,5);
> > insert into serv_instances values (0,5);
> > insert into serv_instances values (0,5);
> > insert into serv_instances values (0,6);
> > insert into serv_instances values (0,6);
> > insert into serv_instances values (0,6);
> > insert into serv_instances values (0,6);
> > insert into serv_instances values (0,6);
> > insert into serv_instances values (0,6);
> > select
> > case
> > when st.type_name like "A%" then "CLASS A"
> > when st.type_name like "B%" then "CLASS B"
> > when st.type_name like "C%" then "CLASS C"
> > end type,
> > count(*)
> > from
> > serv_instances si,
> > s_serv_type st
> > where
> > si.serv_type_id = st.serv_type_id
> > group by
> > 1
> > ;
> > Merlin Ran wrote:
> > > I have following sql to run on an IDS 9.40:
> > > select case
> > > when serv_type_id in (select serv_type_id from s_serv_type where type_name
> > > like "A%") then "Class A"
> > > when serv_type_id in (select serv_type_id from s_serv_type where type_name
> > > like "B%") then "Class B"
> > > when serv_type_id in (1021,2021) then "Class C"
> > > end type, count(*) users from ...
> > > The result is curious:
> > > type users
> > > Class C 10
> > > 34250
> > > After replace the subquery with the actual serv_type_ids queried by the
> > > inner select statement , the result is as expected.
> > > type users
> > > Class A 1231
> > > Class B 15690
> > > Class C 10
> > > //others
> > > Does it mean that IDS 9.40 doesn't support subquery in the CASE expression?
Reply » Rate this post: Text for clearing space
From: Scott Mackenzie - view profile
Date: Thurs, Dec 7 2006 5:40 pm
Email: "Scott Mackenzie"
Unless you are an Orrible SQLer.
- Scott
- Hide quoted text -
- Show quoted text -
-----Original Message-----
From: informix-list-boun...@iiug.org
[mailto:informix-list-boun...@iiug.org] On Behalf Of bozon
Sent: Thursday, December 07, 2006 5:07 AM
To: informix-l...@iiug.org
Subject: Re: subquery in case expression
You are very welcome.
In general don't use nested subselects in the select statement as a
first pass. SQL didn't have them for years and we were able to write
almost ever query you could think of. So, if you come up with a nested
query in the select or the from there is almost certainly another way
to do it. And it is probably better.
Merlin Ran wrote:
> Thank you! I just haven't think of this way. It is much simpler than
> the workaround I use.
> "bozon wrote:"
> > I don't think that it does. 10 didn't work correctly with this query
> > either. I think it is trying to compare a list with integer so it
> > doesn't work.
> > I am not sure why you would want to do this that way the below seems
> > simpler and faster. I included the whole example including the
tables I
> > created to test my work.
> > create table s_serv_type(
> > serv_type_id serial,
> > type_name varchar(20)
> > );
> > create table serv_instances(
> > serv_instances_id serial,
> > serv_type_id integer
> > ) ;
> > insert into s_serv_type values (0, "A0");
> > insert into s_serv_type values (0, "A1");
> > insert into s_serv_type values (0, "B0");
> > insert into s_serv_type values (0, "B1");
> > insert into s_serv_type values (0, "C0");
> > insert into s_serv_type values (0, "C1");
> > insert into serv_instances values (0,1);
> > insert into serv_instances values (0,2);
> > insert into serv_instances values (0,2);
> > insert into serv_instances values (0,3);
> > insert into serv_instances values (0,3);
> > insert into serv_instances values (0,3);
> > insert into serv_instances values (0,4);
> > insert into serv_instances values (0,4);
> > insert into serv_instances values (0,4);
> > insert into serv_instances values (0,4);
> > insert into serv_instances values (0,5);
> > insert into serv_instances values (0,5);
> > insert into serv_instances values (0,5);
> > insert into serv_instances values (0,5);
> > insert into serv_instances values (0,5);
> > insert into serv_instances values (0,6);
> > insert into serv_instances values (0,6);
> > insert into serv_instances values (0,6);
> > insert into serv_instances values (0,6);
> > insert into serv_instances values (0,6);
> > insert into serv_instances values (0,6);
> > select
> > case
> > when st.type_name like "A%" then "CLASS A"
> > when st.type_name like "B%" then "CLASS B"
> > when st.type_name like "C%" then "CLASS C"
> > end type,
> > count(*)
> > from
> > serv_instances si,
> > s_serv_type st
> > where
> > si.serv_type_id = st.serv_type_id
> > group by
> > 1
> > ;
> > Merlin Ran wrote:
> > > I have following sql to run on an IDS 9.40:
> > > select case
> > > when serv_type_id in (select serv_type_id from s_serv_type where
type_name
> > > like "A%") then "Class A"
> > > when serv_type_id in (select serv_type_id from s_serv_type where
type_name
> > > like "B%") then "Class B"
> > > when serv_type_id in (1021,2021) then "Class C"
> > > end type, count(*) users from ...
> > > The result is curious:
> > > type users
> > > Class C 10
> > > 34250
> > > After replace the subquery with the actual serv_type_ids queried
by the
> > > inner select statement , the result is as expected.
> > > type users
> > > Class A 1231
> > > Class B 15690
> > > Class C 10
> > > //others
> > > Does it mean that IDS 9.40 doesn't support subquery in the CASE
expression?
_______________________________________________
Informix-list mailing list
Informix-l...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Reply » Rate this post: Text for clearing space
End of messages
No comments:
Post a Comment