Oninit cdi / Archive
Talk to an expert →

Craziness with views

Posted in 2007

Topics: SQL Development & Query Writing

Chris Salch — — source: IIUG Forums & Mailing Lists

Ok, HPUX 11iv1 Informix 9.40.HC5 I came across some thing utterly nuts: create table data_a ( id integer, vali integer); create table data_b ( id integer, val char(1)); -- data insert into data_a (id,vali) values (1,1); insert into data_a (id,vali) values (2,1); insert into data_a (id,vali) values (3,1); insert into data_a (id,vali) values (4,1); insert into data_a (id,vali) values (5,1); insert into data_a (id,vali) values (2,2); insert into data_a (id,vali) values (3,2); insert into data_a (id,vali) values (4,2); insert into data_a (id,vali) values (5,2); insert into data_b (id,val) values (1,'C'); insert into data_b (id,val) values (2,'C'); insert into data_b (id,val) values (3,'C'); insert into data_b (id,val) values (4,'D'); insert into data_b (id,val) values (5,'D'); insert into data_b (id,val) values (1,'C'); insert into data_b (id,val) values (2,'C'); insert into data_b (id,val) values (3,'C'); insert into data_b (id,val) values (4,'D'); insert into data_b (id,val) values (5,'D'); insert into data_b (id,val) values (1,'C'); insert into data_b (id,val) values (2,'C'); insert into data_b (id,val) values (3,'C'); insert into data_b (id,val) values (4,'D'); insert into data_b (id,val) values (5,'D'); create view view_a ( id, vali) asSELECT data_a.id,data_a.vali FROM data_a join data_a sec on sec.id=data_a.id group by 1,2 having data_a.vali=max(sec.vali) ; create view view_b ( id, bucket) asSELECT view_a.id, case when (SELECT count(*)=0 FROM data_b WHERE data_b.id=view_a.id and data_b.val='C') then 1 when (SELECT count(*)=0 FROM data_b WHERE data_b.id=view_a.id and data_b.val='D') then 2 else 3 end as bucket FROM view_a ; SELECT * FROM view_b WHERE view_b.bucket=2 ; >From that you get the results : id bucket ----- --------- 1 1 2 1 3 1 Similarly if you use this: create table data_a ( id integer); create table data_b ( id integer, val char(1)); -- data insert into data_a (id) values (1); insert into data_a (id) values (2); insert into data_a (id) values (3); insert into data_a (id) values (4); insert into data_a (id) values (5); insert into data_a (id) values (2); insert into data_a (id) values (3); insert into data_a (id) values (4); insert into data_a (id) values (5); insert into data_b (id,val) values (1,'C'); insert into data_b (id,val) values (2,'C'); insert into data_b (id,val) values (3,'C'); insert into data_b (id,val) values (4,'D'); insert into data_b (id,val) values (5,'D'); insert into data_b (id,val) values (1,'C'); insert into data_b (id,val) values (2,'C'); insert into data_b (id,val) values (3,'C'); insert into data_b (id,val) values (4,'D'); insert into data_b (id,val) values (5,'D'); insert into data_b (id,val) values (1,'C'); insert into data_b (id,val) values (2,'C'); insert into data_b (id,val) values (3,'C'); insert into data_b (id,val) values (4,'D'); insert into data_b (id,val) values (5,'D'); create view view_a ( id ) as SELECT distinct id FROM data_a ; create view view_b ( id, bucket) asSELECT view_a.id, case when (SELECT count(*)=0 FROM data_b WHERE data_b.id=view_a.id and data_b.val='C') then 1 when (SELECT count(*)=0 FROM data_b WHERE data_b.id=view_a.id and data_b.val='D') then 2 else 3 end as bucket FROM view_a ; SELECT * FROM view_b WHERE view_b.bucket=2 ; You get this: id bucket ----- --------- 1 1 2 1 3 1 Anybody have any ideas? -- -------------------------------------------- Chris Salch

↪ replying to Chris Salch
Art S. Kagel (Oninit LLC) — — source: IIUG Forums & Mailing Lists

Chris Salch wrote: I strongly suspect that your CASE statements in the 'bucket' calculated columns is faulty. I have no idea how ANY database engine would interpret 'SELECT count(*)=0 ...' in the way you intend. IB that it is ALWAYS returning '1'. Try it this way: case when ((SELECT count(*) FROM data_b WHERE data_b.id=view_a.id and data_b.val='C') = 0) then 1 when ((SELECT count(*) FROM data_b WHERE data_b.id=view_a.id and data_b.val='D') = 0) then 2 else 3 end as bucket Art S. Kagel > Ok, > HPUX 11iv1 Informix 9.40.HC5 > > I came across some thing utterly nuts: > > create table data_a ( id integer, vali integer); > create table data_b ( id integer, val char(1));> > -- data > insert into data_a (id,vali) values (1,1); > insert into data_a (id,vali) values (2,1); > insert into data_a (id,vali) values (3,1); > insert into data_a (id,vali) values (4,1); > insert into data_a (id,vali) values (5,1); > insert into data_a (id,vali) values (2,2); > insert into data_a (id,vali) values (3,2); > insert into data_a (id,vali) values (4,2); > insert into data_a (id,vali) values (5,2);> > insert into data_b (id,val) values (1,'C'); > insert into data_b (id,val) values (2,'C'); > insert into data_b (id,val) values (3,'C'); > insert into data_b (id,val) values (4,'D'); > insert into data_b (id,val) values (5,'D'); > insert into data_b (id,val) values (1,'C'); > insert into data_b (id,val) values (2,'C'); > insert into data_b (id,val) values (3,'C'); > insert into data_b (id,val) values (4,'D'); > insert into data_b (id,val) values (5,'D'); > insert into data_b (id,val) values (1,'C'); > insert into data_b (id,val) values (2,'C'); > insert into data_b (id,val) values (3,'C'); > insert into data_b (id,val) values (4,'D'); > insert into data_b (id,val) values (5,'D');> > create view view_a ( id, vali) as> SELECT > > data_a.id,data_a.vali > FROM > > data_a join data_a sec on sec.id=data_a.id > group by 1,2 > having data_a.vali=max(sec.vali) > ; > > create view view_b ( id, bucket) as> SELECT > > view_a.id, > > case > > when (SELECT count(*)=0 FROM data_b WHERE data_b.id=view_a.id > and data_b.val='C') then 1 > > when (SELECT count(*)=0 FROM data_b WHERE data_b.id=view_a.id > and data_b.val='D') then 2 > > else 3 > > end as bucket > FROM > > view_a > ; > > SELECT > > * > FROM > > view_b > WHERE > > view_b.bucket=2 > ; > > >From that you get the results : > > id bucket > ----- --------- > 1 1 > 2 1 > 3 1 > > Similarly if you use this: > > create table data_a ( id integer); > create table data_b ( id integer, val char(1));> > -- data > insert into data_a (id) values (1); > insert into data_a (id) values (2); > insert into data_a (id) values (3); > insert into data_a (id) values (4); > insert into data_a (id) values (5); > insert into data_a (id) values (2); > insert into data_a (id) values (3); > insert into data_a (id) values (4); > insert into data_a (id) values (5);> > insert into data_b (id,val) values (1,'C'); > insert into data_b (id,val) values (2,'C'); > insert into data_b (id,val) values (3,'C'); > insert into data_b (id,val) values (4,'D'); > insert into data_b (id,val) values (5,'D'); > insert into data_b (id,val) values (1,'C'); > insert into data_b (id,val) values (2,'C'); > insert into data_b (id,val) values (3,'C'); > insert into data_b (id,val) values (4,'D'); > insert into data_b (id,val) values (5,'D'); > insert into data_b (id,val) values (1,'C'); > insert into data_b (id,val) values (2,'C'); > insert into data_b (id,val) values (3,'C'); > insert into data_b (id,val) values (4,'D'); > insert into data_b (id,val) values (5,'D');> > create view view_a ( id ) as > SELECT distinct> > id > FROM > > data_a > ; > > create view view_b ( id, bucket) as> SELECT > > view_a.id, > > case > > when (SELECT count(*)=0 FROM data_b WHERE data_b.id=view_a.id > and data_b.val='C') then 1 > > when (SELECT count(*)=0 FROM data_b WHERE data_b.id=view_a.id > and data_b.val='D') then 2 > > else 3 > > end as bucket > FROM > > view_a > ; > > SELECT > > * > FROM > > view_b > WHERE > > view_b.bucket=2 > ; > > You get this: > > id bucket > ----- --------- > 1 1 > 2 1 > 3 1 > > Anybody have any ideas? >

↪ replying to Art S. Kagel (Oninit LLC)
Chris Salch — — source: IIUG Forums & Mailing Lists

Nice guess but no cigar. It gives the same results either way. The thing I find most telling about this is when you look at the data set and compare it to what should show up in bucket 2. Even though the result set says it is returning bucket 1, the returned values are what should be in bucket 2. Also, if you modify view_a as follows: create view view_a ( id ) asSELECT --distinct id FROM data_a ; You wind up with duplicate values but, the result set shows the correct bucket. On Mon, 2007-11-26 at 18:07 -0500, Art S. Kagel (Oninit LLC) wrote: > Chris Salch wrote: > > I strongly suspect that your CASE statements in the 'bucket' calculated > columns is faulty. I have no idea how ANY database engine would > interpret 'SELECT count(*)=0 ...' in the way you intend. IB that it is > ALWAYS returning '1'. Try it this way: > > case > > when ((SELECT count(*) FROM data_b WHERE data_b.id=view_a.id > and data_b.val='C') = 0) then 1 > > when ((SELECT count(*) FROM data_b WHERE data_b.id=view_a.id > and data_b.val='D') = 0) then 2 > > else 3 > > end as bucket > > Art S. Kagel > > > Ok, > > HPUX 11iv1 Informix 9.40.HC5 > > > > I came across some thing utterly nuts: > > > > create table data_a ( id integer, vali integer); > > create table data_b ( id integer, val char(1));> > > > -- data > > insert into data_a (id,vali) values (1,1); > > insert into data_a (id,vali) values (2,1); > > insert into data_a (id,vali) values (3,1); > > insert into data_a (id,vali) values (4,1); > > insert into data_a (id,vali) values (5,1); > > insert into data_a (id,vali) values (2,2); > > insert into data_a (id,vali) values (3,2); > > insert into data_a (id,vali) values (4,2); > > insert into data_a (id,vali) values (5,2);> > > > insert into data_b (id,val) values (1,'C'); > > insert into data_b (id,val) values (2,'C'); > > insert into data_b (id,val) values (3,'C'); > > insert into data_b (id,val) values (4,'D'); > > insert into data_b (id,val) values (5,'D'); > > insert into data_b (id,val) values (1,'C'); > > insert into data_b (id,val) values (2,'C'); > > insert into data_b (id,val) values (3,'C'); > > insert into data_b (id,val) values (4,'D'); > > insert into data_b (id,val) values (5,'D'); > > insert into data_b (id,val) values (1,'C'); > > insert into data_b (id,val) values (2,'C'); > > insert into data_b (id,val) values (3,'C'); > > insert into data_b (id,val) values (4,'D'); > > insert into data_b (id,val) values (5,'D');> > > > create view view_a ( id, vali) as> > SELECT > > > > data_a.id,data_a.vali > > FROM > > > > data_a join data_a sec on sec.id=data_a.id > > group by 1,2 > > having data_a.vali=max(sec.vali) > > ; > > > > create view view_b ( id, bucket) as> > SELECT > > > > view_a.id, > > > > case > > > > when (SELECT count(*)=0 FROM data_b WHERE data_b.id=view_a.id > > and data_b.val='C') then 1 > > > > when (SELECT count(*)=0 FROM data_b WHERE data_b.id=view_a.id > > and data_b.val='D') then 2 > > > > else 3 > > > > end as bucket > > FROM > > > > view_a > > ; > > > > SELECT > > > > * > > FROM > > > > view_b > > WHERE > > > > view_b.bucket=2 > > ; > > > > >From that you get the results : > > > > id bucket > > ----- --------- > > 1 1 > > 2 1 > > 3 1 > > > > Similarly if you use this: > > > > create table data_a ( id integer); > > create table data_b ( id integer, val char(1));> > > > -- data > > insert into data_a (id) values (1); > > insert into data_a (id) values (2); > > insert into data_a (id) values (3); > > insert into data_a (id) values (4); > > insert into data_a (id) values (5); > > insert into data_a (id) values (2); > > insert into data_a (id) values (3); > > insert into data_a (id) values (4); > > insert into data_a (id) values (5);> > > > insert into data_b (id,val) values (1,'C'); > > insert into data_b (id,val) values (2,'C'); > > insert into data_b (id,val) values (3,'C'); > > insert into data_b (id,val) values (4,'D'); > > insert into data_b (id,val) values (5,'D'); > > insert into data_b (id,val) values (1,'C'); > > insert into data_b (id,val) values (2,'C'); > > insert into data_b (id,val) values (3,'C'); > > insert into data_b (id,val) values (4,'D'); > > insert into data_b (id,val) values (5,'D'); > > insert into data_b (id,val) values (1,'C'); > > insert into data_b (id,val) values (2,'C'); > > insert into data_b (id,val) values (3,'C'); > > insert into data_b (id,val) values (4,'D'); > > insert into data_b (id,val) values (5,'D');> > > > create view view_a ( id ) as > > SELECT distinct> > > > id > > FROM > > > > data_a > > ; > > > > create view view_b ( id, bucket) as> > SELECT > > > > view_a.id, > > > > case > > > > when (SELECT count(*)=0 FROM data_b WHERE data_b.id=view_a.id > > and data_b.val='C') then 1 > > > > when (SELECT count(*)=0 FROM data_b WHERE data_b.id=view_a.id > > and data_b.val='D') then 2 > > > > else 3 > > > > end as bucket > > FROM > > > > view_a > > ; > > > > SELECT > > > > * > > FROM > > > > view_b > > WHERE > > > > view_b.bucket=2 > > ; > > > > You get this: > > > > id bucket > > ----- --------- > > 1 1 > > 2 1 > > 3 1 > > > > Anybody have any ideas? > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- -------------------------------------------- Chris Salch