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     [SOLVED] Loop instead of IN FILE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Loop instead of IN FILE
 Login/Join
 
Platinum Member
posted
Hello,
i think i have strange problem. I need to collect values from a SAP BW which has 700000 records, i need details for 240 entries. i thought i can solve this by using IN FILE, but i got a message that the memory of the SAP BW is not large enough. Now is my idea to call every single record in a loop and add this record to a focus file. So i get my 240 records step by step. I think i can run this tasks scheduled by the report caster at night.

Regards

Christian

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


WF Production Version: 7.7.02M
WF Test Version: 7.7.02M
Developer Studio: 7.7.02
HTML, EXL2K, FLEX, PDF,PPT
 
Posts: 156 | Location: Essen Germany | Registered: December 02, 2010Report This Post
Expert
posted Hide Post
Christian,

Normally, with relational databases, SQL selection criteria (WHERE statements) can have up to 1000 test values, so I'd be surprised that SAP BW does not allow even 240. Perhaps it's how the program is coded. Did you turn SQL traces on?


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
Expert
posted Hide Post
YUCK to:

"call every single record in a loop and add this record to a focus file. So i get my 240 records step by step." Frowner.

Can you HOLD those 240 records and work with that? That way it's on the WebeFOCUS side of things, not the SAP BW side (just athought)?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
If you are doing the loop, I would join to the records in the IN FILE, instead. It will effectively do the same thing.

In fact it would be better to join from the records in the IN FILE.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
How many values can you use with IN FILE before you get a memory issue?

If it's a manageable amount, say 50-ish, you could split your list of values across multiple files; to follow the example, you'd end up with 5 files (let's say HVAL1, HVAL2, ..., HVAL5).

You could then loop through them and query your data referencing them with IN FILE. Something like:

APP FILE HRESULTS DISK hresults.ftm (APPEND
-RUN

-REPEAT :GETDATA FOR &I FROM 1 TO 5
TABLE FILE MYSAPTABLE
SUM ....
WHERE MYFIELD IN FILE HVAL&I ;
ON TABLE HOLD AS HRESULTS
END
-RUN
-:GETDATA

-* Process HRESULTS ...
TABLE FILE HRESULTS
blah
END


You'll be hitting the DB 5 times ... not optimal but better than hitting it 240 times with the row-by-row approach, I think.

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
I would still follow Francis' suggestion of enabling traces to determine what's going on at the SAP level.

I can't imagine an enterprise platform such as SAP blowing up when trying to handle a query statement that has an IN clause with 240 values in it ... there must be something else ...



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Platinum Member
posted Hide Post
Hello,
thanks for the help so far. I think it is even an internal political problem, because the SAP BW Team provides in our campany BO. And i think they limit the memory for our WEBFocus requests.

Regards

Christian


WF Production Version: 7.7.02M
WF Test Version: 7.7.02M
Developer Studio: 7.7.02
HTML, EXL2K, FLEX, PDF,PPT
 
Posts: 156 | Location: Essen Germany | Registered: December 02, 2010Report This Post
Virtuoso
posted Hide Post
Oh brother! they should revisit their policies then. SAP BW is a business intelligence platform and as such it should allow BI tools to have as much resource availability as possible because they are supposed to be dealing with significant volumes of data upon reporting/analysis.

Unless as you say, "political reasons" may be motivating them to push WebFOCUS away in favour of SAP's own BI technology ... hmmm, sounds like a conspiracy ...

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Master
posted Hide Post
From experience, databases and other BI/DW platforms run so much more efficiently when there's no users on the system locking tables and hogging resources.

Of course, one will not be able to tell that for sure if one cannot log on....

-ABT


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Report This Post
Platinum Member
posted Hide Post
Hello,
in the meantime, I build a loop but i can't put the records together!!
I get the message that the table with x lines have been build, but they are not build
together. When i look at the result, i can only see the last result.
I also tried it with the Append, same result.

 
-RUN
-SET &LIMIT=&LINES;
-SET &I=1;
-REPEAT ENDLOOP &LIMIT TIMES
-READ HOLD NOCLOSE &KEY.10.
-SET &KEY.&I=&KEY;
-SET &I=&I+1;



TABLE FILE xxx
SUM
     vvv
     vvv
BY  ttt
WHERE ggg EQ '&KEY.&I1' ;
ON TABLE HOLD AS RESULT FORMAT ALPHA 
END
-RUN
-ENDLOOP
-RUN
 


WF Production Version: 7.7.02M
WF Test Version: 7.7.02M
Developer Studio: 7.7.02
HTML, EXL2K, FLEX, PDF,PPT
 
Posts: 156 | Location: Essen Germany | Registered: December 02, 2010Report This Post
Virtuoso
posted Hide Post
Look at the first line of the sample code I gave you originally ... just adjust it to your specific HOLD file name.

The must be a -RUN after that APP FILE or it will not work.

quote:
I also tried it with the Append, same result.


Please post the code exactly as you had it using (APPEND. I've used that feature multiple times in the past and it's never failed me.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Platinum Member
posted Hide Post
Hello njsden,

please take a look at the code
APP FILE RESULT DISK result.ftm (APPEND
-RUN

-SET &LIMIT=&LINES;
-SET &I=1;
-REPEAT ENDLOOP &LIMIT TIMES
-READ HOLD NOCLOSE &KEY.10.
-SET &KEY.&I=&KEY;
-SET &I=&I+1;



TABLE FILE xxx
SUM
     vvv
     vvv
BY  ttt
WHERE ggg EQ '&KEY.&I1' ;
ON TABLE HOLD AS RESULT FORMAT ALPHA 
END
-RUN
-ENDLOOP
  


I got a FOC 224 Syntax Error message. Thanks for you help !

Regards

Christian


WF Production Version: 7.7.02M
WF Test Version: 7.7.02M
Developer Studio: 7.7.02
HTML, EXL2K, FLEX, PDF,PPT
 
Posts: 156 | Location: Essen Germany | Registered: December 02, 2010Report This Post
Virtuoso
posted Hide Post
quote:
I got a FOC 224 Syntax Error message. Thanks for you help !

Where exactly?

Could you please work an example using the CAR table (or GGSALES) so we can replicate and perhaps be better equipped to help?



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
quote:
-SET &LIMIT=&LINES;

What is &LINES set to in this line? zero? or are you getting results from a previous run not shown here?



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
Got it!

Try using instead:
FILEDEF RESULTS DISK result.ftm (APPEND


That ought to do it.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
Yep. That works.

TABLE FILE CAR
PRINT COUNTRY AS KEY
ON TABLE HOLD AS HKEYS FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES ON
END
-RUN

-SET &LIMIT=&LINES;

FILEDEF HRESULTS DISK result.ftm (APPEND
-RUN

-REPEAT :ENDLOOP &LIMIT TIMES
-READFILE HKEYS

TABLE FILE CAR
PRINT COUNTRY AND CAR AND MODEL
WHERE COUNTRY EQ '&KEY'
ON TABLE HOLD AS HRESULTS
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN
-:ENDLOOP

TABLE FILE HRESULTS
PRINT COUNTRY AND CAR AND MODEL
END



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
quote:
APP FI[LEDEF]
The APP FI[LEDEF] command has been deprecated and aliased to FILEDEF. For information, see the Stored Procedure Reference.


Old-timers curse! Music



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Guru
posted Hide Post
The reason why you see your last result only is very simple. When you issue the -RUN, the stack is emptied and the input file is closed. Try it with the NOCLOSE option and, if needed, close it with the command -CLOSE filename


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
Platinum Member
posted Hide Post
Hello njsden, H Lidholm

thanks for your help!!! I got it!!! At least i thougt the table has to have the same name as the .ftm file. But now it works great for me!!!

Regards

Christian


WF Production Version: 7.7.02M
WF Test Version: 7.7.02M
Developer Studio: 7.7.02
HTML, EXL2K, FLEX, PDF,PPT
 
Posts: 156 | Location: Essen Germany | Registered: December 02, 2010Report 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] Loop instead of IN FILE

Copyright © 1996-2020 Information Builders