Archives of the TeradataForum

Message Posted: Wed, 25 Mar 2009 @ 20:21:21 GMT

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

Is there a trick to comparing dates from different tables? I have compared dates in a good many scripts and have never had an issue, but today, all of a sudden I am having an issue with one table. The column definitions in both tables are defined the exact same way:

     WARNING_CODE_EXPIRATION_1 DATE FORMAT 'YYYY-MM-DD' DEFAULT NULL

But when I do a query like below, I get no results returned even though there are definitely rows that should be returned.

     SELECT
        O.WARNING_CODE_EXPIRATION_1,
        I.WARNING_CODE_EXPIRATION_1

     FROM
         L_ACCOUNT O

     FULL OUTER JOIN V1_L_ACCOUNT I
         ON O.ACCOUNTNUMBER_SRC = I.ACCOUNTNUMBER_SRC

     WHERE
          O.WARNING_CODE_EXPIRATION_1 <> I.WARNING_CODE_EXPIRATION_1 OR
          O.WARNING_CODE_EXPIRATION_2 <> I.WARNING_CODE_EXPIRATION_2 OR
          O.WARNING_CODE_EXPIRATION_3 <> I.WARNING_CODE_EXPIRATION_3 OR
          O.WARNING_CODE_EXPIRATION_4 <> I.WARNING_CODE_EXPIRATION_4 OR
          O.WARNING_CODE_EXPIRATION_5 <> I.WARNING_CODE_EXPIRATION_5 OR
          O.WARNING_CODE_EXPIRATION_6 <> I.WARNING_CODE_EXPIRATION_6 OR
          O.WARNING_CODE_EXPIRATION_7 <> I.WARNING_CODE_EXPIRATION_7 OR
          O.WARNING_CODE_EXPIRATION_8 <> I.WARNING_CODE_EXPIRATION_8

I have done a lot of 'messing around' to try to figure this out, but cannot seem to find the solution. I am not sure if it is a problem with null values or maybe if it is a problem with the format definition or maybe something completely different.

One thing about this specific table that is different from the rest is that I did 'CREATE TABLE AS ... WITH DATA' statement to populate the table. Did that maybe have an effect on how the dates are placed into the one table?

Has anyone ever had any problems like this? Thanks in advance for your help.

Shaun

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