Oninit cdi / Archive
Talk to an expert →

SQL question

Posted in 2009

Topics: SQL Development & Query Writing, Versions, Editions & End-of-Life

Kennedy, Randy — — source: Usenet: comp.databases.informix

IDS 9.40.FC3 on HPUX 11.11 When I run the following SQL (Informix Join), I get back almost every record and they don't meet the criteria; however, if I run the statement using SQL join, I get the correct results. Any idea of why the difference or did I create it incorrectly? select caa443400013,caa443400014 from caa44340,outer caa44040 where caa443400011 = caa440400011 and caa443400012 = caa440400012 and caa443400013 = caa440400013 and caa443400014 = caa440400014 and caa440400014 is null and caa443400013 = 'PR' SELECT caa443400013, caa443400014 FROM caa44340 LEFT JOIN caa44040 ON (caa443400011 = caa440400011) AND (caa443400012 = caa440400012) AND (caa443400013 = caa440400013) AND (caa443400014 = caa440400014) WHERE (((caa440400014) Is Null) AND ((caa443400013)='PR')); Thanks, Randy

↪ replying to Kennedy, Randy
Fernando Nunes — — source: Usenet: comp.databases.informix

Kennedy, Randy wrote: > IDS 9.40.FC3 on HPUX 11.11 > > When I run the following SQL (Informix Join), I get back almost every > record and they don't meet the criteria; however, if I run the statement > using SQL join, I get the correct results. Any idea of why the > difference or did I create it incorrectly? > > select caa443400013,caa443400014 > from caa44340,outer caa44040 > where caa443400011 = caa440400011 > and caa443400012 = caa440400012 > and caa443400013 = caa440400013 > and caa443400014 = caa440400014 > and caa440400014 is null > and caa443400013 = 'PR'> > > > SELECT caa443400013, caa443400014 > FROM caa44340 LEFT JOIN caa44040 ON (caa443400011 = caa440400011) AND > (caa443400012 = caa440400012) AND (caa443400013 = caa440400013) AND > (caa443400014 = caa440400014) > WHERE (((caa440400014) Is Null) AND ((caa443400013)='PR'));> > > > Thanks, > Randy Could you give us an example of some records that prove your idea? Art Kagel had a very interesting response about this kind of query a few weeks ago. Curiously I had to do some queries of this type and I was able to test is statements... And for somebody not used to ANSI joins it can be confusing... I shared my experiences with an Oracle DBA (which shares my handicap with ANSI joins) and the results were the same... (his surprise included :) ). Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Glossary