 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [SOLVED] limitation on WHERE IN FILE?
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] limitation on WHERE IN FILE? Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] limitation on WHERE IN FILE? | Login/Join |
| Platinum Member |
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. 
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 | ||
|
| Virtuoso |
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 : | |||
|
| Master |
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 | |||
|
| Platinum Member |
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 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster | |||
|
| Virtuoso |
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 | |||
|
| Platinum Member |
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 | |||
|
| Expert |
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  T
| |||||||||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] limitation on WHERE IN FILE?
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] limitation on WHERE IN FILE?Copyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |