|
Archives of the TeradataForumMessage Posted: Thu, 12 Jun 2003 @ 15:04:31 GMT
Hi All, I am having some trouble with results from the following query. SELECT COUNT(*) 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 );
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||