RE: subquery in case expression
Unless you are an Orrible SQLer.
- Scott
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org] On Behalf Of bozon
Sent: Thursday, December 07, 2006 5:07 AM
To: informix-list@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-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list