Re: Why is this possible? -- Left outer join does not work
Perhaps
an index is corrupt?
At 10:02 AM 4/14/2005, Jean Sagi wrote:
>Generically speaking Why is this possible:
>
>-- Counting is 7
>select count(*)
>from d left outer join a on d.f1=a.f1 abd d.f2=a.f2
>where d.f3 = _constant_
>and d.f4 = _constant_>;
> (count(*))
> 7
>
>-- Selecting all records with same previous filter:
>select *
>from d left outer join a on d.f1=a.f1 abd d.f2=a.f2
>where d.f3 = _constant_
>and d.f4 = _constant_>;
>
>f1 f2 f3 f4
>--- --- --- ---
>1..............
>2..............
>3..............
>4..............
>5..............
>6..............
>
>6 row(s) retrieved.
>
>
>Why the countig gives a different number than the number of record
>returned from the same query/filter?
>
>Never happened before.
>
>Maybe it is a problem involved with one of the tables? in the previous
>example table a is almost static and has hundred of records but table d is
>one of the busiest tables with millions of records... Maybe something is
>wrong with this table?
>
>Any hist would be appreciated.
>
>J.
>
>PD: What are the proper checks for a table in IDS, somewhere I read they
>are something like oncheck -Cxxx ... Surely our DBA knows them.
>
>
>
>Jean Sagi
>jeansagi@myrealbox.com
>jeansagi@yahoo.com
----------------------------------------------
Michael Dunham-Wilkie, M.Sc., M.P.A.
Senior Database Analyst
Barrodale Computing Services Ltd.
Tel: (250) 472-4372 Fax: (250) 472-4373
Web: <http://www.barrodale.com>http://www.barrodale.com
Email: mike@barrodale.com
----------------------------------------------
Mailing Address:
P.O. Box 3075 STN CSC
Victoria BC Canada V8W 3W2
Shipping Address:
Hut R, McKenzie Avenue
University of Victoria
Victoria BC Canada V8W 3W2
----------------------------------------------
barrodale.com -> Very interesintg...
I can't imagine myself why IBM doesn't shows things like this a lot more.
Marketing maybe?
J.
PD: I have nothing to do with barrodale.com
-----Original Message-----
From: "Mike Dunham...." <mike@barrodale.com>
To: ids@iiug.org
Date: Thu, 14 Apr 2005 16:00:17 -0400 (EDT)
Subject: Re: Why is this possible? -- Left outer join does not work [4715]
Perhaps an index is corrupt?
At 10:02 AM 4/14/2005, Jean Sagi wrote:
>Generically speaking Why is this possible:
>
>-- Counting is 7
>select count(*)
>from d left outer join a on d.f1=a.f1 abd d.f2=a.f2
>where d.f3 = _constant_
>and d.f4 = _constant_>;
> (count(*))
> 7
>
>-- Selecting all records with same previous filter:
>select *
>from d left outer join a on d.f1=a.f1 abd d.f2=a.f2
>where d.f3 = _constant_
>and d.f4 = _constant_>;
>
>f1 f2 f3 f4
>--- --- --- ---
>1..............
>2..............
>3..............
>4..............
>5..............
>6..............
>
>6 row(s) retrieved.
>
>
>Why the countig gives a different number than the number of record
>returned from the same query/filter?
>
>Never happened before.
>
>Maybe it is a problem involved with one of the tables? in the previous
>example table a is almost static and has hundred of records but table d is
>one of the busiest tables with millions of records... Maybe something is
>wrong with this table?
>
>Any hist would be appreciated.
>
>J.
>
>PD: What are the proper checks for a table in IDS, somewhere I read they
>are something like oncheck -Cxxx ... Surely our DBA knows them.
>
>
>
>Jean Sagi
>jeansagi@myrealbox.com
>jeansagi@yahoo.com
----------------------------------------------
Michael Dunham-Wilkie, M.Sc., M.P.A.
Senior Database Analyst
Barrodale Computing Services Ltd.
Tel: (250) 472-4372 Fax: (250) 472-4373
Web: <http://www.barrodale.com>http://www.barrodale.com
Email: mike@barrodale.com
----------------------------------------------
Mailing Address:
P.O. Box 3075 STN CSC
Victoria BC Canada V8W 3W2
Shipping Address:
Hut R, McKenzie Avenue
University of Victoria
Victoria BC Canada V8W 3W2
----------------------------------------------
Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com