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 -~----------~----~----~----~------~----~------~--~---
