Archives of the TeradataForum

Message Posted: Thu, 12 Jun 2003 @ 15:04:31 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi All,

I am having some trouble with results from the following query.

SELECT COUNT(*)
FROM ETL_TEMP.XREF_VENDOR_TMP TP
WHERE TP.VENDOR_ID
NOT IN (select DISTINCT VENDOR_ID FROM ETL_TEMP.XREF_VENDOR);

this query returns a result of 0 where it should be showing that there are 3993 values of VENDOR_ID in the XREF_VENDOR_TMP that are not in the XREF_VENDOR table, However a left join between these tables does return the correct value, does anyone have an ideas as to what I have done wrong here?

I have provided the set of queries and there results along with the DDL for the tables below.

Thanks Peter.

------------------------------------------------------

SELECT COUNT(*)
FROM ETL_TEMP.XREF_VENDOR_TMP

returns: 231226

SELECT COUNT(*)
FROM ETL_TEMP.XREF_VENDOR_TMP TP
WHERE TP.VENDOR_ID
IN (SELECT VENDOR_ID FROM ETL_TEMP.XREF_VENDOR);

returns: 227233

SELECT COUNT(*)
FROM ETL_TEMP.XREF_VENDOR_TMP TP
WHERE TP.VENDOR_ID
NOT IN (SELECT VENDOR_ID FROM ETL_TEMP.XREF_VENDOR);

returns: 0
this value should be: 3993
--------------------------

however a left join on the same table set i.e.

SELECT COUNT(*) FROM ETL_TEMP.XREF_VENDOR_TMP TP
LEFT JOIN ETL_TEMP.XREF_VENDOR F ON TP.VENDOR_ID = F.VENDOR_ID
WHERE F.VENDOR_ID IS NULL

returns:  3993

As you can see in the in the DDL below the data types for the VENDOR_ID columns are the same and they are both the unique primary keys to their respective tables and there are no null values in either column. (Just for laughs I have run the same set of queries using a select distinct for the vendor_id with the same results)

CREATE SET TABLE ETL_TEMP.XREF_VENDOR ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      VENDOR_ID VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
UNIQUE PRIMARY INDEX ( VENDOR_ID );

CREATE SET TABLE ETL_TEMP.XREF_VENDOR_TMP ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      VENDOR_ID VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
UNIQUE PRIMARY INDEX ( VENDOR_ID );

	<Prev	Next>		<<First	<Prev	Next>	Last>>