stack copy from stk_extab into stk_country WHERE XID LIKE 'CC0%';

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

quote:

stack copy from stk_extab into stk_country WHERE XID EQ 'CC0$$$';

stack copy from stk_extab into stk_country WHERE mask(XID,'999') EQ 'CC0';

stack copy from stk_extab into stk_country WHERE XID contains 'CC0';

quote:

The COPY command copies some or all of the rows of one stack into another stack. You can use the COPY command to overwrite existing rows in the target stack, to add new rows, or to create the entire target stack.

You must define the contents of a stack before copying data into it. This can be accomplished by issuing a NEXT or an INFER command for data source fields, and COMPUTE for non-data source fields.

Source and target database stacks used in the Copy command must be derived from the same data source description. The COPY command checks that the data source and segment names are the same, and copies all columns in the source stack whose names and data types exactly match columns in the target stack. In this context, data type refers to the basic data type (such as integer) and all other data attributes including length, precision, null (MISSING), and display options such as zero suppression. Source and target columns do not need to be in the same sequence.


--------------------------------------------------------------------------------
Top of page
--------------------------------------------------------------------------------


Syntax: How to Use the COPY Command
The syntax of the COPY command is

[FOR {int|ALL}|STACK] COPY FROM {stk[(row)]|CURRENT}
INTO {stk[(row)]|CURRENT} [WHERE expression] [;]
where:

FOR
Is a prefix used with int or ALL to specify the number of rows to copy from the source (FROM) stack into the target (INTO) stack. If you omit both FOR and STACK, only the first row of the source stack is copied.

int
Is an integer expression that specifies how many source stack rows to copy into the target stack. If int exceeds the number of source stack rows between the starting row and the end of the stack, all of those rows are copied.

ALL
Indicates that all of the rows starting with either the first row or the subscripted row are copied from the source (FROM) stack into the target (INTO) stack.

STACK
Is a synonym for the prefix FOR ALL. If you omit both FOR and STACK, only the first row of the source stack is copied.

FROM
Is used with a stack name to specify which stack to copy the data from.

INTO
Is used with a stack name to specify the stack to be created or modified.

stk
Is the name of the source or target stack. You can specify the same stack as the source and target stacks.

row
Is a stack subscript that specifies a starting row number. It can be a constant, an integer variable or any Maintain expression that results in an integer value. If you omit row, it defaults to 1.

CURRENT
Specifies the Current Area. If you specify CURRENT for the source stack, all Current Area fields that also exist in the target stack are copied to the target stack. You cannot specify CURRENT if you specify FOR or STACK.

WHERE
Specifies selection criteria for copying stack rows. If you specify a WHERE phrase, you must also specify a FOR or STACK phrase.

expression
Is any Maintain expression that resolves to a Boolean expression. Unlike an expression in the WHERE phrase of the NEXT command, it does not need to refer to a data source field.

;
Terminates the command. Although the semicolon is optional, it is recommended that you include it to allow for flexible syntax and better processing. For more information about the benefits of including the semicolon, see Terminating a Command's Syntax.


--------------------------------------------------------------------------------
Top of page
--------------------------------------------------------------------------------


Example: Copying All Rows of a Stack
The following copies the entire Emp stack into a new stack called Newemp:

FOR ALL COPY FROM Emp INTO Newemp;

--------------------------------------------------------------------------------
Top of page
--------------------------------------------------------------------------------


Example: Copying a Specified Number of Stack Rows
The following copies 100 rows from the Emp stack starting with row number 101. The rows are inserted beginning with row one of the stack Subemp:

FOR 100 COPY FROM Emp(101) INTO Subemp;

--------------------------------------------------------------------------------
Top of page
--------------------------------------------------------------------------------


Example: Copying the First Row of a Stack
The following copies the first row of the Emp stack into the first row in the Temp stack. Only the first row in the source (FROM) stack is copied because this is the default when a prefix is not specified for the COPY command. The data is copied into the first row of the Temp stack because the first row is the default when a row number is not supplied for the target (INTO) stack:

COPY FROM Emp INTO Temp;

--------------------------------------------------------------------------------
Top of page
--------------------------------------------------------------------------------


Example: Copying a Row Into the Current Area
The following example copies the tenth row of the Emp stack into the Current Area. Only one row is copied from the Emp stack because the COPY command does not have a prefix. Every column in the stack is copied into the Current Area. If there is already a field in the Current Area with the same name as a column in the stack, the Current Area variable is replaced with data from the Emp stack:

COPY FROM Emp(10) INTO CURRENT;

--------------------------------------------------------------------------------
Top of page
--------------------------------------------------------------------------------


Example: Copying Rows Based on Selection Criteria
You can also copy selected rows based on selection criteria. The following example copies every row in the World stack that has a Country equal to USA into a new stack called USA:

FOR ALL COPY FROM World INTO USA WHERE Country EQ 'USA';
The following takes data from one stack and places it into three different stacks: one to add data, one to change data, and one to update data.

FOR ALL COPY FROM Inputstk INTO Addstk WHERE Flag EQ 'A';
FOR ALL COPY FROM Inputstk INTO Delstk WHERE Flag EQ 'D';
FOR ALL COPY FROM Inputstk INTO Chngstk WHERE Flag EQ 'C';
FOR ALL INCLUDE Dbfield FROM Addstk;
FOR ALL DELETE Dbfield FROM Delstk;
FOR ALL UPDATE Dbfield1 Dbfield2 FROM Chngstk;

--------------------------------------------------------------------------------
Top of page
--------------------------------------------------------------------------------


Example: Appending One Stack to Another
The following example takes an entire stack and adds it to the end of an existing stack. The subscript consists of an expression. Yeardata.FocCount is a stack variable where Yeardata is the name of the stack and FocCount contains the number of rows currently in the stack. By adding one to FocCount, the data is added after the last row:

FOR ALL COPY FROM Junedata INTO Yeardata(Yeardata.FocCount+1);

--------------------------------------------------------------------------------
Top of page
--------------------------------------------------------------------------------


Reference: Usage Notes for COPY
If the FOR int prefix specifies more rows than are in the source (FROM) stack, all of the rows are copied.
Only the first row of the source (FROM) stack is copied if the COPY command does not include FOR.
The entire stack is copied if the source (FROM) stack is not subscripted and FOR ALL is used.
The row to start copying from defaults to the first row unless the source (FROM) stack is subscripted. If the source (FROM) stack is subscripted, the copy process starts with the row number and copies as many rows as specified in the FOR n prefix, or the remainder of the stack if FOR ALL is specified.
No change is made to the source (FROM) stack unless it is also the target (INTO) stack.
INTO CURRENT cannot be used with the FOR phrase and generates an error if specified.
To copy an entire stack, specify FOR ALL without a subscripted source (FROM) stack stack.
Stack columns created using the COMPUTE command cannot be copied into the Current Area.
If the source (FROM) stack is the Current Area, the only Current Area fields that are copied are those that have a corresponding column name in the target (INTO) stack.
If the target (INTO) stack is not subscripted, the data is copied into the first row in the stack. If the target (INTO) stack is subscripted, the copied row or rows are inserted at this row.
If the COPY command specifies the command output destination as a row or rows of an existing stack that already have data in them, then the old data in these rows is overwritten with the new data when the COPY is executed.
If the source (FROM) stack has fewer columns than the target (INTO) stack, the columns that do not have any data are initialized to blank, zero or null (missing) as appropriate.
Source (FROM) stack rows will overwrite the specified target (INTO) stack rows if they already exist.
If the COPY command creates rows in the target (INTO) stack, and the target (INTO) stack contains columns that are not in the source (FROM) stack, those columns in the new rows will be initialized to their default values of blank, zero, or null (missing).
If the source (FROM) stack has more columns than the target (INTO) stack, only corresponding columns are copied.
The FOR prefix copies rows from the source (FROM) stack one row at a time, not all at the same time. For example, the following command
FOR ALL COPY FROM Car(Car.FocIndex) INTO Car(Car.FocIndex+1);
copies the first row into the second, then copies those same values from the second row into the third, and so on. When the command has finished executing, all rows will have the same values as the first row.


--------------------------------------------------------------------------------
Top of page
--------------------------------------------------------------------------------


Reference: Commands Related to COPY
INFER defines the columns in a stack.
COMPUTE defines the columns in a stack for non-data source fields.
NEXT defines the columns in a stack and places data into it.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Expression Reference.
Reference: Logical Operators.
The following list shows the logical operators you can use in an expression: 

Description                             Operator 
Equality                                   EQ
Inequality                                 NE
Less than                                  LT
Greater than                               GT
Less than or equal to                      LE
Greater than or equal to                   GE
Contains the specified character string    CONTAINS
Omits the specified character string       OMITS 
Negation                                   NOT
Conjunction                                AND
Disjunction                                OR

Using Selection Logic to Retrieve Rows
When you are retrieving rows using the NEXT command, you have the option to restrict the rows you retrieve using the WHERE clause. 
The syntax for this option is 

WHERE operand1 comparison_op1 operand2 
[AND operand3 comparison_op1 operand4 ...]
where:

operand1, operand2, operand3, operand4, ...
Are operands. In each NEXT WHERE expression, one operand must be a data source field, and one must be a valid Maintain expression 
that does not refer to a data source field.

For more information about Maintain expressions, see Expressions Reference.

comparison_op1, comparison_op2, ...
Can be any of the comparison operators listed in Logical Operators or any of the comparison operators listed in the following table. 
Some comparison operators may be listed in both places; this means that they can be used in a WHERE clause in an enhanced way.

Operator                Description                         Example
 
IS, EQ, NE, IS_NOT     Select data source values using      WHERE ZipCode IS '112$$'
                       wildcard characters (you embed 
                       the wildcards in a character 
                       constant in the non-data source 
                       operand). You can use dollar 
                       sign wildcards ($) throughout 
                       the constant to signify that 
                       any character is acceptable in 
                       the corresponding position of 
                       the data source value. 

                       If you wish to allow any value 
                       of any length at the end of the 
                       data source value, you can 
                       combine a dollar sign wildcard 
                       with an asterisk ($*) at the 
                       end of the constant.
 
 
CONTAINS, OMITS        Select data source values that       COMPUTE name/A4 = 'BANK';
                       contain or omit a character          FOR ALL NEXT bank_code bank_name into stackname 
                       string stored in a variable.         WHERE bank_name CONTAINS name;
                                                            returns all data where the word BANK is part of the bank name.

                                                            COMPUTE name/A4 = 'BANK';
                                                            FOR ALL NEXT bank_code 
                                                            bank_name into stackname
                                                             WHERE bank_name OMITS name;
                                                            returns all data where the bank name does not include the word BANK. 
 
EXCEEDS                Selects data source values 
                       that are greater than a numeric 
                       value.
  
 
IN (list),             Select data source values that       FOR ALL NEXT emp_id bank_name INTO stackname
NOT_IN (list)          are in or not in a list. IN          WHERE bank_name NOT_IN ('ASSOCIATED BANK', CITIBANK)
                       and NOT_IN can be used with all      returns all data where the bank name is not in the list.
                       data types.
  
EQ_MASK,               Select data source values that       COMPUTE code/A4='AAA$';
NE_MASK                match or do not match a mask.        FOR ALL NEXT bank_name INTO stackname 
                       Use the $ sign to replace each       WHERE bank_ncode EQ_Mask code;
                       letter in the value. Masks can       returns all data where the bank code starts with AAA 
                       only be used with alphanumeric       and has any character at the end. 
                       data. The masked value may be 
                       hard coded or a variable:            COMPUTE code/A4='AAA$';
                                                            FOR ALL NEXT bank_name INTO Stackname
                                                            WHERE bank_code NE_Mask code;
                                                            returns all data where the bank code doesn't match the mask.