Tag Archives: Join

UPDATE

Posted on by vivaclipper

UPDATE

Update current database file from another database file

Syntax

      UPDATE FROM <xcAlias>
            ON <expKey> [RANDOM]
            REPLACE <idField> WITH <exp>
            [, <idField2> WITH    <exp2>...]

Arguments

FROM <xcAlias> specifies the alias of the work area used to update records in the current work area. This argument may be specified either as a literal file name or as a character expression enclosed in parentheses.

ON <expKey> specifies the expression that defines matching records in the FROM work area.

REPLACE <idField> specifies a field in the current work area to replace with a new value.

WITH <exp> specifies the value to replace into the current field. You must reference any field contained in the FROM work area with the correct alias.

RANDOM allows records in the FROM database file to be in any order. If this option is specified, the current database file must be indexed on <expKey>.

Description

UPDATE is a database command that replaces fields in the current work area with values from another work area based on the specified key expression. UPDATE is designed to update only current work area records based on a one-to-one or one-to-many relation with the FROM work area. This means that UPDATE can only update records in the current work area with unique key values. When there is more than one instance of a key value, only the first record with the key value is updated. The FROM work area, however, can have duplicate key values.

There are two formulations of the command depending on whether the FROM work area records are sorted or indexed on <expKey> or not. If RANDOM is not specified, both the current work area and the FROM work area must be indexed or sorted in <expKey> order. If RANDOM is specified, the current work area must be indexed by <expKey>, but the FROM work area records can be in any order.

To use UPDATE in a network environment, the current database file must be locked with FLOCK() or USEed EXCLUSIVEly. The FROM database file may be used in any mode. Refer to the “Network Programming” chapter in the Programming and Utilities Guide for more information.

Notes

. Deleted records: If DELETED is OFF, deleted records in both source files are processed. Records in the file being updated retain their deleted status and are not affected by the deleted status of records in the FROM file. If DELETED is ON, however, no deleted records are processed from either source file.

Examples

      .  This example UPDATEs the Customer database file with
         outstanding invoice amounts:

      USE Invoices NEW
      USE Customer INDEX Customer NEW
      UPDATE FROM Invoices ON Last;
         REPLACE Owed WITH Owed + Invoices->Amount RANDOM

Seealso

DBCREATEIND(), INDEX, JOIN, REPLACE, SET UNIQUE*, SORT

C5_JOIN

Posted on by vivaclipper 
 JOIN 
 Create a new database file by merging records/fields from two work areas
------------------------------------------------------------------------------
 Syntax

     JOIN WITH <xcAlias> TO <xcDatabase>
        FOR <lCondition> [FIELDS <idField list>]

 Arguments

     WITH <xcAlias> is the name of the work area to merge with records
     from the current work area.  You can specify it either as a literal
     alias or as a character expression enclosed in parentheses.

     TO <xcDatabase> is the name of the target database file specified
     either as a literal filename or as a character expression enclosed in
     parentheses.

     FOR <lCondition> selects only records meeting the specified
     condition.

     FIELDS <idField list> is the projection of fields from both work
     areas into the new database file.  To specify any fields in the
     secondary work area, reference them with the alias.  If the FIELDS
     clause is not specified, all fields from the primary work area are
     included in the target database file.

 Description

     JOIN creates a new database file by merging selected records and fields
     from two work areas based on a general condition.  JOIN works by making
     a complete pass through the secondary work area for each record in the
     primary work area, evaluating the condition for each record in the
     secondary work area.  When the <lCondition> is true (.T.), a new record
     is created in the target database file using the FIELDS specified from
     both work areas.

     If SET DELETED is OFF, deleted records in both source files (i.e., the
     two files being JOINed) are processed.  However, their deleted status is
     not retained in the target <xcDatabase>.  No record in the target file
     is marked for deletion regardless of its deleted status in either of the
     source files.

     If SET DELETED is ON, no deleted records are processed in either of the
     source files.  Thus, deleted records do not become part of the target
     <xcDatabase>.  Similarly, filtered records are not processed and do not
     become part of the target file.

     Warning!  The number of records processed will be the LASTREC() of
     the primary work area multiplied by the LASTREC() of the secondary work
     area.  For example, if you have two database files with 100 records
     each, the number of records JOIN processes is the equivalent of
     sequentially processing a single database file of 10,000 records.
     Therefore, use this command carefully.

 Examples

     .  This example joins Customer.dbf to Invoices.dbf to produce
        Purchases.dbf:

        USE Invoices NEW
        USE Customers NEW
        JOIN WITH Invoices TO Purchases;
           FOR Last = Invoices->Last;
           FIELDS First, Last, Invoices->Number, ;
              Invoices->Amount

 Files   Library is CLIPPER.LIB.

See Also: SET RELATION






        
    
			

	
	



		
			


	

		
C5 Commands


		

		Posted on  by vivaclipper		

	


		

		
 ?|??            Display one or more values to the console
 @...BOX         Draw a box on the screen
 @...CLEAR       Clear a rectangular region of the screen
 @...GET         Create a new Get object and display it
 @...PROMPT      Paint a menu item and define a message
 @...SAY         Display data at a specified screen or printer row and column
 @...TO          Draw a single- or double-line box
 ACCEPT*         Place keyboard input into a memory variable
 APPEND BLANK    Add a new record to the current database file
 APPEND FROM     Import records from a database (.dbf) file or ASCII text file
 AVERAGE         Average numeric expressions in the current work area
 CALL*           Execute a C or Assembler procedure
 CANCEL*         Terminate program processing
 CLEAR ALL*      Close files and release public and private variables
 CLEAR GETS      Release Get objects from the current GetList array
 CLEAR MEMORY    Release all public and private variables
 CLEAR SCREEN    Clear the screen and return the cursor home
 CLEAR TYPEAHEAD Empty the keyboard buffer
 CLOSE           Close a specific set of files
 COMMIT          Perform a solid-disk write for all active work areas
 CONTINUE        Resume a pending LOCATE
 COPY FILE       Copy a file to a new file or to a device
 COPY STRUCTURE  Copy the current .dbf structure to a new database (.dbf) file
 COPY STRU EXTE  Copy field definitions to a .dbf file
 COPY TO         Export records to a database (.dbf) file or ASCII text file
 COUNT           Tally records to a variable
 CREATE          Create an empty structure extended (.dbf) file
 CREATE FROM     Create a new .dbf file from a structure extended file
 DELETE          Mark records for deletion
 DELETE FILE     Remove a file from disk
 DELETE TAG      Delete a tag
 DIR*            Display a listing of files from a specified path
 DISPLAY         Display records to the console
 EJECT           Advance the printhead to top of form
 ERASE           Remove a file from disk
 FIND*           Search an index for a specified key value
 GO              Move the pointer to the specified identity
 INDEX           Create an index file
 INPUT*          Enter the result of an expression into a variable
 JOIN            Create a new database file by merging from two work areas
 KEYBOARD        Stuff a string into the keyboard buffer
 LABEL FORM      Display labels to the console
 LIST            List records to the console
 LOCATE          Search sequentially for a record matching a condition
 MENU TO         Execute a lightbar menu for defined PROMPTs
 NOTE*           Place a single-line comment in a program file
 PACK            Remove deleted records from a database file
 QUIT            Terminate program processing
 READ            Activate full-screen editing mode using Get objects
 RECALL          Restore records marked for deletion
 REINDEX         Rebuild open indexes in the current work area
 RELEASE         Delete public and private memory variables
 RENAME          Change the name of a file
 REPLACE         Assign new values to field variables
 REPORT FORM     Display a report to the console
 RESTORE         Retrieve memory variables from a memory (.mem) file
 RESTORE SCREEN* Display a saved screen
 RUN             Execute a DOS command or program
 SAVE            Save variables to a memory (.mem) file
 SAVE SCREEN*    Save the current screen to a buffer or variable
 SEEK            Search an order for a specified key value
 SELECT          Change the current work area
 SET ALTERNATE   Echo console output to a text file
 SET BELL        Toggle sounding of the bell during full-screen operations
 SET CENTURY     Modify the date format to include or omit century digits
 SET COLOR*      Define screen colors
 SET CONFIRM     Toggle required exit key to terminate GETs
 SET CONSOLE     Toggle console display to the screen
 SET CURSOR      Toggle the screen cursor on or off
 SET DATE        Set the date format for input and display
 SET DECIMALS    Set the number of decimal places to be displayed
 SET DEFAULT     Set the CA-Clipper default drive and directory
 SET DELETED     Toggle filtering of deleted records
 SET DELIMITERS  Toggle or define GET delimiters
 SET DESCENDING  Change the descending flag of the controlling order
 SET DEVICE      Direct @...SAYs to the screen or printer
 SET EPOCH       Control the interpretation of dates with no century digits
 SET ESCAPE      Toggle Esc as a READ exit key
 SET EXACT*      Toggle exact matches for character strings
 SET EXCLUSIVE*  Establish shared or exclusive USE of database files
 SET FILTER      Hide records not meeting a condition
 SET FIXED       Toggle fixing of the number of decimal digits displayed
 SET FORMAT*     Activate a format when READ is executed
 SET FUNCTION    Assign a character string to a function key
 SET INDEX       Open one or more order bags in the current work area
 SET INTENSITY   Toggle enhanced display of GETs and PROMPTs
 SET KEY         Assign a procedure invocation to a key
 SET MARGIN      Set the page offset for all printed output
 SET MEMOBLOCK   Change the block size for memo files
 SET MESSAGE     Set the @...PROMPT message line row
 SET OPTIMIZE    Change the setting that optimizes using open orders
 SET ORDER       Select the controlling order
 SET PATH        Specify the CA-Clipper search path for opening files
 SET PRINTER     Toggle echo of output to printer or set the print destination
 SET PROCEDURE*  Compile procedures and functions into the current object file
 SET RELATION    Relate two work areas by a key value or record number
 SET SCOPE       Change the boundaries for scoping keys in controlling order
 SET SCOPEBOTTOM Change bottom boundary for scoping keys in controlling order
 SET SCOPETOP    Change top boundary for scoping keys in controlling order
 SET SCOREBOARD  Toggle the message display from READ or MEMOEDIT()
 SET SOFTSEEK    Toggle relative seeking
 SET TYPEAHEAD   Set the size of the keyboard buffer
 SET UNIQUE*     Toggle inclusion of non-unique keys into an index
 SET WRAP*       Toggle wrapping of the highlight in menus
 SKIP            Move the record pointer to a new position
 SORT            Copy to a database (.dbf) file in sorted order
 STORE*          Assign a value to one or more variables
 SUM             Sum numeric expressions and assign results to variables
 TEXT*           Display a literal block of text
 TOTAL           Summarize records by key value to a database (.dbf) file
 TYPE            Display the contents of a text file
 UNLOCK          Release file/record locks set by the current user
 UPDATE          Update current database file from another database file
 USE             Open an existing database (.dbf) and its associated files
 WAIT*           Suspend program processing until a key is pressed
 ZAP             Remove all records from the current database file


 

			

	
	



		
			


		
			


	

		
C5 Database Commands


		

		Posted on  by vivaclipper		

	


		

		
Database Commands and Statements


APPEND BLANK : 


Add a new record to current database file.


APPEND BLANK


APPEND FROM :


Import records from a (.dbf) or ASCII file


APPEND FROM <xcFile>
     [FIELDS <idField list>]
     [<scope>] 
     [WHILE <lCondition>] 
     [FOR <lCondition>]
     [SDF | DELIMITED [WITH BLANK | <xcDelimiter>] |
     [VIA <xcDriver>]]


AVERAGE :


Average numeric expressions in the current work area


AVERAGE <nExp list> TO <idVar list> 
     [<scope>] 
     [WHILE <lCondition>] 
     [FOR <lCondition>]


CLEAR ALL* :


Close files and release public and private variables.


CLEAR ALL


CLOSE :


Close a specific set of files


CLOSE [<idAlias> | ALL | ALTERNATE | DATABASES | FORMAT |INDEXES ]


COMMIT :


Perform a solid-disk write for all active work areas


COMMIT


CONTINUE :


Resume a pending LOCATE


CONTINUE


COPY STRUCTURE :


Copy the current (.dbf) structure to a new (.dbf) file


COPY STRUCTURE [FIELDS <idField list>] TO <xcDatabase>


COPY STRUCTURE EXTENDED :


Copy field definitions to a (.dbf) file


COPY STRUCTURE EXTENDED TO <xcExtendedDatabase>


COPY TO :


Export records to a new (.dbf) or ASCII file

COPY [FIELDS <idField list>] TO <xcFile>
     [<scope>] 
     [WHILE <lCondition>] 
     [FOR <lCondition>]
     [SDF | DELIMITED [WITH BLANK | <xcDelimiter>] |
     [VIA <xcDriver>]]


COUNT :


Tally records to a variable


COUNT TO <idVar>
     [<scope>] 
     [WHILE <lCondition>] 
     [FOR <lCondition>]


CREATE :


Create an empty structure extended (.dbf) file


CREATE <xcExtendedDatabase>


CREATE FROM :


Create a new (.dbf) file from a structure extended file


CREATE <xcDatabase> 
    FROM <xcExtendedDatabase> 
    [NEW]
    [ALIAS <xcAlias>] 
    [VIA <cDriver>]


DELETE :


Mark records for deletion


DELETE [<scope>] 
    [WHILE <lCondition>]
    [FOR <lCondition>]


FIELD :


Declare database field names


FIELD <idField list> [IN <idAlias>]


FIND* :


Search an index for a specified key value


FIND <xcSearchString>


GO :


Move the record pointer to a specific record


GO[TO] <nRecord> | BOTTOM | TOP


JOIN :


Merge two (.dbf) files to create a new (.dbf) file


JOIN WITH <xcAlias> TO <xcDatabase>
     FOR <lCondition> 
     [FIELDS <idField list>]


LOCATE :


Search sequentially for a record matching a condition


LOCATE [<scope>] 
    FOR <lCondition>
    [WHILE <lCondition>]


PACK :


Remove deleted records from a database file


PACK


RECALL :


Restore records marked for deletion


RECALL [<scope>] 
     [WHILE <lCondition>]
     [FOR <lCondition>]


REPLACE :


Assign new values to field variables


REPLACE <idField> WITH <exp>
    [, <idField2> WITH <exp2>...]
    [<scope>] 
    [WHILE <lCondition>] 
    [FOR <lCondition>]


SEEK :


Search an index for a specified key value


SEEK <expSearch>


SELECT :


Change the current work area


SELECT <xnWorkArea> | <idAlias>


SET DELETED :


Toggle filtering of deleted records


SET DELETED on | OFF | <xlToggle>


SET EXCLUSIVE :


Establish shared or exclusive USE of database files


SET EXCLUSIVE ON | off | <xlToggle>


SET FILTER :


Hide records not meeting a condition


SET FILTER TO [<lCondition>]




SET  MEMOBLOCK :




     Change the block size for memo files

     SET MEMOBLOCK TO <nSize>


SET PATH :


Specify the search path for opening files


SET PATH TO [<xcPathspec list>]


SET RELATION :


Relate two work areas by a key value or record number


SET RELATION TO [<expKey> | <nRecord> INTO <xcAlias>]
    [, [TO] <expKey2> | <nRecord2> INTO <xcAlias2>...]
    [ADDITIVE]


SET SOFTSEEK :


Toggle relative SEEKing


SET SOFTSEEK on | OFF | <xlToggle>


SET UNIQUE :


Toggle the inclusion of nonunique keys into an index


SET UNIQUE on | OFF | <xlToggle>


SKIP :


Move the record pointer to a new position


SKIP [<nRecords>] [ALIAS <idAlias> | <nWorkArea>]


SORT :


Copy to a (.dbf) file in sorted order


SORT TO <xcDatabase> ON <idField> [/[A | D][C]]
    [, <idField2> [/[A | D][C]]...]
    [<scope>] 
    [WHILE <lCondition>] 
    [FOR <lCondition>]


SUM :


Sum numeric expressions to variables


SUM <nExp list> TO <idVar list>
    [<scope>] 
    [WHILE <lCondition>] 
    [FOR <lCondition>]


TOTAL : 


Summarize records by key value to a (.dbf) file


TOTAL ON <expKey> FIELDS <idField list>
    TO <xcDatabase>
    [<scope>] 
    [WHILE <lCondition>] 
    [FOR <lCondition>]


UNLOCK :


Release file/record locks set by the current user


     UNLOCK [ALL]


UPDATE : 


Update current database file from another database file


UPDATE FROM <xcAlias>
   ON <expKey> [RANDOM]
   REPLACE <idField> WITH <exp>
   [, <idField2> WITH <exp2>...]


USE : 


Open an existing (.dbf) and its associated files

USE [<xcDatabase>
    [INDEX <xcIndex list>]
    [ALIAS <xcAlias>] 
    [EXCLUSIVE | SHARED]
    [NEW] [READONLY]
    [VIA <cDriver>]]


ZAP : 


Remove all records from the current database file


ZAP

			

	
	



		
			


	

		
Database Terms


		

		Posted on  by vivaclipper		

	


		

		
Alias :


The name of a work area; an alternate name given to a database file. Aliases are often used to give database files descriptive names and are assigned when the database file is opened. If no alias is specified when the database file is USEd, the name of the database file becomes the alias.


An alias can be used to reference both fields and expressions (including user-defined functions). In order to alias an expression, the expression must be enclosed in parentheses.


See also : Work Area


Attribute :


As a formal DBMS term, refers to a column or field in a table or database file.


See Also: Column, Field


Beginning of File :


The top of the database file. In Clipper language there is no beginning of file area or record. Instead, it is indicated by BOF() returning true (.T.) if an attempt is made to move the record pointer above the first record in the database file or the database file is empty.


Cell :


In a table, a cell is the intersection of a Row and a Column.


Column :


A database term used to describe a field in a table or database file.


See Also: Field


Concurrency :


The degree to which data can be accessed by more than one user at the same time.


Condition :


A logical expression that determines whether an operation will take place. With database commands, a logical expression that determines what records are included in an operation. Conditions are specified as arguments of the FOR or WHILE clause.


See Also: Scope


Controlling/Master Index :


The index currently being used to refer to records by key value or sequential record movement commands.


See Also: Index, Natural Order


Database :


An aggregation of related operational data used by an application system. A database can contain one or more data files or tables.


See Also: Field, Record, Tuple, View


DBMS :


An acronym for the term database management system. A DBMS is a software system that mediates access to a database through a data manipulation language.


Delimited File :


A text file that contains variable-length database records with each record separated by a carriage return/linefeed pair (CHR(13) + CHR(10)) and terminated with an end of file mark (CHR(26)). Each field within a delimited file is variable length, not padded with either leading or trailing spaces, and separated by a comma. Character strings are optionally delimited to allow for embedded commas.


End of File :


The bottom of a database file. In Clipper language, this is LASTREC() + 1 and is indicated by EOF() returning true (.T.).


Field :


The basic column unit of a database file. A field has four attributes: name, type, length, and decimals if the type is numeric.


See Also: Database, Record, Tuple, Vector, View


Field Variable :


A variable that refers to data in a database field, as opposed to data in memory.


See Also: Local Variable, Memory Variable, Variable


Index :


An ordered set of key values that provides a logical ordering of the records in an associated database file. Each key in an index is associated with a particular record in the database file. The records can be processed sequentially in key order, and any record can be located by performing a SEEK operation with the associated key value.


See Also: Controlling/Master Index, Key Value, Natural Order


Join :


An operation that takes two tables as operands and produces one table as a result. It is, in fact, a combination of other operations including selection and projection.


See Also: Projection, Selection


Key Expression :


An expression, typically based on one or more database fields, that when evaluated, yields a key value for a database record. Key expressions are most often used to create indexes or for summarization operations.


See Also: Index, Key Value


Key Value :


The value produced by evaluating a key expression. When placed in an index, a key value identifies the logical position of the associated record in its database file.


See Also: Index, Key Expression


Master Index :


The index currently being used to refer to records by key value or sequential record movement commands.


See Also : Controlling/Master Index


Memo Type :


A special database field type consisting of one or more characters in the extended character set. The maximum size of a memo field In Clipper language is 65,534 bytes. A memo field differs only from a character string by the fact it is stored in a separate memo (.DBT file) and the field length is variable-length.


See Also: Character String


Natural Order :


For a database file, the order determined by the sequence in which records were originally entered into the file. Also called unindexed order.


See Also: Index


Normalization :


The process of elimination and consolidation of redundant data elements in a database system.


Projection :


A DBMS term specifying a subset of fields. In Clipper, the analogy is the FIELDS clause.


See Also: Join Selection


Query :


A request for information to be retrieved from a database. Alternately, a data structure in which such a request is encoded.


Record :


The basic row unit of a database file consisting of one or more field elements.


See Also: Database, Field, Table, Tuple


Relation :


A link between database files that allows the record pointer to move in more than one database file based on the value of a common field or expression. This allows information to be accessed from more than one database file at a time.


Relational Database System :


A system that stores data in rows and columns, without system dependencies within the data. In other words, relationships between different databases are not stored in the actual database itself, as is the case in a system that uses record pointers.


Row :


A group of related column or field values that are treated as a single entity. It is the same as a Clipper language record.


See Also: Column, Field, Record


Search Condition :


See : Condition, Scope


Scope :


In a database command, a clause that specifies a range of database records to be addressed by the command. The scope clause uses the qualifiers ALL, NEXT, RECORD, and REST to define the record scope.


See Also: Condition


SDF File :


A text file that contains fixed-length database records with each record separated by a carriage return/linefeed pair (CHR(13) + CHR(10)) and terminated with an end of file mark (CHR(26)). Each field within an SDF file is fixed-length with character strings padded with trailing spaces and numeric values padded with leading spaces. There are no field separators.


See Also: Database, Delimited File, Text File


Selection :


A DBMS term that specifies a subset of records meeting a condition. The selection itself is obtained with a selection operator. In Clipper language, the analogy is the FOR clause.


Separator :


The character or set of characters that differentiate fields or records from one another. In Clipper language, the DELIMITED and SDF file types have separators. The DELIMITED file uses a comma as the field separator and a carriage return/linefeed pair as the record separator. The SDF file type has no field separator, but also uses a carriage return/linefeed pair as the record separator.


See Also: Delimiter


Sort Order :


Describes the various ways database files and arrays are ordered.


. Ascending


Causes the order of data in a sort to be from lowest value to highest value.


. Descending


Causes the order of data in a sort to be from highest value to lowest value.


. Chronological


Causes data in a sort to be ordered based on a date value, from earliest to most recent.


. ASCII


Causes data in a sort to be ordered according to the ASCII Code values of the data to be sorted.


. Dictionary


The data in a sort is ordered in the way it would appear if the items sorted were entries in a dictionary of the English language.


. Collating Sequence


Data in a sort will be placed in sequence following the order of characters in the Extended Character Set.


. Natural


The order in which data was entered into the database.


Table :


A DBMS term defining a collection of column definitions and row values. In Clipper, it is represented and referred to as a database file.


Tuple :


A formal DBMS term that refers to a row in a table or a record in a database file. In DIF files, tuple also refers to the equivalent of a table record.


See Also: Database, Field, Record


Update :


The process of changing the value of fields in one or more records. Database fields are updated by various commands and the assignment operator.


Vector :


In a DIF file, vector refers to the equivalent of a table field.


See Also: Database, Field, Record, Tuple


View :


A DBMS term that defines a virtual table. A virtual table does not actually exist but is derived from existing tables and maintained as a definition. The definition in turn is maintained in a separate file or as an entry in a system dictionary file. In Clipper, views are supported only by DBU.EXE and are maintained in (.vew) files.


See Also: Database, Field, Record


Work Area :


The basic containment area of a database file and its associated indexes. Work areas can be referred to by alias name, number, or a letter designator.


See Also: Alias