Hi All, I have the following SQL procedure:

 
 SQL SQLODBC
 SELECT COMPANYNO,  DIVISIONNO, PLANTNO, ACCOUNTNO, ORDERNO,
 COMPANYNM, DIVISIONNM, PLANTNM, ACCOUNTNM, CALENDARYY, CALENDARMM,
 ABS(MIN(CREATEDATE) - MAX(INVDATE)) TDAYSPROC, ABS(MIN(CREATEDATE) - MAX(LPACKDATE)) TDAYSPACK,
 ABS(MIN(CREATEDATE) - MAX(LWEIGHDATE)) TDAYSWEIGH, ABS(MIN(CREATEDATE) - MAX(LMANIFDATE)) TDAYSMANIF,
 ABS(MIN(SSHIPDATE) - MAX(INVDATE)) TDAYSIPSSD, ABS(MIN(CREATEDATE) - MAX(SSHIPDATE)) TDAYSPSSD,
 SUM(PT_UNITS) PT_UNITS,  SUM(PT_VALUE) PT_VALUE, SUM(PT_COST) PT_COST
 FROM TMKFILES.DW_PTPERF
 GROUP BY COMPANYNO,  DIVISIONNO, PLANTNO, ACCOUNTNO, ORDERNO,
 COMPANYNM, DIVISIONNM, PLANTNM, ACCOUNTNM, CALENDARYY, CALENDARMM
 ORDER BY COMPANYNO,  DIVISIONNO, PLANTNO, ACCOUNTNO, ORDERNO,
 COMPANYNM, DIVISIONNM, PLANTNM, ACCOUNTNM, CALENDARYY, CALENDARMM;
 TABLE FILE SQLOUT
 PRINT *
 ON TABLE HOLD AS WFW025 FORMAT FOCUS INDEX COMPANYNO DIVISIONNO PLANTNO ACCOUNTNO ORDERNO
 END

 

But for some reason, the master file that gets created doesnt have all index fields set correctly:

  
FILENAME=WFW025  , SUFFIX=FOC     , $
  SEGMENT=SEG01, SEGTYPE=S1, $
    FIELDNAME=FOCLIST, ALIAS=E01, USAGE=I5, $
    FIELDNAME=COMPANYNO, ALIAS=E02, USAGE=P3, FIELDTYPE=I, $
    FIELDNAME=DIVISIONNO, ALIAS=E03, USAGE=P3, FIELDTYPE=I, $
    FIELDNAME=PLANTNO, ALIAS=E04, USAGE=P3, FIELDTYPE=I, $
    FIELDNAME=ACCOUNTNO, ALIAS=E05, USAGE=A7, $
    FIELDNAME=ORDERNO, ALIAS=E06, USAGE=P7, $
    FIELDNAME=COMPANYNM, ALIAS=E07, USAGE=A50, $
    FIELDNAME=DIVISIONNM, ALIAS=E08, USAGE=A25, $
    FIELDNAME=PLANTNM, ALIAS=E09, USAGE=A25, $
    FIELDNAME=ACCOUNTNM, ALIAS=E10, USAGE=A25, $
    FIELDNAME=CALENDARYY, ALIAS=E11, USAGE=P3, $
    FIELDNAME=CALENDARMM, ALIAS=E12, USAGE=P3, $
    FIELDNAME=TDAYSPROC, ALIAS=E13, USAGE=P9,
      MISSING=ON, $
    FIELDNAME=TDAYSPACK, ALIAS=E14, USAGE=P9,
      MISSING=ON, $
    FIELDNAME=TDAYSWEIGH, ALIAS=E15, USAGE=P9,
      MISSING=ON, $
    FIELDNAME=TDAYSMANIF, ALIAS=E16, USAGE=P9,
      MISSING=ON, $
    FIELDNAME=TDAYSIPSSD, ALIAS=E17, USAGE=P9,
      MISSING=ON, $
    FIELDNAME=TDAYSPSSD, ALIAS=E18, USAGE=P9,
      MISSING=ON, $
    FIELDNAME=PT_UNITS, ALIAS=E19, USAGE=P32,
      MISSING=ON, $
    FIELDNAME=PT_VALUE, ALIAS=E20, USAGE=P33.2,
      MISSING=ON, $
    FIELDNAME=PT_COST, ALIAS=E21, USAGE=P33.2,
      MISSING=ON, $

Is there something I am doing wrong?

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

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

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

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.

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.

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.

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

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!

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???

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?

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...

i wonder if FORMAT XFOCUS has the same limitations ?

definitely HOLD FORMAT XFOCUS
then knock yerself out

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?

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

Isn't it possible for you to filter "on the DB side"? That's where most of the work should be done.

if you needed multiple indices, then you have to hold format XFOCUS

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.

Never tried/knew of XFOCUS, have to look that up....

DEfinitely doing something wrong somewhere, tried using xfocus, still only get three index fields:

  
FILENAME=WFW025  , SUFFIX=XFOC    , $
  SEGMENT=SEG01, SEGTYPE=S1, $
    FIELDNAME=FOCLIST, ALIAS=E01, USAGE=I5, $
    FIELDNAME=COMPANYNO, ALIAS=E02, USAGE=P3, FIELDTYPE=I, $
    FIELDNAME=DIVISIONNO, ALIAS=E03, USAGE=P3, FIELDTYPE=I, $
    FIELDNAME=PLANTNO, ALIAS=E04, USAGE=P3, FIELDTYPE=I, $
    FIELDNAME=ACCOUNTNO, ALIAS=E05, USAGE=A7, $
    FIELDNAME=ORDERNO, ALIAS=E06, USAGE=P7, $
    FIELDNAME=COMPANYNM, ALIAS=E07, USAGE=A50, $
    FIELDNAME=DIVISIONNM, ALIAS=E08, USAGE=A25, $
    FIELDNAME=PLANTNM, ALIAS=E09, USAGE=A25, $

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

Just a weird idea. Try the following

  
 SQL SQLODBC
 SELECT COMPANYNO,  DIVISIONNO, PLANTNO, ACCOUNTNO, ORDERNO,
 COMPANYNM, DIVISIONNM, PLANTNM, ACCOUNTNM, CALENDARYY, CALENDARMM,
 ABS(MIN(CREATEDATE) - MAX(INVDATE)) TDAYSPROC, ABS(MIN(CREATEDATE) - MAX(LPACKDATE)) TDAYSPACK,
 ABS(MIN(CREATEDATE) - MAX(LWEIGHDATE)) TDAYSWEIGH, ABS(MIN(CREATEDATE) - MAX(LMANIFDATE)) TDAYSMANIF,
 ABS(MIN(SSHIPDATE) - MAX(INVDATE)) TDAYSIPSSD, ABS(MIN(CREATEDATE) - MAX(SSHIPDATE)) TDAYSPSSD,
 SUM(PT_UNITS) PT_UNITS,  SUM(PT_VALUE) PT_VALUE, SUM(PT_COST) PT_COST
 FROM TMKFILES.DW_PTPERF
 GROUP BY COMPANYNO,  DIVISIONNO, PLANTNO, ACCOUNTNO, ORDERNO,
 COMPANYNM, DIVISIONNM, PLANTNM, ACCOUNTNM, CALENDARYY, CALENDARMM
 ORDER BY COMPANYNO,  DIVISIONNO, PLANTNO, ACCOUNTNO, ORDERNO,
 COMPANYNM, DIVISIONNM, PLANTNM, ACCOUNTNM, CALENDARYY, CALENDARMM;
 TABLE FILE SQLOUT
 PRINT *
ON TABLE HOLD
END
TABLE FILE HOLD
PRINT *
 ON TABLE HOLD AS WFW025 FORMAT FOCUS INDEX COMPANYNO DIVISIONNO PLANTNO ACCOUNTNO ORDERNO
 END

nothing to it, just do it. it enables multi dimensional indices.

not so weird as that worked and gave me all 5 indexes......

But I already pushed the sql back to the server and it's working pretty well. Will leave this to as another mystery!!