Re: Riddle me this?

Jim Addison

Re: Riddle me this?
(in response to Brian Goldberg)
A where clause in your subselect ?

Jim.




Mark Buzzard
<mark_buzzar To: [login to unmask email]
[login to unmask email]> cc: (bcc: Jim Addison/IS/SLC/StandardLifeGroup)
Sent by: bcc: Jim Addison/IS/SLC/StandardLifeGroup
DB2 Data Subject: Riddle me this?
Base
Discussion
List
<[login to unmask email]
erv.ylassoc.
com>


27/12/2002
14:33
Please
respond to
DB2 Data
Base
Discussion
List






I have what I think is an odd result from a comparision that I want to
achieve on two like tables.

Below is the query I am running:

SELECT COUNT(*)
FROM ID1.SOURCE
WHERE NOT EXISTS(
SELECT 1 FROM ID2.SOURCE)

ID1.SOURCE total count is 20,500
ID2.SOURCE total count is 20,000

Each table has a unique index on the same 5 of its 7 columns. I think that
my count for this query should be 500, however, I am getting a count of 0.
The reason I say this, is I took a quick look on the first column's values,
and I have different values present on each table. So set A does contain
values different than set B.

Any help on what I am missing?



Buzz



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can






For more information on Standard Life, visit our website
http://www.standardlife.com/

The Standard Life Assurance Company, Standard Life House, 30 Lothian Road,
Edinburgh EH1 2DH, is registered in Scotland (No. SZ4) and regulated by the
Financial Services Authority. Tel: 0131 225 2552 - calls may be recorded or
monitored. This confidential e-mail is for the addressee only. If received
in error, do not retain/copy/disclose it without our consent and please
return it to us. We virus scan and monitor all e-mails but are not
responsible for any damage caused by a virus or alteration by a third party
after it is sent.



Mark Buzzard

Riddle me this?
I have what I think is an odd result from a comparision that I want to
achieve on two like tables.

Below is the query I am running:

SELECT COUNT(*)
FROM ID1.SOURCE
WHERE NOT EXISTS(
SELECT 1 FROM ID2.SOURCE)

ID1.SOURCE total count is 20,500
ID2.SOURCE total count is 20,000

Each table has a unique index on the same 5 of its 7 columns. I think that
my count for this query should be 500, however, I am getting a count of 0.
The reason I say this, is I took a quick look on the first column's values,
and I have different values present on each table. So set A does contain
values different than set B.

Any help on what I am missing?



Buzz



Terry Purcell

Re: Riddle me this?
(in response to Mark Buzzard)
Mark,

You have coded a non-correlated subquery. Try correlating it.....

SELECT COUNT(*)
FROM ID1.SOURCE I1
WHERE NOT EXISTS(
SELECT 1 FROM ID2.SOURCE I2
WHERE I2.COL = I1.COL)

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Mark Buzzard
Sent: Friday, December 27, 2002 8:34 AM
To: [login to unmask email]
Subject: Riddle me this?


I have what I think is an odd result from a comparision that I want to
achieve on two like tables.

Below is the query I am running:

SELECT COUNT(*)
FROM ID1.SOURCE
WHERE NOT EXISTS(
SELECT 1 FROM ID2.SOURCE)

ID1.SOURCE total count is 20,500
ID2.SOURCE total count is 20,000

Each table has a unique index on the same 5 of its 7 columns. I think that
my count for this query should be 500, however, I am getting a count of 0.
The reason I say this, is I took a quick look on the first column's values,
and I have different values present on each table. So set A does contain
values different than set B.

Any help on what I am missing?



Buzz








Brian Goldberg

Re: Riddle me this?
(in response to Terry Purcell)
Your subquery has no selection conditions, so it will always be true.

Your query should look something like this:

SELECT COUNT(*)
FROM ID1.SOURCE ID1
WHERE NOT EXISTS(
SELECT 1 FROM ID2.SOURCE ID2
WHERE ID1.col1 = ID2.col1
AND ID1.col2 = ID2.col2 ... )

Brian



Mark Buzzard
<[login to unmask email] To: [login to unmask email]
C.COM> cc: (bcc: BRIAN GOLDBERG/QVC)
Ext: NA Subject: Riddle me this?
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
LASSOC.COM>


12/27/2002 09:33
AM
Please respond to
DB2 Data Base
Discussion List






I have what I think is an odd result from a comparision that I want to
achieve on two like tables.

Below is the query I am running:

SELECT COUNT(*)
FROM ID1.SOURCE
WHERE NOT EXISTS(
SELECT 1 FROM ID2.SOURCE)

ID1.SOURCE total count is 20,500
ID2.SOURCE total count is 20,000

Each table has a unique index on the same 5 of its 7 columns. I think that
my count for this query should be 500, however, I am getting a count of 0.
The reason I say this, is I took a quick look on the first column's values,
and I have different values present on each table. So set A does contain
values different than set B.

Any help on what I am missing?



Buzz



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



[login to unmask email]

Re: Riddle me this?
(in response to Jim Addison)
How about

SELECT COUNT(*)
FROM ID1.SOURCE A
WHERE NOT EXISTS(
SELECT 1 FROM ID2.SOURCE B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
AND A.COL3 = B.COL3
AND A.COL4 = B.COL4
AND A.COL5 = B.COL5)





Mark Buzzard <[login to unmask email]>@LISTSERV.YLASSOC.COM> on 12/27/2002
06:33:40 AM

Please respond to DB2 Data Base Discussion List
<[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Riddle me this?


I have what I think is an odd result from a comparision that I want to
achieve on two like tables.

Below is the query I am running:

SELECT COUNT(*)
FROM ID1.SOURCE
WHERE NOT EXISTS(
SELECT 1 FROM ID2.SOURCE)

ID1.SOURCE total count is 20,500
ID2.SOURCE total count is 20,000

Each table has a unique index on the same 5 of its 7 columns. I think that
my count for this query should be 500, however, I am getting a count of 0.
The reason I say this, is I took a quick look on the first column's values,
and I have different values present on each table. So set A does contain
values different than set B.

Any help on what I am missing?



Buzz



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can


**********************************************************************
Please Note:
The information in this E-mail message, and any files transmitted
with it, is confidential and may be legally privileged. It is
intended only for the use of the individual(s) named above. If you
are the intended recipient, be aware that your use of any confidential
or personal information may be restricted by state and federal
privacy laws. If you, the reader of this message, are not the
intended recipient, you are hereby notified that you should not
further disseminate, distribute, or forward this E-mail message.
If you have received this E-mail in error, please notify the sender
and delete the material from any computer. Thank you.
**********************************************************************



John H. Maenpaa Jr.

Re: Riddle me this?
(in response to William_O'Black@FFIC.COM)
Shouldn't your query be:
SELECT COUNT(*)
FROM ID1.SOURCE
WHERE NOT EXISTS(
SELECT 1 FROM ID2.SOURCE
WHERE ID1.SOURCE.KEY = ID2.SOURCE.KEY)

On Friday 27 December 2002 08:33 am, Mark Buzzard wrote:
> I have what I think is an odd result from a comparision that I want to
> achieve on two like tables.
>
> Below is the query I am running:
>
> SELECT COUNT(*)
> FROM ID1.SOURCE
> WHERE NOT EXISTS(
> SELECT 1 FROM ID2.SOURCE)
>
> ID1.SOURCE total count is 20,500
> ID2.SOURCE total count is 20,000
>
> Each table has a unique index on the same 5 of its 7 columns. I think that
> my count for this query should be 500, however, I am getting a count of 0.
> The reason I say this, is I took a quick look on the first column's values,
> and I have different values present on each table. So set A does contain
> values different than set B.
>
> Any help on what I am missing?
>
>
>
> Buzz
--
John Maenpaa
Yevich, Lawson and Associates
www.ylassoc.com



michael bell

Re: Riddle me this?
(in response to John H. Maenpaa Jr.)
Look at the SQL manual about the difference between correlated sub-queries
and non-correlated sub-queries. A correlated sub-query has join criteria in
the sub-query. The SELECT 1 FROM ID2.SOURCE always succeeds so no row from
id1.source every qualify.

Mike Bell
HLS Technologies
----- Original Message -----
From: "Mark Buzzard" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Friday, December 27, 2002 8:33 AM
Subject: Riddle me this?


> I have what I think is an odd result from a comparision that I want to
> achieve on two like tables.
>
> Below is the query I am running:
>
> SELECT COUNT(*)
> FROM ID1.SOURCE
> WHERE NOT EXISTS(
> SELECT 1 FROM ID2.SOURCE)
>
> ID1.SOURCE total count is 20,500
> ID2.SOURCE total count is 20,000
>
> Each table has a unique index on the same 5 of its 7 columns. I think
that
> my count for this query should be 500, however, I am getting a count of 0.
> The reason I say this, is I took a quick look on the first column's
values,
> and I have different values present on each table. So set A does contain
> values different than set B.
>
> Any help on what I am missing?
>
>
>
> Buzz
>
>
>





Dave Nance

Re: Riddle me this?
(in response to michael bell)
It seems to me the where clause is missing, but I'll hazard a guess that you just left that off of the mail you sent? SQL should look something like:
SELECT COUNT(*)
FROM ID1.SOURCE S1
WHERE NOT EXISTS(
SELECT 1 FROM ID2.SOURCE S2
where S1.keycols = S2.keycols)




Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 12/27/02 9:33:40 AM >>>
I have what I think is an odd result from a comparision that I want to
achieve on two like tables.

Below is the query I am running:

SELECT COUNT(*)
FROM ID1.SOURCE
WHERE NOT EXISTS(
SELECT 1 FROM ID2.SOURCE)

ID1.SOURCE total count is 20,500
ID2.SOURCE total count is 20,000

Each table has a unique index on the same 5 of its 7 columns. I think that
my count for this query should be 500, however, I am getting a count of 0.
The reason I say this, is I took a quick look on the first column's values,
and I have different values present on each table. So set A does contain
values different than set B.

Any help on what I am missing?



Buzz




This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.