Jim: Good article. Thank you very much.
Bruce > -------- Original Message -------- > Subject: [RBASE-L] - Re: Exploding BOM > From: James Bentley <[email protected]> > Date: Fri, March 16, 2012 9:48 am > To: [email protected] (RBASE-L Mailing List) > > > James B. > > You might find the following article helpful > http://en.wikipedia.org/wiki/Join_%28SQL%29 > > > Jim Bentley > American Celiac Society > [email protected] > tel: 1-504-737-3293 > > > >________________________________ > > From: Jim Belisle <[email protected]> > >To: RBASE-L Mailing List <[email protected]> > >Sent: Friday, March 16, 2012 10:42 AM > >Subject: [RBASE-L] - Re: Exploding BOM > > > >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 > >>> > >>> > >>> > >> > > > > > > > > > > > > > > > > > > > > > > > >
