I got this error and I do not know what it means. Any help is grand

(FOC1539) STATIC TABLE: SCREENING DATA OUT OF RANGE : 3227115

This message has been edited. Last edited by: <d3nis370>,

Without any details as to what you were doing and the structure of your data sources (including masterfile definitions) it's very hard to determine what's going on.

Please take a look at this for some information regarding that error and possible solution.

Search FOC1539 in IBI Tech Support website and you'll get quite a few related entries.

Here is my code. I am making a file with specific selection criteria and then joining this file to another report changing the selection criteria and displaying the 2 different numbers that are being generated (allrefunds and Negrefunds)


FILEDEF ALLREFUN DISK mtg_policy/allrefund
DEFINE FILE FMR003
Tran1/A1=SUBSTR(2, FMR003.FMR003.PC_TRAN_TYPE, 1, 1, 1, 'A1');
tran2/A1=SUBSTR(2, FMR003.FMR003.PC_TRAN_TYPE, 2, 1, 1, 'A1');
END

TABLE FILE FMR003
SUM
'FMR003.FMR003.PC_ACCOUNT_NO' NOPRINT
'FMR003.FMR003.PC_PREMIUM_AMOUNT'
'FMR003.FMR003.PC_FEE_AMOUNT'
'FMR003.FMR003.PC_TAX_AMOUNT'
COMPUTE allrefunds/D12.2 = ( FMR003.FMR003.PC_PREMIUM_AMOUNT ) + ( FMR003.FMR003.PC_FEE_AMOUNT ) + ( FMR003.FMR003.PC_TAX_AMOUNT );

WHERE ( FMR003.FMR003.PC_TRAN_DATE EQ 1100226 ) AND ( FMR003.FMR003.PC_FUNDED_DATE GT 0 ) AND ( FMR003.FMR003.Tran1 NE 'V' OR 'P' ) AND ( FMR003.FMR003.tran2 NE 'R' );
ON TABLE HOLD AS ALLREFUN
END

JOIN
ALLREFUN.ALLREFUN.PC_ACCOUNT_NO IN ALLREFUN TO MULTIPLE
FMR003.FMR003.PC_ACCOUNT_NO IN FMR003 TAG J0 AS J0
END
DEFINE FILE ALLREFUN
Tran1/A1=SUBSTR(2, J0.FMR003.PC_TRAN_TYPE, 1, 1, 1, 'A1');
Tran2/A1=SUBSTR(2, J0.FMR003.PC_TRAN_TYPE, 2, 1, 1, 'A1');
END
TABLE FILE ALLREFUN
SUM
'ALLREFUN.ALLREFUN.allrefunds'
COMPUTE NegRefunds/D12.2 = ( J0.FMR003.PC_PREMIUM_AMOUNT ) + ( J0.FMR003.PC_FEE_AMOUNT ) + ( J0.FMR003.PC_TAX_AMOUNT );
'J0.FMR003.PC_ACCOUNT_NO' NOPRINT

WHERE ( J0.FMR003.PC_TRAN_DATE EQ 1100226 ) AND ( J0.FMR003.PC_FUNDED_DATE GT 0 ) AND ( J0.FMR003.Tran1 NE 'V' OR 'P' ) AND ( J0.FMR003.Tran2 EQ 'R' );
ON TABLE PCHOLD FORMAT EXL2K
END


Can you enable SQL traces and show us what you get?

A quick glance at your code shows that you are attempting to join a HOLD file to a DBMS table (I think that's what FMR003 is) which is not too good a practice.

Perhaps you could let us know what exactly you're trying to achieve? there may be a slight different approach to what you look for.

I am writing a report that only uses 1 file (fmr003). Now I need to generate 3 numbers based on different selection criteria meaning allrefunds = any records that do not have a tran type R. Negrefunds= any records that have a tran type R. refunds2ignore = operaterid equal to S38. Once I get those number I add them all together to get the final number.

Here is my SQL.

FOC2598 - FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : Tran1
FOC2598 - FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : tran2
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED
SELECT T1."PCACCT",T1."PCTRANDT",T1."PCTRNTYP",T1."PCPREM",
T1."PCFEE",T1."PCTAX",T1."PCFUNDT" FROM MTGLIB01/FMR003 T1
WHERE (T1."PCFUNDT" > 0) AND (T1."PCTRANDT" = 1100226) FOR
FETCH ONLY;
...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
FOC2598 - FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : Tran1
FOC2598 - FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : Tran2
FOC2598 - FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : WHERE expression
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2599 - NON-SQL SEGMENT IN HIERARCHY (OTHER INTERFACE PRESENT)
SELECT T2."PCACCT",T2."PCTRANDT",T2."PCTRNTYP",T2."PCPREM",
T2."PCFEE",T2."PCTAX",T2."PCFUNDT" FROM MTGLIB01/FMR003 T2
WHERE (T2."PCACCT" = ?) AND (T2."PCFUNDT" > 0) AND
(T2."PCTRANDT" = 1100226) FOR FETCH ONLY;
...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0



Hmmm, wouldn't this give you what you need:

TABLE FILE FMR003
SUM 
        PC_PREMIUM_AMOUNT
        PC_FEE_AMOUNT
        PC_TAX_AMOUNT
COMPUTE ALL_REFUNDS/D12.2 = IF EDIT(MAX.PC_TRAN_TYPE, '$9') NE 'R' THEN (PC_PREMIUM_AMOUNT + PC_FEE_AMOUNT + PC_TAX_AMOUNT) ELSE 0;
COMPUTE NEG_REFUNDS/D12.2 = IF EDIT(MAX.PC_TRAN_TYPE, '$9') EQ 'R' THEN (PC_PREMIUM_AMOUNT + PC_FEE_AMOUNT + PC_TAX_AMOUNT) ELSE 0;
BY PC_TRAN_TYPE
WHERE (PC_TRAN_DATE EQ 1100226 ) AND ( PC_FUNDED_DATE GT 0 ) AND (PC_TRAN_TYPE NOT LIKE 'V%') AND (PC_TRAN_TYPE NOT LIKE 'P%');
END

All in one single pass through the data and aggregations/filters handled directly by the database instead of WF (which usually results in better performance).

There is something I didn't quite understand from your code but decided to leave it "as is" .. what date is this?

quote:

PC_TRAN_DATE EQ 1100226

PC_TRAN_DATE EQ 1100226 (This is saying that the Tran Date has to equal Feb. 26, 2010.)

Well what you suggested didn't work. For ALL REFUNDS it gave me 2 numbers when I only need 1 and it does the same for NEG REFUNDS. Am I going about this the right way or should I finad another approach?

The reason that you are getting 2 sets of numbers is because you are summing the numbers and then computing the totals. Just do the following

TABLE FILE FMR003
SUM 
 COMPUTE ALL_REFUNDS/D12.2 = IF EDIT(MAX.PC_TRAN_TYPE, '$9') NE 'R' THEN (PC_PREMIUM_AMOUNT + PC_FEE_AMOUNT + PC_TAX_AMOUNT) ELSE 0;
COMPUTE NEG_REFUNDS/D12.2 = IF EDIT(MAX.PC_TRAN_TYPE, '$9') EQ 'R' THEN (PC_PREMIUM_AMOUNT + PC_FEE_AMOUNT + PC_TAX_AMOUNT) ELSE 0;
BY PC_TRAN_TYPE
WHERE (PC_TRAN_DATE EQ 1100226 ) AND ( PC_FUNDED_DATE GT 0 ) AND (PC_TRAN_TYPE NOT LIKE 'V%') AND (PC_TRAN_TYPE NOT LIKE 'P%');
END

  

quote:

TABLE FILE FMR003
SUM
PC_PREMIUM_AMOUNT
PC_FEE_AMOUNT
PC_TAX_AMOUNT
COMPUTE ALL_REFUNDS/D12.2 = IF EDIT(MAX.PC_TRAN_TYPE, '$9') NE 'R' THEN (PC_PREMIUM_AMOUNT + PC_FEE_AMOUNT + PC_TAX_AMOUNT) ELSE 0;
COMPUTE NEG_REFUNDS/D12.2 = IF EDIT(MAX.PC_TRAN_TYPE, '$9') EQ 'R' THEN (PC_PREMIUM_AMOUNT + PC_FEE_AMOUNT + PC_TAX_AMOUNT) ELSE 0;
BY PC_TRAN_TYPE
WHERE (PC_TRAN_DATE EQ 1100226 ) AND ( PC_FUNDED_DATE GT 0 ) AND (PC_TRAN_TYPE NOT LIKE 'V%') AND (PC_TRAN_TYPE NOT LIKE 'P%');
END

I Still get the same result

ALL_REFUNDS NEG_REFUNDS
1,690,958.74 .00
.00 2,120.92
747,885.58 .00
.00 270.00


I should be getting

ALL_REFUNDS NEG_REFUNDS
2,438,844.32 2,390.92

d3nis370, if you look closer you'll notice that when retrieving data I'm breaking it BY PC_TRAN_TYPE. This is required due to the expression in the COMPUTEs which depend on that field.

All you need to do is just take my original code, HOLD the results and then create another report out of that HOLD file *without* breaking down by PC_TRAN_TYPE. You should be getting all of your fields (including PC_PREMIUM_AMOUNT, PC_FEE_AMOUNT and PC_TAX_AMOUNT) in one single row.

quote:

1100226 (This is saying that the Tran Date has to equal Feb. 26, 2010)

That was my initial assumption but then I couldn't understand how 110 would be translated into 2010 ... anyway, this is not relevant as long as your DB adapter is making the right data translation which seems to be the case as it works for you.

njsden and RSquared thank you so very much for all of your help everything worked great after I created the hold file and made another report from it!

quote:

1100226 (This is saying that the Tran Date has to equal Feb. 26, 2010)

Julian date...

Oh, that's why! Thanks Tom. I am a regular YYMD user so that value was driving me nuts.

njsden,

Actually, it's not a "real" Julian date; the 1st byte, 1, denotes '20', 0 denotes '19', 2 might denote "21', etc.

"OLD" concept to save space(1 byte). Never understood the concept, though. Maybe an old COBOL/VSAM storage idea, don't know...

It is quite painful to work with dates when WF doesn't recognize the IBM date format *cyymmdd

In the "old" days, some yymmdd dates were stored in 4 bytes packed decimal. The left-most half byte not being used had the value 0.

When the year 2000 came along, some bright programmer thought that instead of reformating all those date fields to accomodate the necessary yyyymmdd format, one could use instead the left-most byte and then by adding 19000000 to the field one would get the right date.

So, for WF, if you have those queer dates, use a DEFINEd field, preferably in the MASTER, and do the aritmetic.

Fortunately we will not be around in 2899...

You're welcome d3nis370.

The bottom line here is to always try to obtain your results with the less amount of work. That usually implies changes to the way you approach your problems and requires a real understanding of both your database and WF. If anything can be concluded from this exercise is to please, please avoid structures like the following at all cost:

JOIN field_a IN HOLD_FILE TO field_b IN DATABASE

SQL traces don't hurt either and allow you to see what WF and the database are doing behind the scenes. If you have a TABLE FILE ... SUM and traces tell you AGGREGATION NOT DONE FOR THE FOLLOWING REASON: then you know you're doing something that is not quite right or compatible with your database capabilities and it's time to go back to the drawing board.

Anyway, I'm glad that the solution worked for you.

- Neftali.

I had the exact error today with a different reason.
I was comparing part numbers from two systems, one with the length A17 and the other A15 as the join-fields. Report was produced with error behind the scene. My mistake thinking this is OK when results showed up.

quote:

(FOC1539) STATIC TABLE: SCREENING DATA OUT OF RANGE : E4NN9404587AB26L
(FOC1539) STATIC TABLE: SCREENING DATA OUT OF RANGE : F0NN94422N28CA11M

These are the A17 field values which were not obvious to me initially. They finally showed up on the report without error after defining the field length A15 to A17 on the to-field.

quote:

The bottom line here is to always try to obtain your results with the less amount of work. That usually implies changes to the way you approach your problems and requires a real understanding of both your database and WF. If anything can be concluded from this exercise is to please, please avoid structures like the following at all cost:

JOIN field_a IN HOLD_FILE TO field_b IN DATABASE

Neftali, 99% of the time I use this type of joins, don't know how to avoid it at all cost. I tend to gather the minimum information from the primary file and put it into HOLD_FILE, then start joing the DATABASE files. In case of using Left outer joins, I also filter the to-files before joining them together to give the true missing values. Lots of passes and sometimes ended up hitting hard on performance. Never get over with the LOJ.

Hua