Jim/Larry:
"Ijole" (eee-ho-lay) is a handy 'exclamacion en espanol' for moments such as this.
Took it. Worked it. Created an INNER JOINed (J1,J2) view 'vwfablist':
--CREATE_VIEW_VWFABLIST
--LEGEND:
--table: sot1288partorder
--table: partdesc
--perm view: vwfabmethseq
--CODE:
DROP VIEW vwfablist
CREATE VIEW vwfablist +
AS SELECT +
ALL +
FROM +
(( +
so51288partorder t1 +
INNER JOIN +
partdesc t2 ON t1.partno=t2.partno +
) J1 +
INNER JOIN +
vwfabmethseq t3 ON t3.partno=j1.partno +
) j2 +
WHERE +
j2.fabsequence like '1'
RETURN
CREATE VIEW vwfablist +
AS SELECT +
ALL +
FROM +
(( +
so51288partorder t1 +
INNER JOIN +
partdesc t2 ON t1.partno=t2.partno +
) J1 +
INNER JOIN +
vwfabmethseq t3 ON t3.partno=j1.partno +
) j2 +
WHERE +
j2.fabsequence like '1'
RETURN
Database Explorer shows vwfablist; but this CANNOT be browsed. Report Wizard fingers 'ALL' when it chokes out: 'Duplicate field name 'partno''; this explaining a 'plus-2' column count. I need a 'kitchen sink' view here. So, questions:
A) Must I declare columns from each of the three tables/views individually below the 'AS SELECT'?
B) Will the 'Jx.' alias tags work within that listing?
C) When creating feedstock views (e.g. 'vwfabmethseq') are column aliases mandatory to avoid presenting 'tx.colname' column names to this procedure?
Exciting stuff. How do I thank you guys?
bruce chitiea
safesectors inc
> -------- Original Message --------
> Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables?
> From: James Bentley <[email protected]>
> Date: Thu, November 18, 2010 5:04 am
> To: [email protected] (RBASE-L Mailing List)
>
>
> Larry,
>
> According to my records nested inner joins were introduced as early as Feb 2008
> as a sample RMD. Some time later the ability to nest all join types were added
> by never formally introduced or documented.
>
>
> I agree it was a great addition as it allowed my to simplify many of my views as
> you can see from my example I have a master table "RegisterofMbrs" with a slew
> of sub-tables most of which may not have record(s) for a given member. Hence the
> need for LEFT OUTER JOIN. Formally this required have individual views to create
> each OUTER JOIN and then a final view to mesh all of the outer join views. Now
> I have just the one view.
>
> Jim Bentley
> American Celiac Society
> [email protected]
> tel: 1-504-737-3293
>
>
> >
> >From: Lawrence Lustig <[email protected]>
> >To: RBASE-L Mailing List <[email protected]>
> >Sent: Wed, November 17, 2010 9:58:09 PM
> >Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables?
> >
> >
> >HOLY FRIJOLES!
> >
> >
> >This is the single biggest improvement in the R:Base engine since I can
> >remember, and I haven't seen it mentioned ANYWHERE (except your post). I just
> >checked WhatsNewInRBase76ForWindows.PDF, and couldn't find it in there. I had
> >no idea that R:Base supported SQL-92 JOIN syntax!
> >
> >
> >--
> >Larry
> >
> >
> >
> ________________________________
> From: James Bentley <[email protected]>
> >To: RBASE-L Mailing List <[email protected]>
> >Sent: Wed, November 17, 2010 7:43:43 PM
> >Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables?
> >
> >Bruce,
> >
> >You are correct I had problems in cutting the data from working code. Was
> >hurrying to go to class and took a shortcut. Here is the corrected info:
> >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 AcademicAsList A ON
> >J2.MbrNumber=A.MbrNumber) J3 LEFT OUTER JOIN DirHighSchools S ON
> >J3.HighSchoolNbr=S.HighSchoolNbr) J4 LEFT OUTER JOIN MbrFromChapter F ON
> >J4.MbrNumber=F.MbrNumber) J5 LEFT OUTER JOIN MbrFathersInfo D ON J5.MbrNumber =
>
> >D.MbrNumber
> >
> >Hope you were able to correctly figure out. The key is to match the
> >parenthesis. Also you can mix the nesting of join types. In my case it is LEFT
> >
> >OUTER JOIN.
> >
> >
> >Jim Bentley
> >American Celiac Society
> >[email protected]
> >tel: 1-504-737-3293
> >
> >
> >
> >----- Original Message ----
> >> From: Bruce Chitiea <[email protected]>
> >> To: RBASE-L Mailing List <[email protected]>
> >> Sent: Wed, November 17, 2010 2:49:20 PM
> >> Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables?
> >>
> >> Larry, Bill, Jim:
> >>
> >> Thanks much: powerful stuff.
> >>
> >> Jim: Here's the example, abstracted:
> >>
> >> FROM +
> >> ( ( ( ( ( +
> >> tableview1 t1 +
> >>
> >> INNER JOIN +
> >> tableview2 t2 +
> >> ON t1.keycolumn=t2.keycolumn +
> >> ) J1 +
> >>
> >> INNER JOIN +
> >> tableview3 t3 +
> >> ON J1.keycolumn=t3.keycolumn +
> >> ) J2 +
> >>
> >> INNER JOIN +
> >> tableview4 t4 +
> >> ON J2.keycolumn=t4.keycolumn +
> >> ) J3 +
> >>
> >> INNER JOIN +
> >> tableview5 t5 +
> >> ON J3.keycolumn=t5.keycolumn
> >>
> >> I'm assuming that this was cut from working code, skewing the number of
> >> open/close parens. If this were stand-alone, would I open with:
> >>
> >> FROM +
> >> ( ( ( ( +
> >>
> >> and close with:
> >>
> >> ON J3.keycolumn=t5.keycolumn +
> >> ) J4
> >>
> >>
> >> Yours,
> >>
> >> bruce chitiea
> >> safesectors inc
> >>
> >> > -------- Original Message --------
> >> > Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables?
> >> > From: James Bentley <[email protected]>
> >> > Date: Wed, November 17, 2010 11:50 am
> >> > To: [email protected] (RBASE-L Mailing List)
> >> >
> >> >
> >> > Better still RBase supports nested INNER and OUTER JOINS. Try following
> >>syntax;
> >> >
> >> > FROM (((((RegisterOfMbrs R INNER +
> >> > JOIN MbrCurHomeAdr H ON R.MbrNumber=H.MbrNumber) J1 INNER +
> >> > JOIN MbrCurWorkAdr W ON J1.MbrNumber=W.MbrNumber) J2 INNER +
> >> > JOIN AcademicAsList A ON J2.MbrNumber=A.MbrNumber) J3 INNER +
> >> > JOIN DirHighSchools S ON J3.HighSchoolNbr=S.HighSchoolNbr
> >> >
> >> > You can also nest a mixture of INNER JOINS, LEFT OUTER JOIN, RIGHT OUTER
> >>JOIN,
> >>
> >> > and FULL OUTER JOIN. The key is proper use of Parentheses. This syntax
> >> > available in v7.6 (Windows & DOS) and higher.
> >> >
> >> >
> >> > Jim Bentley
> >> > American Celiac Society
> >> > [email protected]
> >> > tel: 1-504-737-3293
> >> >
> >> >
> >> > >
> >> > >From: Lawrence Lustig <[email protected]>
> >> > >To: RBASE-L Mailing List <[email protected]>
> >> > >Sent: Tue, November 16, 2010 1:21:50 PM
> >> > >Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables?
> >> > >
> >> > >
> >> > >You can perform multiple INNER JOINs in a single statement using the older
> >
> >
> >> > >SQL-89 syntax:
> >> > >
> >> > >
> >> > >SELECT * FROM table1, table2, table3 +
> >> > > WHERE table1.colX = table2.colY AND table2.colA = table3.colB
> >> > >
> >> > >
> >> > >Unfortunately, this works only for implicit INNER JOINs in the older
> >>syntax.
> >>
> >> > > For SQL-92 syntax (which is the only way to do OUTER JOINs) you are
> >>limited to
> >>
> >> > >2 tables per SELECT statement. The work-around there is to use nested
> >>views as
> >>
> >> > >described in your message.
> >> > >--
> >> > >Larry
> >>
> >>
> >>
> >
> >
> >
> >
> >
> >
> Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables?
> From: James Bentley <[email protected]>
> Date: Thu, November 18, 2010 5:04 am
> To: [email protected] (RBASE-L Mailing List)
>
>
> Larry,
>
> According to my records nested inner joins were introduced as early as Feb 2008
> as a sample RMD. Some time later the ability to nest all join types were added
> by never formally introduced or documented.
>
>
> I agree it was a great addition as it allowed my to simplify many of my views as
> you can see from my example I have a master table "RegisterofMbrs" with a slew
> of sub-tables most of which may not have record(s) for a given member. Hence the
> need for LEFT OUTER JOIN. Formally this required have individual views to create
> each OUTER JOIN and then a final view to mesh all of the outer join views. Now
> I have just the one view.
>
> Jim Bentley
> American Celiac Society
> [email protected]
> tel: 1-504-737-3293
>
>
> >
> >From: Lawrence Lustig <[email protected]>
> >To: RBASE-L Mailing List <[email protected]>
> >Sent: Wed, November 17, 2010 9:58:09 PM
> >Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables?
> >
> >
> >HOLY FRIJOLES!
> >
> >
> >This is the single biggest improvement in the R:Base engine since I can
> >remember, and I haven't seen it mentioned ANYWHERE (except your post). I just
> >checked WhatsNewInRBase76ForWindows.PDF, and couldn't find it in there. I had
> >no idea that R:Base supported SQL-92 JOIN syntax!
> >
> >
> >--
> >Larry
> >
> >
> >
> ________________________________
> From: James Bentley <[email protected]>
> >To: RBASE-L Mailing List <[email protected]>
> >Sent: Wed, November 17, 2010 7:43:43 PM
> >Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables?
> >
> >Bruce,
> >
> >You are correct I had problems in cutting the data from working code. Was
> >hurrying to go to class and took a shortcut. Here is the corrected info:
> >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 AcademicAsList A ON
> >J2.MbrNumber=A.MbrNumber) J3 LEFT OUTER JOIN DirHighSchools S ON
> >J3.HighSchoolNbr=S.HighSchoolNbr) J4 LEFT OUTER JOIN MbrFromChapter F ON
> >J4.MbrNumber=F.MbrNumber) J5 LEFT OUTER JOIN MbrFathersInfo D ON J5.MbrNumber =
>
> >D.MbrNumber
> >
> >Hope you were able to correctly figure out. The key is to match the
> >parenthesis. Also you can mix the nesting of join types. In my case it is LEFT
> >
> >OUTER JOIN.
> >
> >
> >Jim Bentley
> >American Celiac Society
> >[email protected]
> >tel: 1-504-737-3293
> >
> >
> >
> >----- Original Message ----
> >> From: Bruce Chitiea <[email protected]>
> >> To: RBASE-L Mailing List <[email protected]>
> >> Sent: Wed, November 17, 2010 2:49:20 PM
> >> Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables?
> >>
> >> Larry, Bill, Jim:
> >>
> >> Thanks much: powerful stuff.
> >>
> >> Jim: Here's the example, abstracted:
> >>
> >> FROM +
> >> ( ( ( ( ( +
> >> tableview1 t1 +
> >>
> >> INNER JOIN +
> >> tableview2 t2 +
> >> ON t1.keycolumn=t2.keycolumn +
> >> ) J1 +
> >>
> >> INNER JOIN +
> >> tableview3 t3 +
> >> ON J1.keycolumn=t3.keycolumn +
> >> ) J2 +
> >>
> >> INNER JOIN +
> >> tableview4 t4 +
> >> ON J2.keycolumn=t4.keycolumn +
> >> ) J3 +
> >>
> >> INNER JOIN +
> >> tableview5 t5 +
> >> ON J3.keycolumn=t5.keycolumn
> >>
> >> I'm assuming that this was cut from working code, skewing the number of
> >> open/close parens. If this were stand-alone, would I open with:
> >>
> >> FROM +
> >> ( ( ( ( +
> >>
> >> and close with:
> >>
> >> ON J3.keycolumn=t5.keycolumn +
> >> ) J4
> >>
> >>
> >> Yours,
> >>
> >> bruce chitiea
> >> safesectors inc
> >>
> >> > -------- Original Message --------
> >> > Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables?
> >> > From: James Bentley <[email protected]>
> >> > Date: Wed, November 17, 2010 11:50 am
> >> > To: [email protected] (RBASE-L Mailing List)
> >> >
> >> >
> >> > Better still RBase supports nested INNER and OUTER JOINS. Try following
> >>syntax;
> >> >
> >> > FROM (((((RegisterOfMbrs R INNER +
> >> > JOIN MbrCurHomeAdr H ON R.MbrNumber=H.MbrNumber) J1 INNER +
> >> > JOIN MbrCurWorkAdr W ON J1.MbrNumber=W.MbrNumber) J2 INNER +
> >> > JOIN AcademicAsList A ON J2.MbrNumber=A.MbrNumber) J3 INNER +
> >> > JOIN DirHighSchools S ON J3.HighSchoolNbr=S.HighSchoolNbr
> >> >
> >> > You can also nest a mixture of INNER JOINS, LEFT OUTER JOIN, RIGHT OUTER
> >>JOIN,
> >>
> >> > and FULL OUTER JOIN. The key is proper use of Parentheses. This syntax
> >> > available in v7.6 (Windows & DOS) and higher.
> >> >
> >> >
> >> > Jim Bentley
> >> > American Celiac Society
> >> > [email protected]
> >> > tel: 1-504-737-3293
> >> >
> >> >
> >> > >
> >> > >From: Lawrence Lustig <[email protected]>
> >> > >To: RBASE-L Mailing List <[email protected]>
> >> > >Sent: Tue, November 16, 2010 1:21:50 PM
> >> > >Subject: [RBASE-L] - Re: Inner Join: Joining 3 or more tables?
> >> > >
> >> > >
> >> > >You can perform multiple INNER JOINs in a single statement using the older
> >
> >
> >> > >SQL-89 syntax:
> >> > >
> >> > >
> >> > >SELECT * FROM table1, table2, table3 +
> >> > > WHERE table1.colX = table2.colY AND table2.colA = table3.colB
> >> > >
> >> > >
> >> > >Unfortunately, this works only for implicit INNER JOINs in the older
> >>syntax.
> >>
> >> > > For SQL-92 syntax (which is the only way to do OUTER JOINs) you are
> >>limited to
> >>
> >> > >2 tables per SELECT statement. The work-around there is to use nested
> >>views as
> >>
> >> > >described in your message.
> >> > >--
> >> > >Larry
> >>
> >>
> >>
> >
> >
> >
> >
> >
> >
