- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Compare 2 records in Same Table
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-20-2006 09:02 AM
тАО07-20-2006 09:02 AM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-20-2006 10:01 AM
тАО07-20-2006 10:01 AM
Re: Compare 2 records in Same Table
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');
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-20-2006 01:14 PM
тАО07-20-2006 01:14 PM
Re: Compare 2 records in Same Table
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-20-2006 04:13 PM
тАО07-20-2006 04:13 PM
SolutionJust 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 -
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-20-2006 08:25 PM
тАО07-20-2006 08:25 PM
Re: Compare 2 records in Same Table
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" )