Gentlemen:
Thanks so much for your assistance.
This returns 512 rows.
select * from tmp_index_member tim
where tim.ISIN NOT IN
(select ISIN from security sec
where ISIN is NOT NULL and
securitytypekey IS NOT NULL and securitytypekey NOT IN ( 5,27) )
Can someone explain why the NULL ISINs in Security is causing
so much grief? I do not get it.
KD
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andreas Joseph
Krogh
Sent: Wednesday, October 29, 2008 3:58 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] simple SQL query
On Wednesday 29 October 2008 18:39:42 Kevin Duffy wrote:
> Hello:
>
>
>
> I have a couple of queries that are giving me headaches.
>
> They are actually very simple, but I do not understand why
>
> I am not getting the expected results. Maybe I need new glasses.
>
> Please be kind.
>
>
>
> The table definitions are below.
>
>
>
> The table TMP_INDEX_MEMBER contains 21057 rows.
>
> These rows contain 3167 distinct ISINs. ISIN is a type of unique
> security identifier.
>
>
>
> This query
>
> select * from security
>
> where securitytypekey NOT IN ( 5,27) and ISIN IN
>
> (select ISIN from tmp_index_member )
>
> returns 3069 rows. This tells me that there are 3069 ISINs
>
> in the SECURITY table. ISINs that I already know about.
>
>
>
>
>
> update tmp_index_member set securitykey = security.securitykey
>
> from security
>
> where securitytypekey NOT IN (5,27) and tmp_index_member.ISIN =
> security.ISIN
>
> results in Query returned successfully: 20545 rows affected, 2169 ms
> execution time.
>
>
>
> There are now 512 row in TMP_INDEX_MEMBER that have not been updated.
>
> OK now the confusion begins.
>
>
>
> I would expect the following query to return 512 rows. It returns zero.
>
> select * from tmp_index_member tim
>
> where tim.ISIN NOT IN
>
> (select distinct sec.ISIN from security sec where securitytypekey NOT
> IN ( 5,27) )
>
>
>
>
>
> I want to add to SECURITY the securities that are new to me. To do this
> I need the above
>
> query to work.
I bet you have NULLs in some of the rows so your "NOT IN" doesn't work. I
suggest you rewrite to something like:
... WHERE (securitytypekey IS NOT NULL OR securitytypekey NOT IN (5,27))...
> Question: does a UNIQUE constraint create an index?
Yes.
> Maybe your fresh eyes will see something obvious.
>
> Many thanks to taking a look at this issue.
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / CEO
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
