Did you try the solution I gave? I think it will work for you. Basically it say "pick all of the columns from the membermaster table if there is no corresponding record in the memberdtl table that has a mbrfee greater than 0"
Been a long time since I coded COBOL. Regards, Mike On Thu, Oct 2, 2008 at 3:59 AM, j <[EMAIL PROTECTED]> wrote: > > Thanks for all the responses. I was only thinking of eof because this > program is written in Cobol and I wanted to try the logic in sql. The > object is to throw out any mbrno that has a mbrsep with a mbrfee > 0. > Mbrno 12345678 has 5 mbrseps > Mbrsep 1234567801 mbrfee = 0 > Mbrsep 1234567802 mbrfee = 0 > Mbrsep 1234567803 mbrfee = 500 > Mbrsep 1234567804 mbrfee = 0 > Mbrsep 1234567805 mbrfee = 0 > > The program(Cobol) reads thru and sees that the third mbrsep has a > mbrfee so it skips it and goes back and reads the next mbrno from the > master > > Mbrno 12345689 has 3 mbrseps > mbrsep 1234568901 mbrfee = 0 > mbrsep 1234568902 mbrfee = 0 > mbrsep 1234568903 mbrfee = 0 > > It would write this record to the sort file and at end reads sort and > prints a list. Sorry, I guess I should have made this more clear > yesterday. I ran the program and got a list and I was trying to find > the correct query in sql to see if I got the same totals. > > > On Oct 1, 6:50 pm, "Michael Moore" <[EMAIL PROTECTED]> wrote: > > "well one of us is right ... or both wrong .. or something else" > > I think that pretty much covers all the bases. :-) can we go home now? > > > > > > > > On Wed, Oct 1, 2008 at 3:21 PM, rob wolfe <[EMAIL PROTECTED]> > wrote: > > > > > well one of us is right ... or both wrong .. or something else > > > i wasnt sure if only needed the stuff from A or if there was something > > > else from B was needed so i just (as per usual) decided to dump the > > > pair of tables ... I am such a messy coder some days > > > > > Michael Moore wrote: > > > > 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] > > > > <mailto:[EMAIL PROTECTED]>> wrote: > > > > > > On Oct 1, 5:14 pm, j <[EMAIL PROTECTED] > > > > <mailto:[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.- Hide quoted text - > > > > - Show quoted text - > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
