RE: Rewrite query
-----
Original Message -----
From: Jay Zwagerman <jay2@iafalls.com>
At: 9/ 4 10:25
> Art
>
> Thanks for your response, but I don't think 7.2 supports left outer
> joins either. What would be the equivalent?
Oops, sorry, didn't notice the version info. You can use INFORMIX OUTER join
syntax but that does not allow for filtering on the NULL-ness of the fields
from
the outer table so you have to select one field from the outer table, write all
of the output to temp table, and do the filter when you count the temp table
rows. So:
select t1.address1, t1.address2, t1.zip, t2.zip zip2
from t1 left outer join t2
where t1.address1 = t2.address1 and t1.address2 = t2.address2
and t1.zip = t2.zip
UNION
select t1.address1, t1.address2, t1.zip, t3.zip zip2
from t1 left outer join t3
where t1.address1 = t3.address1 and t1.address2 = t3.address2
and t1.zip = t3.zip
UNION
select t2.address1, t2.address2, t2.zip, t3.zip zip2
from t2 left outer join t3
where t2.address1 = t3.address1 and t2.address2 = t3.address2
and t2.zip = t3.zip
INTO TEMP barney;
SELECT COUNT(*)
FROM barney
WHERE zip2 IS NOT NULL;
BTW you REALLY should upgrade. IDS 7.24 was 10-15% faster than 7.21; IDS 7.30
was 15-25% faster than 7.24; IDS 7.31 was about 10% faster than 7.30; IDS
7.31UD2+ improved sorting, index creation and update statistics speed; and
that's just staying in the 7.xx line. If you want to go to 9.xx note that IDS
9.3 improved speed somewhat over IDS 7.31 and IDS 9.4 is faster than IDS 9.3
and
that does not mention the new features you are missing out on the least of
which is ANSI OUTER JOIN syntax.
Art S. Kagel
> Jay
>
> -----Original Message-----
> From: ART KAGEL, BLOOMBERG/ 65E 55TH [mailto:KAGEL@bloomberg.net]
> Sent: Thursday, September 04, 2003 8:12 AM
> To: jay2@iafalls.com
> Subject: Re: Rewrite query [506]
>
> IDS does not support selecting from a the results of a select statement
> directly
> you have to use a temp table. Try this:
>
>
> select t1.address1, t1.address2, t1.zip
> from t1 left outer join t2
> on t1.address1 = t2.address1 and t1.address2 = t2.address2 and t1.zip> = t2.zip
> where t2.zip is not null
> UNION
> select t1.address1, t1.address2, t1.zip
> from t1 left outer join t3
> on t1.address1 = t3.address1 and t1.address2 = t3.address2 and t1.zip> = t3.zip
> where t3.zip is not null
> UNION
> select t2.address1, t2.address2, t2.zip
> from t2 left outer join t3
> on t2.address1 = t3.address1 and t2.address2 = t3.address2 and t2.zip> = t3.zip
> where t3.zip is not null
> INTO TEMP fred;
>
> SELECT COUNT(*) from fred;>
> Art S. Kagel
>
>
> ----- Original Message -----
> From: Jay <jay2@iafalls.com>
> At: 9/ 4 9:58
>
> > Hello
> > I have 3 tables, and I am trying to find out how many addresses exist
> in all 3
> > tables. Each table structure has account_id, address1, address2, and
> zip.
> These
> > are all magazine subscription lists. I can't match up the account_id's
> because
> > someone could subscribe to 2 magazines and have different account_id's
> so I
> > would just want to match up addresses and zips. I hope I made sense.
> >
> > example
> >
> > table1
> > 123 fake st 55555
> > 111 fake st 55555
> > 128 fake st 55555
> >
> > table2
> > 123 fake st 55555
> > 222 fake st 55555
> > 224 fake st 55555
> >
> > table3
> > 123 fake st 55555
> > 128 fake st 55555
> > 667 fake st 55555
> >
> > I would want 2 as a result
> >
> > because 123 fake st and 128 fake st are in multiple tables.
> >
> > Here is the query as I know how to write it, but it doesn't work on
> Informix
> > 7.2. I get a syntax error after the from. Can someone help me on
> rewriting
> it?
> >
> >
> > select count(*)
> > from (select 1
> > from (select address1, address2, zip from t1
> > union all
> > select address1, address2, zip from t2
> > union all
> > select address1, address2, zip from t3)
> > group by address1, address2, zip
> > having count(*) > 1);