JAMES,
I have read some on the OUTER JOINS and INNER JOINS but still do not understand how to do them. I will add that to the list of code I need to study. James Belisle Making Information Systems People Friendly Since 1990 ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of James Bentley Sent: Friday, March 16, 2012 10:37 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Exploding BOM Here is the definition of a view illustrating nested joins from my production address database SET ERROR MESSAGES 677 OFF DROP VIEW RptVuMbrDtlDp SET ERROR MESSAGES 677 ON -- CREATE VIEW RptVuMbrDtlDp (FChrLastName,LastNameSortSeq,FirstName,MiddleName,+ LastName,FullNameLFM,MbrNumber,ChpNbrPfxWLtr,OrgChapLit,OrgChapLtrNbr,+ Salutation,PrsnTitle,DearBroGreet,NickName,SigOtherName,Birthday,Initiat ed,+ HighSchoolClassOf,HighSchoolName,CurHomeAudAction,CurHomeAudDate,+ CurHomeAudTime,CurWorkAudAction,CurWorkAudDate,CurWorkAudTime,DateOfDeat h,+ AcademicRecCnt,MbrStatus,RegisterChgDate,MailStatus,MAdrSource,FaceBookL it,+ LinkedInLit,HAdrSource,HPhoneNbr,HCellNbr,HExtraAdrLine,HMainAdrLine,+ HCityStateZip,HCountry,HomeChgDate,WAdrSource,WPhoneNbr,WFaxNbr,JobTitle ,+ CpyName,WExtraAdrLine,WMainAdrLine,WCityStateZip,WCountry,WorkChgDate,+ MbrCurEMailAdr,MbrAltEMailAdr,DegreeList,FathersNameLFM,EstAge) + AS SELECT FChrLastName,LastNameSortSeq,FirstName,MiddleName,LastName,+ FullNameLFM,MbrNumber,ChpNbrPfxWLtr,(IFLT(MbrNumber,0,'Org Chapter:',NULL)),+ (IFLT(MbrNumber,0,ChpNbrWLtr,NULL)),Salutation,PrsnTitle,DearBroGreet,+ NickName,SigOtherName,Birthday,Initiated,HighSchoolClassOf,HighSchoolNam e,+ CurHomeAudAction,CurHomeAudDate,CurHomeAudTime,CurWorkAudAction,+ CurWorkAudDate,CurWorkAudTime,DateOfDeath,AcademicRecCnt,MbrStatus,+ RegisterChgDate,MailStatus,MAdrSource,(SSUB('NO,Yes',1+FaceBookRecCnt)), + (SSUB('NO,Yes',1+LinkedInRecCnt)),HAdrSource,HPhoneNbr,HCellNbr,+ HExtraAdrLine,HMainAdrLine,(IFEXISTS(HCity,HCity+CHAR(32),NULL)+ + IFNULL(HStateUSPSCode,NULL,IFEQ(HStateUSPSCode,(CHAR(90)+ + CHAR(70)),NULL,HStateUSPSCode+ CHAR(32)))+ + IFEXISTS(HUSPSZipCode,HUSPSZipCode,NULL)),HCountry,HomeChgDate,WAdrSourc e,+ WPhoneNbr,WFaxNbr,JobTitle,CpyName,WExtraAdrLine,WMainAdrLine,+ (IFEXISTS(WCity,WCity+CHAR(32),NULL)+ IFNULL(WStateUSPSCode,NULL,+ IFEQ(WStateUSPSCode,(CHAR(90)+ CHAR(70)),NULL,WStateUSPSCode+CHAR(32)))+ + IFEXISTS(WUSPSZipCode,WUSPSZipCode,NULL)),WCountry,WorkChgDate,+ MbrCurEMailAdr,MbrAltEMailAdr,DegreeList,D.DadFullNameLFM,+ (FORMAT(IFNULL(DeathYear,INT((.#DATE - IFEXISTS(Birthday,Birthday,+ IFEXISTS(Initiated,ADDYR(Initiated,-19),.#DATE)))/365.25),DeathYear - + IYR4(Birthday)),AgeFmtMask)) FROM ((((((RegisterOfMbrs R LEFT OUTER + JOIN MbrCurHomeAdr H ON R.MbrNumber=H.MbrNumber) J1 LEFT OUTER + JOIN MbrCurWorkAdr W ON J1.MbrNumber=W.MbrNumber) J2 LEFT OUTER + JOIN MbrCurEmailInfo E ON J2.MbrNumber=E.MbrNumber) J3 LEFT OUTER + JOIN MbrAltEmailInfo A ON J3.MbrNumber=A.MbrNumber) J4 LEFT OUTER + JOIN AcademicAsList B ON J4.MbrNumber=B.MbrNumber) J5 INNER + JOIN DirHighSchools S ON J5.HighSchoolNbr=S.HighSchoolNbr) J6 LEFT OUTER + JOIN MbrFathersInfo D ON J6.MbrNumber = D.MbrNumber -- COMMENT ON VIEW RptVuMbrDtlDp IS 'RptVu Member details (duplex print version)' RETURN Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ________________________________ From: Victor Timmons <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Thursday, March 15, 2012 11:38 PM Subject: [RBASE-L] - Re: Exploding BOM I would like to see this in practice. On Thu, Mar 15, 2012 at 2:27 PM, James Bentley < [email protected]> wrote: You can also have multiple levels of nested INNER JOINS and OUTER JOINS. See Latest documentation under JOINS topic. Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ________________________________ From: Dan Goldberg <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Thursday, March 15, 2012 1:12 PM Subject: [RBASE-L] - Re: Exploding BOM You are correct. I am thinking of the Celko's nested sets is what is not possible. Dan -----Original Message----- From: Albert Berry Sent: Thursday, March 15, 2012 10:48 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Exploding BOM That's not quite true Dan, you can join one table to another. Select ParentID from Contacts t1, left outer join ChildID from Contacts t2 where t1.ParentID = t2.ParentID. Cannot try it right now - my system just ate my R:Base but I have done this sort of thing in the past. Albert On 15/03/2012 11:33 AM, Dan Goldberg wrote: > The problem is rbase cannot join tables to itself and does not recurse > itself.. That is why you have to use nested cursors. > Dan Goldberg > *From:* Ben Petersen <mailto:[email protected]> > *Sent:* Thursday, March 15, 2012 10:18 AM > *To:* RBASE-L Mailing List <mailto:[email protected]> > *Subject:* [RBASE-L] - Re: Exploding BOM > I looked a little further, this link is much better with examples and > code. > > http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ > > > > On Thu, Mar 15, 2012 at 9:36 AM, Ben Petersen <[email protected] > <mailto:[email protected]>> wrote: > > Karen, > > Take a look a "nested sets". > > http://en.wikipedia.org/wiki/Nested_set_model > http://www.sqlteam.com/article/more-trees-hierarchies-in-sql > > It takes a minute or two to wrap your head around, but is an > elegant solution (I think) to what you're proposing -- with > unlimited levels. No cursors, but could be write intensive if that > is a concern, though I wouldn't imagine any more than a cursor loop. > > Ben > > > > > > On Thu, Mar 15, 2012 at 8:55 AM, <[email protected] > <mailto:[email protected]>> wrote: > > Dan: That's the way I was going to go, a definite number of > cursors. I feel > better knowing that at least one person did it in that > manner! I'll give it a while > and if no one has code for unlimited levels, then I'll just go > with that. Thanks! > > Karen > > > In a message dated 3/15/2012 10:44:55 AM Central Daylight > Time, [email protected] <mailto:[email protected]> writes: >> >> I use cursors. I just set up for expected level of boms that >> we could possibly imagine using. We only go to a max of 6 >> levels down into the bom so I put in 8 cursor levels to just >> in case and put an error message if it finds something below >> 8 levels so I can add more someday. >> >> I know Rbase does not support celko's nested boms so I use >> cursors instead. >> >> Dan >> >> >> >
