Prev: LONG to CLOB within a trigger - alternative to synching data values?
Next: How you can save fuel and the environment
From: jimmyb on 8 Jan 2010 18:02
I'm trying to find the records in Table A, that are not in Table B. So
I thought the Minus set operator would be a good tool to use.

select count(*) from tbl_A ;
-- 21265 rows

select a.person_id
from tbl_A a
minus
select b.person_id
from tbl_B b
-- 1399 rows

So far that looks good. Now I want to see the date the records in
Table A were created, but this column does not exist in Table B; so I
add a null value to Table B - like this.

select a.person_id, a.creation_date
from tbl_A a
minus
select b.person_id, to_date(null) "creation_date"
from tbl_B b
-- 21247 rows

Am I missing something? I thought you replaced missing columns with
null values cast to the corresponding data type. No?
From: vsevolod afanassiev on 8 Jan 2010 18:55
The "minus" operator should be applied to "person_id" column and you
are applying it to both "person_id" and "creation_date", this is the
reason for getting 21247 records instead of 1399.

Since tables are small performance shouldn't be an issue, so why not
use

select a.person_id, a.creation_date
from tbl_A a
where
a.person_id not in
(select b.person_id from tbl_B b);




From: jimmyb on 8 Jan 2010 19:07
On Jan 8, 3:55 pm, vsevolod afanassiev <vsevolod.afanass...(a)gmail.com>
wrote:
> The "minus" operator should be applied to "person_id" column and you
> are applying it to both "person_id" and "creation_date", this is the
> reason for getting 21247 records instead of 1399.
>
> Since tables are small performance shouldn't be an issue, so why not
> use
>
> select a.person_id, a.creation_date
> from tbl_A a
> where
> a.person_id not in
> (select b.person_id from tbl_B b);

Actually, that is what I ended doing, except I used not exists...and
got the results I wanted.

I'm guessing the MINUS operator works differently than a UNION when
you having missing columns from one of the tables.
From: Malcolm Dew-Jones on 8 Jan 2010 18:17
jimmyb (jimmybrock(a)gmail.com) wrote:
: I'm trying to find the records in Table A, that are not in Table B. So
: I thought the Minus set operator would be a good tool to use.

: select count(*) from tbl_A ;
: -- 21265 rows

: select a.person_id
: from tbl_A a
: minus
: select b.person_id
: from tbl_B b
: -- 1399 rows

: So far that looks good. Now I want to see the date the records in
: Table A were created, but this column does not exist in Table B; so I
: add a null value to Table B - like this.

: select a.person_id, a.creation_date
: from tbl_A a
: minus
: select b.person_id, to_date(null) "creation_date"
: from tbl_B b
: -- 21247 rows

: Am I missing something? I thought you replaced missing columns with
: null values cast to the corresponding data type. No?

E.g. set A
x.no dates y.with dates
( 1) | ( 1, 1-jan-2000 )
( 2) | ( 2, 23-feb-1999)
minus set B |
( 2) | ( 2, null )
gives |
( 1) | ( 1, 1-jan-2000 )
| ( 2, 23-feb-1999)

In the second half of the example, set A.y does not contain ( 2, null ),
so minus'ing it does not remove anything. That shows why your second
query returns more rows. To use minus, I would do something like the
following

select id, creation_date
from tbl_A
where person_id
in
( select a.person_id
from tbl_A a
minus
select b.person_id
from tbl_B b
)


From: Nevin Hahn on 11 Jan 2010 00:59
On solution is :

select a1.person_id,a1.creation_date
from tbl_A a1
where a1.person_id in (
select a.person_id
from tbl_A a
minus
select b.person_id
from tbl_B b
)

Thanks

"jimmyb" <jimmybrock(a)gmail.com> wrote in message
news:de4e69fb-cf08-4af4-a7e7-4ec829c1f29e(a)34g2000yqp.googlegroups.com...
> I'm trying to find the records in Table A, that are not in Table B. So
> I thought the Minus set operator would be a good tool to use.
>
> select count(*) from tbl_A ;
> -- 21265 rows
>
> select a.person_id
> from tbl_A a
> minus
> select b.person_id
> from tbl_B b
> -- 1399 rows
>
> So far that looks good. Now I want to see the date the records in
> Table A were created, but this column does not exist in Table B; so I
> add a null value to Table B - like this.
>
> select a.person_id, a.creation_date
> from tbl_A a
> minus
> select b.person_id, to_date(null) "creation_date"
> from tbl_B b
> -- 21247 rows
>
> Am I missing something? I thought you replaced missing columns with
> null values cast to the corresponding data type. No?

 | 
Pages: 1
Prev: LONG to CLOB within a trigger - alternative to synching data values?
Next: How you can save fuel and the environment