Re: Table differences
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Thu, 11 Jun 2009 10:38:10 -0700 (PDT)
Message-ID: <3e8cef15-34f3-40ee-bc7f-dac28b71b0a8_at_u10g2000vbd.googlegroups.com>
On Jun 10, 4:47 pm, Thomas Kellerer <FJIFALSDG..._at_spammotel.com> wrote:
> a..._at_unsu.com wrote on 10.06.2009 22:38:
>
> > I'm sure there is a way to do this. Have not found it yet.
>
> > But, I have 2 tables, different structures. Both have a date column.
> > I want to get the rows from table A which do not exist in table B,
> > based on the date:
>
> > TABLE A: COL1, DATE_COL
> > TABLE B: COL1, COL2, COL3, DATE_COL
>
> > I want to get the rows in TABLE A which are not in TABLE B, based
> > solely on the DATE_COL. I do not think I can use MINUS due to the
> > column difference. And, this is going to a user, so cannot use PL/SQL
> > either.....
>
> SELECT *
> FROM table_a
> WHERE date_col NOT IN (SELECT date_col FROM table_b WHERE date_col IS NOT NULL)
>
> You are aware that the DATE datatype in Oracle contains a time part as well?
Date: Thu, 11 Jun 2009 10:38:10 -0700 (PDT)
Message-ID: <3e8cef15-34f3-40ee-bc7f-dac28b71b0a8_at_u10g2000vbd.googlegroups.com>
On Jun 10, 4:47 pm, Thomas Kellerer <FJIFALSDG..._at_spammotel.com> wrote:
> a..._at_unsu.com wrote on 10.06.2009 22:38:
>
> > I'm sure there is a way to do this. Have not found it yet.
>
> > But, I have 2 tables, different structures. Both have a date column.
> > I want to get the rows from table A which do not exist in table B,
> > based on the date:
>
> > TABLE A: COL1, DATE_COL
> > TABLE B: COL1, COL2, COL3, DATE_COL
>
> > I want to get the rows in TABLE A which are not in TABLE B, based
> > solely on the DATE_COL. I do not think I can use MINUS due to the
> > column difference. And, this is going to a user, so cannot use PL/SQL
> > either.....
>
> SELECT *
> FROM table_a
> WHERE date_col NOT IN (SELECT date_col FROM table_b WHERE date_col IS NOT NULL)
>
> You are aware that the DATE datatype in Oracle contains a time part as well?
And yes you could use a minus since you would code the select on table_b to only select col1 and the date.
Thomas's comment mean if the time portion of the date is not significant (assuming the data was not stored with the time set to midnight for all rows in both tables) then you need to remove it from your comparison. See the SQL manual entry for TRUNC.
HTH -- Mark D Powell -- Received on Thu Jun 11 2009 - 12:38:10 CDT