Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] limitation on WHERE IN FILE?
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] limitation on WHERE IN FILE?
 Login/Join
 
Platinum Member
posted
I'm trying to create a FOCUS file (TABLEC) using the WHERE IN FILE command. The TABLEB has an output of about 8000 records with a single field ALPHA 38 format. The script worked fine, but apparently the TABLEB output became to big at one point to use it for WHERE IN FILE, and all of a sudden gives an EDASERVE crash now. When I comment the WHERE IN FILE out, the script runs fine but process all the records of TABLEB. I also used a record limit of for example 5000, and that's also fine and the script completes. The procedure is a bit quicker when the filtering is applied, so I prefer to use the filtering and not the complete set of records is being processed unnecessarily. Is there a limitation on the WHERE IN FILE command in terms of capacity? Another try was using ON TABLE HOLD AS TABLEB FORMAT ALPHA, but that did not change a thing.

Suggestions anyone?

ps. DEFINE FIELDC is neeeded to shorten fieldname for use with FOCUS INDEX. Smiler

TABLE FILE TABLEA
BY FIELDA
WHERE FIELDA NE '';
ON TABLE SAVE AS TABLEB
END
-RUN

DEFINE FILE TABLEB
FIELDC/A38 = FIELDBBBBBBBBBBBBBBBBBB;
END

TABLE FILE TABLEB
PRINT FIELDD
      FIELDE
      FIELDF
   BY FIELDC
WHERE FIELDC IN FILE TABLEB;
ON TABLE HOLD AS TABLEC FORMAT FOCUS INDEX FIELDC
END
-RUN

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


WebFOCUS 8105m
Windows 7, All Outputs

Member of the Benelux Usergroup
 
Posts: 198 | Location: Amsterdam | Registered: August 24, 2011Reply With QuoteReport This Post
Virtuoso
posted Hide Post
An entire 5000? On Oracle, the database refuses if you go over 1000.

What you can do is perform the query for each set of 1000 records + the remainder, and then glue the results together using MORE FILE.

I think like so:
TABLE FILE TABLEA
SUM COMPUTE ROWNO/I10 = LAST ROWNO +1;
BY FIELDA
WHERE FIELDA NE '';
ON TABLE HOLD AS TEMP1
END
-RUN

-SET &ITEMS = &LINES;
-SET &I = 1;
-REPEAT :FILTER FOR &BATCH FROM 1 TO &ITEMS STEP 5000;

TABLE FILE TEMP1
BY LOWEST 5000 FIELDA
WHERE ROWNO GE &BATCH;
ON TABLE HOLD AS TABLEB&I
END
-SET &I = &I +1;
-:FILTER

DEFINE FILE TABLEB
FIELDC/A38 = FIELDBBBBBBBBBBBBBBBBBB;
END

-REPEAT :FETCH FOR &T FROM 1 TO &I;
TABLE FILE TABLEB
PRINT FIELDD
      FIELDE
      FIELDF
   BY FIELDC
WHERE FIELDC IN FILE TABLEB&I;
ON TABLE HOLD AS TABLEC&I
END
-:FETCH

TABLE FILE TABLEC1
PRINT FIELDD
      FIELDE
      FIELDF
   BY FIELDC
ON TABLE HOLD AS TABLEC FORMAT FOCUS INDEX FIELDC
-REPEAT :MERGE FOR &T FROM 2 TO &I;
MORE
FILE TABLEC&T
-:MERGE
END


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1649 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Master
posted Hide Post
One word.. Pagination. No one wants to look at a report with 5000+ rows of data in it, plus your hurting the end user experience, because that will be slow no matter what technology you use.



- FOCUS Man, just FOCUS!
-----------------------------
Product: WebFOCUS
Version: 8.1.04
Server: Windows 2008 Server
 
Posts: 578 | Registered: October 01, 2014Reply With QuoteReport This Post
Platinum Member
posted Hide Post
We only use WHERE IN FILE if it has limited number of records. WebFocus translates this in a WHERE FIELDC IN (value1, value2, value3, value4, ....). So probably the WHERE statement gets too long or the database can not process this long sql statement.

If you have this much values I would use a JOIN in stead of WHERE IN FILE. If TABLEA is in a different database, so you can not join directly with this table (WebFocus can, but retrieves all records which you do not want), then use ON TABLE HOLD AS TABLEB FORMAT XYZ to hold this in the same databases to make a proper join.


WebFocus 8203M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 154 | Registered: March 29, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
SWES,
To answer the question of limitation, yes there is a 32K limitation. Your TABLEB file has 304K, so I am not surprised...


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1936 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thank you all for the response! GavinL I hear what you are saying, we did not use this procedure for reporting purposes, but to assemble a record set. I figured that indeed it was better now to do this in DataMigrator, so I did it, and the limitation is out of question.

Thanks again, SWES


WebFOCUS 8105m
Windows 7, All Outputs

Member of the Benelux Usergroup
 
Posts: 198 | Location: Amsterdam | Registered: August 24, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
SWES,

I know that you found a different solution but I wanted to mention DB_INFILE so that people searching the forum might hit upon it, as it may provide a potential method.

Not tried it myself though so I couldn't comment on success Frowner

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5617 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] limitation on WHERE IN FILE?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.