Re: ACE Report problem

mtiernan@world.std.com (Michael C Tiernan) writes: >I want to scream, really. Hello everyone. I'm hoping that there's >someone out here who can point out where I'm screwing this darned thing >up. I've got a report that I'm trying to run through ACE and I USED to >have it working but then I had to rewrite it and now it's failing. >I'm stuck with Informix V2.10.00B right now (please, no Flames, I don't >have a choice) >Here's the problem: > Table 1 - Customer: > customer_id serial; > [Other fields] > Table 2 - Problem: > customer_id integer; (Points to same above) > modid serial; > report_date date; > synopsis text(short); > [Other fields] > Table 3 - Misc: > modid integer; (Points to same above) > description text(long); > > There may NOT be an entry in table 3 for each occurance of table > 2 but there will be a table 2 for any table 3. Also, there WILL > be a table 1 entry for any table 2 entry. There CAN be multiple > table 2s for any table 1 (I know you know that but I thought I'd > say it anyway) > What I have tried to do is search for any occurance of a > "string" in misc.description, then, if found, return the table 2 > that it goes with and the table 1. Now, if there's no "string" > in Misc, search Problem (T2) for it and if found, return the > problem and the customer information. > I was using a select like this: > select > [Assorted fields] > from > customer, problem, outer misc > where > customer.customer_id = problem.customer_id > and problem.rdate > "1.1.92" > and ( ( problem.modid = problem.modid > and misc.description like "%timeout%" ) > or problem.synopsis like "%timeout%" ) > I was aiming for: > Search all problems > the first of 1992 for the word > "timeout" and if found, report it and the customer info. > If the word was found in the associated table "misc" > (which may NOT have an entry, report it) >The problem is that this is NOT finding the entries that it should. I'm >using very hard wired entries, forcing it to find ones I know exist >(I've done the presearches by hand to prove it) but each time I try, it >never seems to use the 'outer' table correctly, it seems to either find >ANY entry in 'misc' which has the same "modid" as a problem entry or it >doesn't report a "problem" entry because it doesn't have a "Misc" entry >attached to it. >My question is, what the HECK AM I DOING WRONG? I suspect that this is >a case of looking too closely. Two untested possibilities: I think you may just have your parentheses in the wrong place, along with some syntax anomalies: select [Assorted fields] from customer, problem, outer misc where customer.customer_id = problem.customer_id and problem.rdate > "1.1.92" and problem.modid = misc.modid and ( misc.description like "%timeout%" or problem.synopsis like "%timeout%" ) If this doesn't satisfy, you might try a UNION: select [Assorted fields] from customer, problem, outer misc where customer.customer_id = problem.customer_id and problem.rdate > "1.1.92" and problem.modid = misc.modid and problem.synopsis like "%timeout%" UNION select [Assorted fields] from customer, problem, misc where customer.customer_id = problem.customer_id and problem.rdate > "1.1.92" and problem.modid = misc.modid and misc.description like "%timeout%" ================ Dennis J. Pimple Informix CSE / Denver 303-850-0210