topic Re: Compare 2 records in Same Table in Operating System - HP-UX

Compare 2 records in Same Table

intp — Thu, 20 Jul 2006 16:02:02 GMT

Hi,

I have the following data in table A.
Acnt key , recrd type , ref accnt key
1000 , F, Null
2000 , F, Null
3000,N, 2000
4000 C 1000

i want to fetch records those Acnt key column not referred in
ref accnt key column of any other record whose type is 'N'. In above case i need to eliminate 2000,F,Null because 2000(accnt key) is referred in another record's 3000(accnt key) ref accnt key column whose record type is 'N'

Thanks

Re: Compare 2 records in Same Table

TwoProc — Thu, 20 Jul 2006 17:01:24 GMT

I've not tested this but just a
set operation should do the trick quickly.
If you work out the possible syntax errors, it should do the trick for you.

select tba.Acntkey
from tbla tba
minus
(select tbb.refaccntky as Acntkey
from tbla tbb
where tbb.recrdtype = 'N');

Re: Compare 2 records in Same Table

James R. Ferguson — Thu, 20 Jul 2006 20:14:12 GMT

Hi:

The Perl script below will accept piped input or will read a file of your data. It is assumed that your fields are comma-delimited, like:

1000,F,Null
2000,F,Null
3000,N,2000
4000,C,1000

# cat ./filter
#!/usr/bin/perl
use strict;
use warnings;
my %things;
my (@fld, @zot);
my $key;
while (<>) {
chomp;
@fld = split /,/, $_;
$things{$fld[0]} = $_;
push @zot, $fld[2] if $fld[1] =~/N/;
}
for $key (@zot) {
delete $things{$key};
}
for $key (sort keys %things) {
print "$things{$key}\n";
}
1;

...run as :

# ./filter datafile

(or):

# echo "1000,F,Null\n2000,F,Null\n3000,N,2000\n4000,C,1000" | ./filter

Regards!

...JRF...

Re: Compare 2 records in Same Table

Hein van den Heuvel — Thu, 20 Jul 2006 23:13:50 GMT

Jrf, you know I like to suggest a perl solution for just about anything, but this problem seems to refers to a (relational) database table, asking for relational logic.
Just using sql would appear to be called for.

Intp, Are you sure you have your 'nots' and 'eliminate' versus 'fetch' straight in the problem description?

At any rate, it should be something along the lines of:

select * from a a where exists (select 'x' from a b where b.ref_key = a.acnt_key and b.type = 'N');'

Demo below.
Hth,
Hein.

create table A (acnt_key number(9), type char, ref_key number(9));
insert into a values (1000,'F',null);
:
:
insert into a values (4000,'C', 1000);

select * from a order by acnt_key

"ACNT_KEY","TYPE","REF_KEY"
"1000","F",""
"2000","F",""
"3000","N","2000"
"4000","C","1000"

select * from a a
where exists (
select 'x'
from a b
where a.acnt_key = b.ref_key
and b.type = 'N');'

ACNT_KEY TYPE REF_KEY
2000 F -

Re: Compare 2 records in Same Table

Steve Lewis — Fri, 21 Jul 2006 03:25:23 GMT

How about this single SQL:

select Acnt_key from table_A A
where not exists (
select 1 from table_A B
where B.ref_acnt_key = A.acnt_key
and B.recrd_type = "N" )