different results in select ? – Informix Archive

Erik Kurz — Fri, 10 Sep 2004 12:46:58 +0200 — source: Usenet: comp.databases.informix

hi, i get different results executing following selects: additional information -> tab3 has no records! 1) SELECT tab1.*,tab2.*,'E11','EXP', FROM tab1,tab2, WHERE fakturen_nr = '123456', AND tab2.rowid = tab1.id_leistungsart result (3 records): tab1.*,tab2.*,E11,EXP tab1.*,tab2.*,E11,EXP tab1.*,tab2.*,E11,EXP 2) SELECT tab1.*,tab2.*,'E11','EXP', FROM tab1,tab2, WHERE fakturen_nr = '123456', AND tab2.rowid = tab1.id_leistungsart, UNION SELECT tab3.*,tab2.*,'I10','IE', FROM tab3,tab2, WHERE fakturen_nr = '123456', AND tab2.rowid = tab3.id_leistungsart result (1 record !!!): tab1.*,tab2.*,E11,EXP can anyone explain me this different results ? is this a bug or is it just my mistake ? thanks erik

↪ replying to Erik Kurz

Doug Lawry — Fri, 10 Sep 2004 13:10:47 +0100 — source: Usenet: comp.databases.informix

Change UNION (which removes duplicates) to UNION ALL. -- Regards, Doug Lawry www.douglawry.webhop.org "Erik Kurz" <erik.kurz@analytika.at> wrote in message news:414185e8$1@e-post.inode.at... > hi, > > i get different results executing following selects: > additional information -> tab3 has no records! > > 1) SELECT tab1.*,tab2.*,'E11','EXP', > FROM tab1,tab2, > WHERE fakturen_nr = '123456', > AND tab2.rowid = tab1.id_leistungsart > > result (3 records): tab1.*,tab2.*,E11,EXP > tab1.*,tab2.*,E11,EXP > tab1.*,tab2.*,E11,EXP > > 2) SELECT tab1.*,tab2.*,'E11','EXP', > FROM tab1,tab2, > WHERE fakturen_nr = '123456', > AND tab2.rowid = tab1.id_leistungsart, > UNION > SELECT tab3.*,tab2.*,'I10','IE', > FROM tab3,tab2, > WHERE fakturen_nr = '123456', > AND tab2.rowid = tab3.id_leistungsart > > result (1 record !!!): tab1.*,tab2.*,E11,EXP > > can anyone explain me this different results ? > is this a bug or is it just my mistake ? > > thanks > erik

↪ replying to Erik Kurz

Erik Kurz — Fri, 10 Sep 2004 14:22:52 +0200 — source: Usenet: comp.databases.informix

thanks to all, the problem is solved now greetings from austria, erik "Erik Kurz" <erik.kurz@analytika.at> schrieb im Newsbeitrag news:414185e8$1@e-post.inode.at... > hi, > > i get different results executing following selects: > additional information -> tab3 has no records! > > 1) SELECT tab1.*,tab2.*,'E11','EXP', > FROM tab1,tab2, > WHERE fakturen_nr = '123456', > AND tab2.rowid = tab1.id_leistungsart > result (3 records): tab1.*,tab2.*,E11,EXP > tab1.*,tab2.*,E11,EXP > tab1.*,tab2.*,E11,EXP > > 2) SELECT tab1.*,tab2.*,'E11','EXP', > FROM tab1,tab2, > WHERE fakturen_nr = '123456', > AND tab2.rowid = tab1.id_leistungsart, > UNION > SELECT tab3.*,tab2.*,'I10','IE', > FROM tab3,tab2, > WHERE fakturen_nr = '123456', > AND tab2.rowid = tab3.id_leistungsart > result (1 record !!!): tab1.*,tab2.*,E11,EXP > > can anyone explain me this different results ? > is this a bug or is it just my mistake ? > > thanks > erik > >