Re: Exception Report

In article <2631@abcom.ATT.COM> mdb@abcom.ATT.COM (3030 ) writes: > >I need help with an INFORMIX ace report. > >I have a table which contains raw data and a table which contains >valid values for one of the columns column. > >What I need to be able to do is be able to identify all rows which >do not have a corresponding value in my look up table (i.e. exception >report). > >I realize that this is probable trivial but I am having trouble >figuring this out since I am new to Informix. > >Thank for your help. >Mike Hi Mike. What you need is not difficult but not trivial either. To do the lookup you would write: SELECT <lookup_column> FROM <lookup_table> WHERE <lookup_column> = <lookup_value> Now you are looking for thinks that don't match the above. A more complete exception hunt would look something like: SELECT * FROM <data_table> WHERE <data_table.possible_exception> NOT IN (SELECT <lookup_column> FROM <lookup_table> WHERE <lookup_column> = <data_table.possible_exception>) The parentheses are required syntax. You can play with these a bit. -- Jake Salomon -- ----------------Obligatory smart-$$$ remark:--------------------- | There's two kinds of people: Those who categorize people into | | two groups and those who don't. (Barth's Aging proverb.) | -----------------------------------------------jacob@informix.com