Is there something I am doing wrong?This message has been edited. Last edited by: Lloyd,
Lloyd Prendergast Michael Kors (USA), Inc. Phone: 201-453-5076 Fax: 646-354-4776 Lloyd.Prendergast@Michaelkors.com
WebFOCUS 523 WebFOCUS 7.6 WebFOCUS 7.7
Windows Server 2005 Windows Server 2008
Iseries V5R3M0 Iseries V5R5M0 Iseries V6R1M0
February 20, 2013, 11:32 AM
Francis Mariani
Hmmmm....
I might try changing that to something like this:
TABLE FILE SQLOUT
SUM *
BY COMPANYNO
BY DIVISIONNO
BY PLANTNO
BY ACCOUNTNO
BY ORDERNO
ON TABLE HOLD AS WFW025 FORMAT FOCUS INDEX COMPANYNO DIVISIONNO PLANTNO ACCOUNTNO ORDERNO
END
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
February 20, 2013, 11:49 AM
Lloyd
That didn't work either, stranger stil is that it is only allowing me to have three indexed fields...If I switch up the index order only the first three fields get indexed
Lloyd Prendergast Michael Kors (USA), Inc. Phone: 201-453-5076 Fax: 646-354-4776 Lloyd.Prendergast@Michaelkors.com
WebFOCUS 523 WebFOCUS 7.6 WebFOCUS 7.7
Windows Server 2005 Windows Server 2008
Iseries V5R3M0 Iseries V5R5M0 Iseries V6R1M0
February 20, 2013, 12:12 PM
Francis Mariani
One other thing to try, perhaps not very efficient, is to create a simple HOLD file in the first step, then create the FOCUS DB with indexes in a second step.
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
February 20, 2013, 12:28 PM
Danny-SRL
Lloyd,
In the documentation:
quote:
ON TABLE HOLD [AS filename] FORMAT FOCUS [INDEX field1 field2 ...]
INDEX field1... Enables you to index FOCUS fields. All fields specified after INDEX are specified as FIELDTYPE=I in the Master File. Up to four fields can be indexed.
Also, I would guess that since you have a FOCLIST WF indexes only 3 fields, unless in your version the number of indexes is down to 3.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
February 20, 2013, 01:04 PM
Lloyd
Yeah, I somehow missed that 4 field limit on Focus indexes (pretty limited if you ask me).
I am just gonna push the entire query out to the server.
Also, not sure about foclist eating up an index field, its doesnt have the index attribute set in the master file.
Lloyd Prendergast Michael Kors (USA), Inc. Phone: 201-453-5076 Fax: 646-354-4776 Lloyd.Prendergast@Michaelkors.com
WebFOCUS 523 WebFOCUS 7.6 WebFOCUS 7.7
Windows Server 2005 Windows Server 2008
Iseries V5R3M0 Iseries V5R5M0 Iseries V6R1M0
February 20, 2013, 01:13 PM
Francis Mariani
Interesting, though the documentation states a maximum of 4, I seem to get 6indexes using this code:
TABLE FILE CAR
SUM
SALES
BY COUNTRY
BY CAR
BY MODEL
BY HEIGHT
BY WEIGHT
BY LENGTH
BY HEIGHT
ON TABLE HOLD AS BASEAPP/HH FORMAT FOCUS INDEX COUNTRY CAR MODEL HEIGHT WEIGHT LENGTH
END
---------
FILENAME=HH, SUFFIX=FOC ,
DATASET=baseapp/hh.foc, $
SEGMENT=SEG01, SEGTYPE=S7, $
FIELDNAME=COUNTRY, ALIAS=E01, USAGE=A10, FIELDTYPE=I, $
FIELDNAME=CAR, ALIAS=E02, USAGE=A16, FIELDTYPE=I, $
FIELDNAME=MODEL, ALIAS=E03, USAGE=A24, FIELDTYPE=I, $
FIELDNAME=HEIGHT, ALIAS=E04, USAGE=D5, FIELDTYPE=I, $
FIELDNAME=WEIGHT, ALIAS=E05, USAGE=D6, FIELDTYPE=I, $
FIELDNAME=LENGTH, ALIAS=E06, USAGE=D5, FIELDTYPE=I, $
FIELDNAME=HEIGHT, ALIAS=E07, USAGE=D5, $
FIELDNAME=SALES, ALIAS=E08, USAGE=I6, $
Perhaps it's increased in v7.7.03
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
February 20, 2013, 01:43 PM
Danny-SRL
All this is quite nice BUT, I wouldn't advise to use HOLD FORMAT FOCUS with indexes if the number of records times the number of indexes is big. What is big? Back in the old days of FOCUS M/F, rule of thumb was 20K. When a large file had to be created with many indexes, we used special techniques for loading (alternate MASTERs and block loading) and creating the indexes AFTER loading the data. So, caveat!
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
February 20, 2013, 02:00 PM
Danny-SRL
Francis, I checked. And lo and behold, you were right. SO, I went to check PC/FOCUS DOS (6.01.02 - 1994), and there too 6 indexes were created.
What does this say about documentation???
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
February 20, 2013, 02:34 PM
FrankDutch
What will happen if you hold format SQL. The foclist will be the key. Or If you hold format focus with 2 or 3 segments? Can each segment have 3 indexed fields?
Use multiverb to get the more segments. What would it do with the performance?
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
February 20, 2013, 02:54 PM
Lloyd
Danny, that has been my undoing all along, being that I inherited WF with only taking 2 classes most of my training came from reading code. By doing that I seemed to have adopted some bad practices, and documentation sometimes made that worse, especially when there are typos and things that are just wrong...I have used focus indexes before and never hit that four field limit..
BTW..still unclear why my code didn't work...
Lloyd Prendergast Michael Kors (USA), Inc. Phone: 201-453-5076 Fax: 646-354-4776 Lloyd.Prendergast@Michaelkors.com
WebFOCUS 523 WebFOCUS 7.6 WebFOCUS 7.7
Windows Server 2005 Windows Server 2008
Iseries V5R3M0 Iseries V5R5M0 Iseries V6R1M0
February 20, 2013, 03:23 PM
Waz
i wonder if FORMAT XFOCUS has the same limitations ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
February 20, 2013, 03:33 PM
susannah
definitely HOLD FORMAT XFOCUS then knock yerself out
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
February 20, 2013, 10:05 PM
Danny-SRL
Lloyd, With which version of WF are you having problems? Do you really need all those indexes? Also, what is the size (in number of records) of the FOCUS file you are creating?
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
February 21, 2013, 11:00 AM
Lloyd
I needed those indexes to join that file to another file. I just went ahead and did the entire join on the DB side, not the best fix as now Ill be returning a larger data set and then filtering, but c'est la vie.
The total record count as it stood was about 20000 recotrds, but that would have been filtered down to a few thousand....
The version I was using is 7.7.01
Lloyd Prendergast Michael Kors (USA), Inc. Phone: 201-453-5076 Fax: 646-354-4776 Lloyd.Prendergast@Michaelkors.com
WebFOCUS 523 WebFOCUS 7.6 WebFOCUS 7.7
Windows Server 2005 Windows Server 2008
Iseries V5R3M0 Iseries V5R5M0 Iseries V6R1M0
February 21, 2013, 11:15 AM
Francis Mariani
Isn't it possible for you to filter "on the DB side"? That's where most of the work should be done.
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
February 21, 2013, 12:17 PM
susannah
if you needed multiple indices, then you have to hold format XFOCUS
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
February 21, 2013, 12:23 PM
Lloyd
Thats what I'm doing now....originally I wanted to do it three steps, first filter the parent file, then filter the child file (which has about 50 columns, 1000's of rows), then join the two.
By joining the two first creates a huge recordset which has to be filtered (think 20000 X thousands, with about 70 columns )
Joining first then filtering will return less records, but the initial temporary work file will be huge, and a possible efficiency hole.
Lloyd Prendergast Michael Kors (USA), Inc. Phone: 201-453-5076 Fax: 646-354-4776 Lloyd.Prendergast@Michaelkors.com
WebFOCUS 523 WebFOCUS 7.6 WebFOCUS 7.7
Windows Server 2005 Windows Server 2008
Iseries V5R3M0 Iseries V5R5M0 Iseries V6R1M0
February 21, 2013, 12:26 PM
Lloyd
Never tried/knew of XFOCUS, have to look that up....
DEfinitely doing something wrong somewhere, tried using xfocus, still only get three index fields: