Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Problems copying stacks with a where clause

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Problems copying stacks with a where clause
 Login/Join
 
Guru
posted
Hi all,

I've got a stack which contains a number of columns, identified by a code (2 characters alfa and 4 digits). I want to copy all the countries into a separate stack, using the copy command. However, it copies 0 rows, so as a work-around I used a repeat-loop copying all rows where the code was 'CC0'. The copy command that did not work was the following:

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

Any clues?

Tia
Håkan

This message has been edited. Last edited by: Kerry,


WebFOCUS DS 8.0.06/08 DS/AS
WebFOCUS RS 8.0.08 (Linux/IBM i)
WebFOCUS Client 8.0.06 (Linux)
 
Posts: 319 | Location: Stockholm, Sweden | Registered: February 04, 2004Report This Post
Expert
posted Hide Post
I don't know Maintain, but perhaps you can use the LIKE selection instead:

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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
Hakan,

Francis is correct. You can use the LIKE operator with a '%' wildcard.

You might also try WHERE XID EQ 'CC0$*'

And look up LIKE in your MDE's Help-Language Reference


Regards,
Dave

http://www.daveayers.com

WebFocus/Maintain 7.6.4-8
on Win2000 and 2003 Server
 
Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003Report This Post
Guru
posted Hide Post
Francis, Dave,

the LIKE operator gives me FOC3727 when running and the EQ 'CC0$*' returns 0 rows, so I suppose I'll have to stick to the work-around.

Rgds
Håkan


WebFOCUS DS 8.0.06/08 DS/AS
WebFOCUS RS 8.0.08 (Linux/IBM i)
WebFOCUS Client 8.0.06 (Linux)
 
Posts: 319 | Location: Stockholm, Sweden | Registered: February 04, 2004Report This Post
Virtuoso
posted Hide Post
Håkan

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


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

It is a bit limited because you need to know the length of the search string, so it is not a very dynamic approach.

Or

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

Which may work as your field XID is 2 alpha, 4 numeric.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Master
posted Hide Post
There is a command in Maintain, EQ_MASK and NE_MASK however, it seems to only be working for NEXT with WHERE and not COPY with WHERE. For example, this works:

Compute mask1/a6 = "CCO$$$";
For all next XID into STK_COUNTRY
Where XID EQ_MASK mask1;

However, this would require multiple NEXT statements. I will try to find out why EQ_MASK is not available for COPY.

For now, I agree with Alan. The best way to accomplish what you want is:

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

Or you can create an upper and lower bound:

Compute LB/a3="CC0";
Compute UP/a3="CCZ";
Stack copy from stk_extab into stk_country
where xid ge lb
and xid le ub

Mark
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
Platinum Member
posted Hide Post
Well, this clearly sucks !

The 7.6.4 Maintain Language Reference clearly states that LIKE, and IS and EQ with masks should work.

Did they work on a previous release, or is this just a lie in the documentation ? And how many other mis-statements are in Maintain documentation ?

Sorry to be so frank, but I find this kind of thing very troubling.


Regards,
Dave

http://www.daveayers.com

WebFocus/Maintain 7.6.4-8
on Win2000 and 2003 Server
 
Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003Report This Post
Virtuoso
posted Hide Post
Mmmm.

I have seen LIKE with reporting, but NEVER with Maintain. Do you have a a manual DN number and page?

Additionally, the manuals are quite explicit in saying that a WHERE on a NEXT is different than that on a WHERE for a COPY.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
Alan,

I looked it from 'Help'-'Language Reference' in my 7.6.4 Developer Studio, Maintain Developer Environment. Just as it came from IBI.

I haven't checked any .pdf files on the IBI site


Regards,
Dave

http://www.daveayers.com

WebFocus/Maintain 7.6.4-8
on Win2000 and 2003 Server
 
Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003Report This Post
Virtuoso
posted Hide Post
Dave,

The only reference I can find to LIKE and IS and EQ with MASKS is in 'Selecting Records for your Reports', which ain't Maintain.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Master
posted Hide Post
Dave
I spoke with programming. The issue here is, when using the NEXT command, we pass the WHERE clause conditions to the parser. When we are doing COPY, we use internal Maintain code. The Masks are not supported. I have checked the manual:

Maintain Language Reference > Command Reference > COPY.

I don't see any reference to WHERE supporting masking or wildcards. Again, this is different than NEXT with WHERE. If there is a location in the manual that states that this is supported please let me know and I will research it.

Mark
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
Expert
posted Hide Post
From the online documentation (Maintain Language Reference > Command Reference > COPY), how can you determine that LIKE is not a valid expression?

"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." The example shows "WHERE Flag EQ 'A'" - how does one assume that LIKE won't work?


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
I tried to backtrack to where in my 7.6.4 documentation, I found the references to LIKE, but can't seem to do so. I am pretty sure I saw "maintain language reference' at the top.

And I too am a sucker for the statement in the doc.
"expression - Is any Maintain expression that resolves to a Boolean expression."

If there is a difference in behavior between COPY and NEXT, it should be clearly specified in the documentation.


Regards,
Dave

http://www.daveayers.com

WebFocus/Maintain 7.6.4-8
on Win2000 and 2003 Server
 
Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003Report This Post
Virtuoso
posted Hide Post
Oh come on, read the documentation, don't just glibly make assumptions. Attend a Maintain training course, it is all made clear.

EXPRESSION REFERENCE:
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

**************************************************

RETRIEVING DATA:
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. 


I don't think the documentation can be much clearer....... where is LIKE?

Anyone want glasses!!!!!!!

This message has been edited. Last edited by: Alan B,


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Problems copying stacks with a where clause

Copyright © 1996-2020 Information Builders