subquery in case expression
A user on IDS 9.40 found that a CASE expression using "serv_type_id IN (SELECT ...)" in the WHEN clauses returned wrong results (rows falling through to NULL), while hard-coding the IDs worked. Another poster confirmed the same misbehaviour on IDS 10, suggesting the engine mishandles the list-vs-value comparison there. No fix/patch was identified, but two rewrites were offered: join the lookup table and apply CASE on type_name LIKE 'A%'/'B%' with GROUP BY, or use UNION ALL of per-class counts. The original poster adopted the join/CASE version.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have following sql to run on an IDS 9.40:
select casewhen 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?
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?
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 <whatevertable> as main
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 <whatevertable> as main
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 <whatevertable> as main
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
Really faster than the single join with a case statement? I would have
to see it to believe it. :-)
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 <whatevertable> as main
> 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 <whatevertable> as main
> 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 <whatevertable> as main
> 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
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?
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?