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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Output data from report into Array

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Output data from report into Array
 Login/Join
 
neuro
Platinum Member
posted
I am looking through the documentation and on the forum and I am trying to figure out how to create an array, but more specically how to create an array based off the results of a report/procedure. Any help with this would be very helpful!!! Smiler

I learn best from real examples, and I am not finding anything that is helping me to find this.


Currenly working @ Learning Circle Education Services
Previously worked @ Nationwide Insurance
Prod: WebFOCUS 7.6.11


Test: WebFOCUS 7.6.11


Dev: WebFOCUS 7.6.11
 
Posts: 125 | Location: Columbus, Ohio | Registered: March 31, 2006Report This Post
dhagen
Virtuoso
posted Hide Post
An array for what programming language?


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
neuro
Platinum Member
posted Hide Post
In the focus language? Otherwise, I have no idea what you mean. Lets say I have a result from a query in MS SQL SERVER that gives me 6 contracts for people. I then want to insert these 6 contracts into a variable such as an array and then in another query/report I want to do a:
"WHERE X in (&ARRAY)". I know I can do this by using a JOIN, however it wants to pull all contracts from the ORACLE database into a hold file then do the join and give me results. The process is overkill and I am looking for a simpler approach. If I can assign a ARRAY to these 6 contracts, then I can just say "Hey WebFocus, run this query where the contract is in the 6 contracts from my previous query on a different database"

Does this make sense?


Currenly working @ Learning Circle Education Services
Previously worked @ Nationwide Insurance
Prod: WebFOCUS 7.6.11


Test: WebFOCUS 7.6.11


Dev: WebFOCUS 7.6.11
 
Posts: 125 | Location: Columbus, Ohio | Registered: March 31, 2006Report This Post
jodye
Platinum Member
posted Hide Post
Hi Neruo

I believe you are looking for something like this simple example that loops through a list and does a new query based on the current value of a variable. Sort of like using an array.

Jodye

-SET &ECHO=ALL;
SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
TABLE FILE CAR
SUM
SALES NOPRINT
BY COUNTRY
ON TABLE HOLD AS MYHOLD FORMAT ALPHA
END

-SET &MYRECS=&LINES;

-*READ THE DATA INTO A LOOP
-RUN
-SET &I=0;
-STRT_READ
-SET &I=&I+1;
-READ MYHOLD &MYCOUNTRY.&I.A10.


TABLE FILE CAR
SUM SALES
BY COUNTRY
WHERE COUNTRY EQ '&MYCOUNTRY.&I';
END


-IF &I LT &MYRECS THEN GOTO STRT_READ;


WF 8.0.0.5M
 
Posts: 246 | Location: Montreal, QC, Canada | Registered: October 01, 2003Report This Post
Håkan
Guru
posted Hide Post
What about this?

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD AS TEMP FORMAT ALPHA
END
-RUN
TABLE FILE CAR
SUM SALES
BY COUNTRY
WHERE COUNTRY IN FILE TEMP;
END


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
neuro
Platinum Member
posted Hide Post
I am actually trying both Lidholm's and Jodye's examples to learn both. However, on Lidholm's I am getting a:

0 NUMBER OF RECORDS IN TABLE= 7 LINES= 7
0 NUMBER OF RECORDS IN TABLE= 7 LINES= 7
0 ERROR AT OR NEAR LINE 34 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC272) FORMAT ERROR IN DECODE OR FILE ELEMENT: +15O
(FOC009) INCOMPLETE REQUEST STATEMENT


Do I need to do something special in the


TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD AS TEMP FORMAT ALPHA
END
-RUN
So that the format is correct? Thanks to both of you so far, I am going to use both ways and learn this.


Currenly working @ Learning Circle Education Services
Previously worked @ Nationwide Insurance
Prod: WebFOCUS 7.6.11


Test: WebFOCUS 7.6.11


Dev: WebFOCUS 7.6.11
 
Posts: 125 | Location: Columbus, Ohio | Registered: March 31, 2006Report This Post
neuro
Platinum Member
posted Hide Post
Lidholm,

I ran your CAR example and get the following error:

0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5
0 ERROR AT OR NEAR LINE 4 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC36219) AN ERROR OCCURED WHEN OPENING FILE: TEMP
0 ERROR AT OR NEAR LINE 9 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC038) THE EXTERNAL FILE DOES NOT CONTAIN ANY TEST LITERALS: (TEMP)
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT


Currenly working @ Learning Circle Education Services
Previously worked @ Nationwide Insurance
Prod: WebFOCUS 7.6.11


Test: WebFOCUS 7.6.11


Dev: WebFOCUS 7.6.11
 
Posts: 125 | Location: Columbus, Ohio | Registered: March 31, 2006Report This Post
smiths
Platinum Member
posted Hide Post
This particular example fails because of the space in 'W GERMANY'.

Revise the code as follows and it works:
  

-* Add single quotes around text
DEFINE FILE CAR
COUNTRY_X/A28 = '''' | COUNTRY | '''';
END

TABLE FILE CAR
PRINT COUNTRY_X
ON TABLE HOLD AS TEMP FORMAT ALPHA
END

-RUN

TABLE FILE CAR
SUM SALES
BY COUNTRY
WHERE COUNTRY IN FILE TEMP
END


So the syntax is correct, just the data caused the problem.

Regards,
Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
jodye
Platinum Member
posted Hide Post
Hi Neuro

My approach assumes that you want to do a bunch of separate queries based on each value in the loop. If you only need to do a sinlge query based on the first read then for sure use Lidholm's method.

Jodye


WF 8.0.0.5M
 
Posts: 246 | Location: Montreal, QC, Canada | Registered: October 01, 2003Report This Post
neuro
Platinum Member
posted Hide Post
First I want to say my thanks, things are starting to come together with the first two sections of my code. The CAR example works great.

I am now getting the following error:


0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5
0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5
0 ERROR AT OR NEAR LINE 38 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC272) FORMAT ERROR IN DECODE OR FILE ELEMENT: +J2
(FOC009) INCOMPLETE REQUEST STATEMENT


My CODE is the following:


ENGINE SQLMSS SET DEFAULT_CONNECTION ohlewwap0055
SQL SQLMSS PREPARE SQLOUT FOR
SELECT
UPPER(UserID) USERID,
DateTime,
ROWID
FROM vLM_producer_potential
WHERE DateTime > DATEADD(dd,-9,getdate())
AND POTENTIAL = '1'
;
END
TABLE FILE SQLOUT
BY USERID
BY DateTime
BY ROWID
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS LMS-POTENTIAL FORMAT FOCUS INDEX ROWID
END
-RUN
DEFINE FILE LMS-POTENTIAL
ROW3/A15V= '''' | ROWID | '''';
END
TABLE FILE LMS-POTENTIAL
PRINT ROW3
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS TEMP1 FORMAT ALPHA
END
-RUN
DEFINE FILE S_CONTACT
ROWSIEB/A15V='''' | S_CONTACT.S_CONTACT.ROW_ID | '''';
END
TABLE FILE S_CONTACT
BY S_CONTACT.S_CONTACT.ROW_ID
WHERE S_CONTACT.S_CONTACT.ROW_ID IN FILE TEMP1;
END



I cannot figure out why if I have my values within quotes that I am still getting this error. A few of us here at work have been trying to solve it with no luck.


Currenly working @ Learning Circle Education Services
Previously worked @ Nationwide Insurance
Prod: WebFOCUS 7.6.11


Test: WebFOCUS 7.6.11


Dev: WebFOCUS 7.6.11
 
Posts: 125 | Location: Columbus, Ohio | Registered: March 31, 2006Report This Post
dhagen
Virtuoso
posted Hide Post
How about:
 
ENGINE SQLMSS SET DEFAULT_CONNECTION ohlewwap0055
SQL SQLMSS PREPARE SQLOUT FOR
SELECT
ROWID
FROM vLM_producer_potential
WHERE DateTime > DATEADD(dd,-9,getdate())
AND POTENTIAL = '1'
;
END
TABLE FILE SQLOUT
PRINT ROWID NOPRINT
BY ROWID 
ON TABLE HOLD AS #TMPOUT FORMAT SQLMSS
END
-RUN
JOIN ROWID IN #TMPOUT TO ROWID IN S_CONTACT AS J1.
END
-RUN
TABLE FILE #TMPOUT
PRINT *
BY ROWID NOPRINT
BY S_CONTACT.ROW_ID
END


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
neuro
Platinum Member
posted Hide Post
dhagen: We have been doing it this way, however the problem and correct me if I am wrong is that on a join each part of the join actually performs each query seperately, then once the hold files are complete it then performs the join. The reports are taking entirely to long to run and the reason is that we have have a Oracle Database which is the backend to Siebel. We have millions of records to go through and this can make a hold file extremely large. The reason I am trying to figure out how to perform the other two ways as Lidholm and Jodye have stated seem to be a better way to perform this procedure. I also do not want to put a lot of time and resources on the database system since others int he company need to create reports as well. Does this make sense? If I can understand these two ways I can then also make this a common process among my coworkers to reduce time and resources. I know that if I can figure out my last previous post this will perform the procedure very fast.


Currenly working @ Learning Circle Education Services
Previously worked @ Nationwide Insurance
Prod: WebFOCUS 7.6.11


Test: WebFOCUS 7.6.11


Dev: WebFOCUS 7.6.11
 
Posts: 125 | Location: Columbus, Ohio | Registered: March 31, 2006Report This Post
neuro
Platinum Member
posted Hide Post
What I have figured out up to this point is the following. When I run the following code:


ENGINE SQLMSS SET DEFAULT_CONNECTION ohlewwap0055
SQL SQLMSS PREPARE SQLOUT FOR
SELECT
UPPER(UserID) USERID,
DateTime,
ROWID
FROM vLM_producer_potential
WHERE DateTime > DATEADD(dd,-9,getdate())
AND POTENTIAL = '1'
;
END
TABLE FILE SQLOUT
BY USERID
BY DateTime
BY ROWID
ON TABLE HOLD AS LMS-POTENTIAL FORMAT FOCUS INDEX ROWID
END
-RUN
DEFINE FILE LMS-POTENTIAL
ROW3/A15V= '''' | ROWID | '''';
END
TABLE FILE LMS-POTENTIAL
PRINT ROW3
-*ON TABLE HOLD AS TEMP1 FORMAT ALPHA
END
-RUN
-EXIT
DEFINE FILE S_CONTACT
ROWSIEB/A15V='''' | S_CONTACT.S_CONTACT.ROW_ID | '''';
END
TABLE FILE S_CONTACT
BY S_CONTACT.S_CONTACT.ROW_ID
WHERE ROWSIEB IN FILE TEMP1;
END


I get the following results:


PAGE 1
ROW3
'1+J2Z+41'
'1+3M+201'
'1+EJD+247'
'1+15OW+20'
'1+1Q7Y+11'


Now, the FIELD in SIEBEL/ORACLE is in 'A15V' format and has the same ROWID information as above in it.

When I run the next code:


ENGINE SQLMSS SET DEFAULT_CONNECTION ohlewwap0055
SQL SQLMSS PREPARE SQLOUT FOR
SELECT
UPPER(UserID) USERID,
DateTime,
ROWID
FROM vLM_producer_potential
WHERE DateTime > DATEADD(dd,-9,getdate())
AND POTENTIAL = '1'
;
END
TABLE FILE SQLOUT
BY USERID
BY DateTime
BY ROWID
ON TABLE HOLD AS LMS-POTENTIAL FORMAT FOCUS INDEX ROWID
END
-RUN
DEFINE FILE LMS-POTENTIAL
ROW3/A15V= '''' | ROWID | '''';
END
TABLE FILE LMS-POTENTIAL
PRINT ROW3
ON TABLE HOLD AS TEMP1 FORMAT ALPHA
END
-RUN
DEFINE FILE S_CONTACT
ROWSIEB/A15V='''' | S_CONTACT.S_CONTACT.ROW_ID | '''';
END
TABLE FILE S_CONTACT
BY S_CONTACT.S_CONTACT.ROW_ID
WHERE ROWSIEB IN FILE TEMP1;
END


This gives me the following Error:


No HTML Output!
--------------------------------------------------------------------------------
0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5
0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5
0 ERROR AT OR NEAR LINE 32 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC272) FORMAT ERROR IN DECODE OR FILE ELEMENT: +J2
(FOC009) INCOMPLETE REQUEST STATEMENT


It seems to mess up because the first ROW is:
'1+J2Z+41', why does the error give me

FOC272) FORMAT ERROR IN DECODE OR FILE ELEMENT: +J2

It very well may be that I have a I.D.I.O.T. ERROR since it can only be as smart as the person running it. Smiler


Currenly working @ Learning Circle Education Services
Previously worked @ Nationwide Insurance
Prod: WebFOCUS 7.6.11


Test: WebFOCUS 7.6.11


Dev: WebFOCUS 7.6.11
 
Posts: 125 | Location: Columbus, Ohio | Registered: March 31, 2006Report This Post
KevinG
Platinum Member
posted Hide Post
Neuro,

Have you opened the TEMP1.ftm file in a text editor and viewed the contents? Are the contents what you expect?

Kevin


WF 7.6.10 / WIN-AIX
 
Posts: 141 | Location: Denver, CO | Registered: December 09, 2005Report This Post
neuro
Platinum Member
posted Hide Post
What is the general location of these files?


Currenly working @ Learning Circle Education Services
Previously worked @ Nationwide Insurance
Prod: WebFOCUS 7.6.11


Test: WebFOCUS 7.6.11


Dev: WebFOCUS 7.6.11
 
Posts: 125 | Location: Columbus, Ohio | Registered: March 31, 2006Report This Post
neuro
Platinum Member
posted Hide Post
Ok, by making the following edit


DEFINE FILE LMS-POTENTIAL
ROW3/A15V= '''' | ROWID | '''';
END
TABLE FILE LMS-POTENTIAL
PRINT ROW3
ON TABLE PCHOLD AS TEMP1 FORMAT ALPHA
END
-RUN
-EXIT


I sent to a PCHOLD, and get the following:


000010'1+J2Z+41'
000010'1+3M+201'
000011'1+EJD+247'
000011'1+15OW+20'
000011'1+1Q7Y+11'


Now, what is the 000010 and 000011 data and how does that apply to me just wanting whats between the single quotes?


Currenly working @ Learning Circle Education Services
Previously worked @ Nationwide Insurance
Prod: WebFOCUS 7.6.11


Test: WebFOCUS 7.6.11


Dev: WebFOCUS 7.6.11
 
Posts: 125 | Location: Columbus, Ohio | Registered: March 31, 2006Report This Post
KevinG
Platinum Member
posted Hide Post
Neuro,

Seems like you need to issue the following command above your current ENGINE SQLMSS SET command;

ENGINE SQLMSS SET VARCHAR OFF

The leading numbers in your hold file are the varchar field widths. I don't think th IN FILE is handling those.

Hope this helps,

Kevin


WF 7.6.10 / WIN-AIX
 
Posts: 141 | Location: Denver, CO | Registered: December 09, 2005Report This Post
neuro
Platinum Member
posted Hide Post
Doing that results:


No HTML Output!

--------------------------------------------------------------------------------


0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5
0 ERROR AT OR NEAR LINE 21 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD: ROW3
0 ERROR AT OR NEAR LINE 24 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: ROW3
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
0 ERROR AT OR NEAR LINE 33 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC351) INPUT FILE NOT ALLOCATED: TEMP1
(FOC009) INCOMPLETE REQUEST STATEMENT




Currenly working @ Learning Circle Education Services
Previously worked @ Nationwide Insurance
Prod: WebFOCUS 7.6.11


Test: WebFOCUS 7.6.11


Dev: WebFOCUS 7.6.11
 
Posts: 125 | Location: Columbus, Ohio | Registered: March 31, 2006Report This Post
KevinG
Platinum Member
posted Hide Post
Neuro,

Define ROW3 as A17 rather than A15V. The field in Siebl/Oracle is 15 and you are adding 2 charactrers to it. The VARCHARs seem to be causing problems for the IN FILE lookup.

Keep us posted,

Kevin


WF 7.6.10 / WIN-AIX
 
Posts: 141 | Location: Denver, CO | Registered: December 09, 2005Report This Post
neuro
Platinum Member
posted Hide Post
Ok, I finally figured it out, all of your information helped me:

In the Master File I changed the following:


FIELDNAME=ROW_ID, ALIAS=ROW_ID, USAGE=A15V, ACTUAL=A15V, $


TO


FIELDNAME=ROW_ID, ALIAS=ROW_ID, USAGE=A15, ACTUAL=A15V, $


WORKS PERFECTLY NOW!!! Smiler I am not liking the "V"


Currenly working @ Learning Circle Education Services
Previously worked @ Nationwide Insurance
Prod: WebFOCUS 7.6.11


Test: WebFOCUS 7.6.11


Dev: WebFOCUS 7.6.11
 
Posts: 125 | Location: Columbus, Ohio | Registered: March 31, 2006Report 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     Output data from report into Array

Copyright © 1996-2020 Information Builders