I am researching controls and background information on Active X. Found a
utility that list the dll's associated to the active x and a bit shocked
on
the list. 10k plus dll's on my workstation Wow! But trying to narrow down
the ones that can be used and find what controls are available? Any
ideas?
So far I have a few working and they seem cool but what about the others?
Is
there any list that people have or would it behoove us to create a list of
Active X and what controls are available? Thinking out loud, but curious
also. Maybe create it off list (blog) and allow RList members too few the
lists?
Sincerely,
Paul D.
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Brad
Davidson
Sent: Friday, March 12, 2010 10:17 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Union view
Razzak,
Sure appreciate your time in making this detailed explanation of subject
inner/outer/full outer join, goes a long way in helping me understand its
usage!
Thanks, Bob, for asking the question.
Brad Davidson
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of A. Razzak
Memon
Sent: Friday, March 12, 2010 1:58 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Union view
At 10:32 AM 3/11/2010, Bob Thompson <[email protected]> wrote:
I am not as familar with INNER/OUTER joins but will give it a try.
Bob,
In addition to great suggestions you have already received, here
are a few examples that should get you started and understand
the ABC's of INNER, OUTER, and NESTED INNER JOINS in R:BASE.
A regular join includes the rows that match between two tables.
Matching rows are determined by the linking column, a column
that is common to the two tables. When the tables are joined,
rows that have matching data in the linking column are included
in the result, other rows are ignored. An outer join includes
the rows that don't have matching values.
The outer join designation is an additional option to the FROM
clause of the SELECT command.
SELECT...FROM lefttblview T1 LEFT OUTER JOIN righttblview T2
RIGHT
FULL
ON T1.column1 = T2.column2
The type of outer join (LEFT, RIGHT or FULL) goes between the
table names in the FROM clause, the keyword ON precedes the
linking columns. You can add an additional WHERE clause that
further qualifies the rows to be selected, as well as using
the GROUP BY, HAVING and ORDER BY clauses of SELECT.
LEFT OUTER JOIN:
Includes the matching rows between table one (T1) and table
two (T2), and rows from table one that don't have a match in
table two. The rows from table one that don't have a match
in table two are NULL filled for the table two columns. Table
one is the first table referenced in the FROM clause, the
table on the "left".
RIGHT OUTER JOIN:
Includes the matching rows between table two (T2) and table
one (T1), and rows in table two that don't have a match in
table one. The rows in table two that don't have a match in
table one are null filled for the table one columns. Table
two is the second table referenced in the FROM clause, the
table on the "right".
The use of LEFT or RIGHT depends the order you list the
tables and from which table you want the rows that don't
match. For example,
... FROM table1 LEFT OUTER JOIN table2 ...
includes the rows from table1 that aren't in table2
... FROM table1 RIGHT OUTER JOIN table2 ...
includes the rows from table2 that aren't in table1.
... FROM table2 RIGHT OUTER JOIN table1 ...
is the same as
... FROM table1 LEFT OUTER JOIN table2 ...
You can change from RIGHT to LEFT by simply changing the
tables instead of changing the OUTER JOIN specification.
... FROM table2 RIGHT OUTER JOIN table1 ...
does a right outer join using table1, it returns all the
rows from table1 and matching rows from table2.
... FROM table1 RIGHT OUTER JOIN table2 ...
does a right outer join using table2, it returns all the
rows from table2 and matching rows only from table1. It
is the same as
... FROM table2 LEFT OUTER JOIN table1 ...
Make sure your tables are in the right order to select the
desired rows.
FULL OUTER JOIN:
Includes all the rows from both tables, the rows that don't
match are null filled. There will be rows with data from
both tables, rows with data from table one and null for the
table two columns and rows with data from table two and null
for the columns from table one. This gives the same result
as the R:BASE UNION command, but when used in a view, a
permanent table and its corresponding data is not created
in the database.
An advantage with this new syntax over creating an outer
join using the UNION operator with SELECT is you don't get
"unnamed" columns in the result. You won't need to remember
to define column names when you create your view.
The syntax limits you to creating an outer join using only
two tables at a time. Create a three table join by creating
a view with one two-table outer join, then using that view
in a second outer join. You need to be sure that the linking
column for the second join is not on the outer join side of
the first join, i.e. it is not null filled.
For example, to link the InvoiceHeader, Customer and Employee
tables from the RRBYW16 sample database, first create a view
joining InvoiceHeader and Employee using a left outer join.
This includes rows in the InvoiceHeader table that are not
in the Employee table. We can't include rows from Employee
that are not in InvoiceHeader because that fills the linking
column between InvoiceHeader and Customer, CustID, with NULLs.
Example:
CONNECT RRBYW16 IDENTIFIED BY NONE
SET ERROR MESSAGE 677 OFF
DROP VIEW View1
SET ERROR MESSAGE 677 ON
CREATE VIEW View1 +
(TransID,TransDate,InvoiceTotal,CustID,EmpID,EmpFName,EmpLName) +
AS SELECT +
t1.TransID,t1.TransDate,t1.InvoiceTotal,t1.CustID,t1.EmpID, +
t2.EmpFName,t2.EmpLName +
FROM InvoiceHeader t1 +
LEFT OUTER JOIN Employee t2 ON t1.EmpID = T2.EmpID
BROWSE * FROM View1
RETURN
This view returns all the rows that are in both InvoiceHeader and
Employee, and the rows in InvoiceHeader that do not have a matching
Employee ID Number, i.e. are not in Employee. It is important to
structure the outer join this way. You do not include rows from
Employee that aren't in InvoiceHeader, that would return NULLS for
the CustID column in InvoiceHeader, the column that is used to link
with the Customer table.
Then, create the second view. This view will join the data selected
by the first view with the data from the Customer table. In our
final result, we want Customer rows that don't have a matching
transaction and any of the transaction records that don't have a
matching Customer -- we use a full outer join. We didn't use a full
outer join in the first view because we wanted to be sure that the
CustID column, our link to the customer table, always contains a
value.
CONNECT RRBYW16 IDENTIFIED BY NONE
SET ERROR MESSAGE 677 OFF
DROP VIEW View2
SET ERROR MESSAGE 677 ON
CREATE VIEW View2 AS SELECT t1.*, t2.* FROM View1 t1 +
FULL OUTER JOIN Customer t2 ON t1.CustID = t2.CustID
The resulting data that results from this second view combines all
the rows from the first view joining InvoiceHeader and Employee
with rows from the Customer table. In other words, this view
includes all the rows from the first view, View1, the outer join
between InvoiceHeader and Employee, that have matching Customer
information, the rows from the Customer table that have no
transactions, and the rows from view1 that have no customer
information.
Examples of INNER JOINS and NESTED INNER JOINS:
-- Example 01
CONNECT RRBYW16 IDENTIFIED BY NONE
BROWSE t1.EmpLName,t1.EmpFName,(SUM(t1.Price))=S +
FROM ((Employee t2 INNER JOIN InvoiceHeader t3 ON t2.EmpID = t3.EmpID) t5
+
INNER JOIN InvoiceDetail t4 on t4.TransID = t5.TransID) t1 +
GROUP BY t1.EmpLName,t1.EmpFName OPTION CAPTION Example 01
-- Example 02
CONNECT RRBYW16 IDENTIFIED BY NONE
BROWSE (t1.EmpLName+','&t1.EmpFName)=27 AS Employee_Name,(SUM(t1.Price))=S
+
AS Total_Sale +
FROM ((Employee t2 INNER JOIN InvoiceHeader t3 ON t2.EmpID = t3.EmpID) t5
+
INNER JOIN InvoiceDetail t4 on t4.TransID = t5.TransID) t1 +
GROUP BY t1.EmpLName,t1.EmpFName OPTION CAPTION Example 02
-- Example 03
CONNECT RRBYW16 IDENTIFIED BY NONE
SET ERROR MESSAGE 677 OFF
DROP VIEW tTotSaleByEmployee
SET ERROR MESSAGE 677 ON
CREATE TEMPORARY VIEW `tTotSaleByEmployee` (Employee_Name, Total_Sale) AS
+
SELECT (t1.EmpLName+','&t1.EmpFName)=27,(SUM(t1.Price))=S +
FROM ((Employee t2 INNER JOIN InvoiceHeader t3 ON t2.EmpID = t3.EmpID) t5
+
INNER JOIN InvoiceDetail t4 on t4.TransID = t5.TransID) t1 +
GROUP BY t1.EmpLName,t1.EmpFName
BROWSE * FROM tTotSaleByEmployee OPTION CAPTION Example 03
RETURN
Hope that helps!
Very Best R:egards,
Razzak.
