Razzak Your "From the Edge" series is terrific. I learn something from every one of them. I hope you're collecting them for a book!
David Blocker ----- Original Message ----- From: "A. Razzak Memon" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, November 19, 2001 12:18 AM Subject: From The Edge: Understanding Dotted vs. Ampersand Variables > > November 19, 2001 > > ==================================================================== > From the Edge: Understanding Dotted vs. Ampersand Variables > Section: Variables > Chapter: Running R:BASE Your Way! > Platform: R:BASE 2000 (ver 6.5++) for DOS/Windows > Build: 1.842xRT03 and Higher > ==================================================================== > > Dotted Variables: > ---------------- > > 01. Variables used to hold values. > > 02. When you want R:BASE to use the "value" contained in > the variable. > > 03. When you use a variable in a calculation or as a > comparision value, you use "dot" variable. > > 04. A rule of thumb for when to "dot" a variable is to always "dot" > the variable when it is on the right side of the operator. > > 05. Dotting a variable basically turns it into a constant value. > R:BASE looks only at the value of the variable when it is > dotted. R:BASE doesn't look at the datatype, just at the value > the variable contains. That's why you can have a TEXT datatype > variable (result of a CHOOSE or DIALOG command, for example) > containing a value that looks like a DATE and use that variable > to compare to a DATE datatype column or variable. > > 06. In expressions, R:BASE checks the datatype of a dotted variable. > An expression is anything enclosed in parentheses. R:BASE > verifies the datatype in expressions to make sure the expression > is valid. You can't add an INTEGER to a TEXT for example. > > 07. Dotted variables are used in Form and Report expressions as they > are in the SET VAR command. On the right side of the operator, > dot the variable. > > 08. Dotted variables are commonly used in WHERE clauses and in > calculations with other variables. > > Example 01: > > SET VAR vDate DATE = 11/19/2001 > SELECT * FROM TransMaster WHERE TransDate <= .vDate > > selects all the records from the table TransMaster where the > value in the column TransDate is less than or equal to the > value contained in the variable vdate. > > Example 02: > > SET VAR vOrder INTEGER = 20001 > SET VAR vAmount CURRENCY = NULL > SET VAR vShip TEXT = NULL > SET VAR vFreight CURRENCY = NULL > SET VAR vTax CURRENCY = NULL > SET VAR vState TEXT = NULL > > SELECT NetAmount, ShipMethod, StateAbr INTO + > vAmount INDIC IvAmount, + > vShip INDIC IvShip, + > vState INDIC IvState + > FROM Orders WHERE OrderNum = .vOrder > IF vShip = 'AIR' THEN > SET VAR vFreight = $11.00 > ELSE > SET VAR vFreight = $5.00 > ENDIF > SET VAR vAmount = (.vAmount + .vFreight) > SELECT TaxRate INTO vTax INDIC IvTax + > FROM States WHERE StateAbr = .vState > SET VAR vAmount = (.vAmount+(.vAmount*.vTax)) > > On the right side of the equals sign (the operator), > the variable is dotted. On the left side of the operator, > in the IF and the SET VAR commands, the variable is > referenced by its name only, it is not dotted. > > Ampersand Variables: > ------------------- > > 01. You can't dot a variable when it contains part of a command > - a table or column name, or an ORDER BY or WHERE clause. > > 02. When a variable contains part of a command, its name is > prefaced with an ampersand, "&", and it is called an ampersand > variable. The "&" in front of the variable name tells R:BASE > that the variable contains part of the command, not a value, > and the contents of the variable are used when parsing the > command. > > 03. Don't confuse the ampersand that prefaces a variable name with > the ampersand that is used to concatenate TEXT values. > > 04. Because an ampersand variable is part of a command, it can't > be used inside parentheses. Parentheses indicate expressions, > expressions are parsed separately from the rest of the command. > You need to include the parentheses as part of the variable > value. Sub-selects and IN lists are enclosed in parentheses and > you can't use an ampersand variable inside them, you need to > include the entire sub-select or IN list, including parentheses, > as the variable value. > > 05. Ampersand variables are most often used to hold table and column > names and WHERE and ORDER BY clauses. By using ampersand variables > to hold column and table names, you can use the same command to > select data from different tables. The CHOOSE command displays > menus of available tables and columns. > > Example 01: > > SET VAR vTable TEXT = NULL > SET VAR vColList TEXT = NULL > CLS > CHOOSE vTable FROM #TABLES AT CENTER,CENTER TITLE 'Choose Table' + > CAPTION 'List of Tables' LINES 18 FORMATTED > CLS > CHOOSE vColList FROM #COLUMNS IN &vTable AT CENTER,CENTER + > CHKBOX TITLE 'Choose Column(s)' CAPTION 'List of Columns' + > LINES 18 FORMATTED > > The values selected from the menus are placed into variables. > The variables might look like this: > > vTable = Employee TEXT > vColList = Empid,EmpLname,EmpPhone,EmpExt TEXT > > The variables are then used in any command that uses a table name > or column list. Where you see Column names or Table/View name you > can substitute an ampersand variable that contains the Column, > Table or View name. The variables must be used as ampersand > variables to tell R:BASE they contain part of the command. > > Example 02: > > BROWSE &vColList FROM &vTable > > To prompt for an ORDER BY clause, use the CHKSORT option instead > of CHKBOX on the CHOOSE...FROM #COLUMNS. > > Example 03: > > SET VAR vOrderBy TEXT = NULL > CHOOSE vOrderBy FROM #COLUMNS IN &vTable AT CENTER,CENTER + > CHKSORT TITLE 'Choose Column(s)' CAPTION 'Order By' + > LINES 18 FORMATTED > > The CHKSORT option prompts for Ascending or Descending just like > the R:BASE sort menus. The variable contains ASC or DESC as well > as the column names. It might look like this: > > vOrderBy = EmpLname ASC,EmpFname ASC TEXT > > In the command, follow the keywords ORDER BY with the ampersand > variable containing the columns to order by. > > Example 04: > > BROWSE &vColList FROM &vTable ORDER BY &vOrderBy > > --- > > Download PDF version of this article at: > > http://www.rbase.com/FromTheEdge/DottedVsAmpersandVariables.pdf > > Send your constructive comments to: mailto:[EMAIL PROTECTED] > > Very Best Regards, > > Razzak. > > ===================================-============================ > Official R:BASE List Server: mailto:[EMAIL PROTECTED] > RBTI Events/Training: http://www.rbase2000.com/events > R:DCC Members: http://www.rbase2000.com/rdcc > ================================================================ > R:BASE, Oterro & R:Tango are registered trademarks of RBTI. > ==================================-============================= >
