At 12:39 PM 11/6/2014, Bob Thompson wrote:
Can a single table view with an added autonum column be created?
Syntax?
Bob,
Yes, you CAN define a Virtual Auto-Number in Views.
Here's how ...
Using the sample Running R:BASE Your Way! Part 18 (RRBYW18) database,
follow these steps to understand and create a VIEW with row counter
(auto-number).
01. Launch R:BASE eXtreme 9.5 (32/64)
02. CONNECT RRBYW18
03. Using the built-in R:BASE Editor, create a procedure to allow
incremental number and then save the file as File01.RMD
Example:
-- File01.RMD
SET VAR vNum INTEGER = (IFNULL(.vNum,1,(.vNum+1)))
RETURN .vNum
04. Use the PUT command to Store the above Procedure at the R> prompt:
PUT File01.RMD AS AutoNumView 'Power of R:BASE'
05. Create a VIEW to display Virtual AutoNumbers
Example:
-- File02.RMD
SET ERROR MESSAGE 677 OFF
DROP VIEW VirtualNumbView
SET ERROR MESSAGE 677 ON
CREATE VIEW `VirtualNumbView` +
(AutoCol,EmpLastName,EmpFirstName) +
AS SELECT (Call AutoNumView()),EmpLName,EmpFName +
FROM Employee
RETURN
06. Now see the Virtual AutoNumbers in View!
At the R> prompt:
CLEAR ALL VAR
SELECT * FROM VirtualNumbView
Notice the results.
07. Keep in mind that the last value of AutoCol is returned as nn.
08. So if you try the same SELECT command again, you will get the
results with incremental number.
SELECT * FROM VirtualNumbView
09. If you would like the starting number be 1001, then:
CLEAR VARIABLE vNum
SET VAR vNum INTEGER = 1000
SELECT * FROM VirtualNumbView
There you have the Virtual Auto-Number in Views!
How simple and yet very powerful methodology.
Once you understand the concept of this exercise, you may adapt
this technique as you see fit.
Have fun!
Very Best R:egards,
Razzak
www.rbase.com
www.facebook.com/rbase
--
31 years of continuous innovation!
16 Years of R:BASE Technologies, Inc. making R:BASE what it is today!
