based on my understanding of the problem
select * from membermaster a
where not exists (select 1
from Memberdetl b
where a.mbrno = substr( b.mbrsep,1,8)
and mbrfee > 0);
Mike
On Wed, Oct 1, 2008 at 2:43 PM, Rob Wolfe <[EMAIL PROTECTED]> wrote:
>
> On Oct 1, 5:14 pm, j <[EMAIL PROTECTED]> wrote:
> > I was hoping I could get some help with a query. I have a read of a
> > membermaster file (table a) and it uses the key member to read table
> > b (memberdetl). The key is mbrno varchar 8
> > Memberdetl also has a field called mbrsep which is varchar 10 the
> > first 8 characters are mbrno and the last 2 are the seperator. For
> > instance:
> >
> > Membermaster-mbrno 12345678 = Memberdetl-mbrno 12345678. but
> > memberdetl-mbrsep can =
> > 123567801, 1234567802, 1234567803 etc. I want to read thru all the
> > mbrseps and if any have a mbrfee > 0 I want to throw that record
> > away and go read the next table A record until eof
>
> and this version of it makes more sense than the one I just
> answered ...
>
> select a.*,b.*
> from membermaster a
> join Memberdetl b on a.mbrno = substr(b.mbrsep,1,8)
> where nvl(b.mbrfee,0) = 0
>
> the nvl is just in case you can have an empty fee field, you dont want
> to leave them out .. or maybe you do .. i just assumed you didnt
>
> you might or might not want a distinct in there as well ... depends on
> what your data looks like
>
> This is actually not that difficult a problem (meaning that it is the
> kind of thing that you have to do all the time - and of course
> assuming that I didnt misread it completely - which does happen) and
> if you are thinking about EOF's you are REALLY on the wrong track.
> With Oracle databases, or any relational databases for that matter you
> should always think in terms of sets of data as a first step. Don't
> make the mistake of treating a database as just a bunch of flat files
> because it just isnt the case.
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
