[Solved]How to use a small Hold File to filter a big file with INNER JOIN?
I plan to use a small hold-file to filter a big file with INNER JOIN command. But I failed. I tried to test with ibi simple tables, the result came out was wrong. Should I not use INNER JOIN? Or there are other ways to do it? The first Store hold file came out with 22 rows. I expect the result from centrysale order should have 22 rows or less. But it is not.. I have tested both on 7.7.03 on mainframe and 8.01 on window. Thanks in advanced.
TABLE FILE STORES
PRINT
STORES.STORES.STORECODE
STORES.STORES.POSTALCODE
WHERE STORES.STORES.REGION EQ 'New England' OR 'Middle Atlantic';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_STORE FORMAT XFOCUS INDEX 'STORES.STORES.STORECODE'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
JOIN
INNER CENTURYSALES.ORDERS.STORECODE IN centurysales TO UNIQUE
HOLD_STORE.SEG01.STORECODE IN HOLD_STORE TAG J0 AS J0
END
TABLE FILE CENTURYSALES
SUM
CENTURYSALES.ORDERS.PLANTCODE
CENTURYSALES.ORDERS.QUANTITY
BY LOWEST CENTURYSALES.ORDERS.STORECODE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
This message has been edited. Last edited by: Emily Lee,
WebFOCUS 8.2.01 AppStudio HTML, PDF, Excel
August 09, 2014, 04:43 AM
Alan B
The approach that I would take would be to use the HOLD file as the parent in the JOIN. That way the main table is only read for the values within the HOLD file.
Or, if you really want to have the main file as the parent, you can screen on STORECODE NE ' ' in the final request, or use MULTIPLE instead of UNIQUE, but reference a field from the HOLD file. Wither will work, but of course you are doing more work than necessary to get the records you want.
Remember that for a UNIQUE JOIN the child is assumed to exist, whether it does or not.
Alan. WF 7.705/8.007
August 09, 2014, 08:12 AM
HarryLo
The other thing you might want to try is simply writing the store codes to a hold file. Then you can use that file as a filter. e.g IF CENTURYSALES.ORDERS.STORECODE EQ (HOLD)
Harry
WebFOCUS 7.6 Windows, All Outputs
August 11, 2014, 03:38 PM
susannah
first, in your first extract, you don't need XFOCUS but you do need a BY field in order for the indexing to kick inl doesn't seem you need postal code, you don't reference it in your post-join extract, but we'll put it in. TABLE FILE STORES BY STORECODE BY POSTALCODE WHERE whatever ON TABLE SET HOLDLIST PRINTONLY ON TABLE HOLD AS HSTORE FORMAT FOCUS INDEX STORECODE END ... oughta do it. will give you only 1 record for each storecode (unless something very geographically wierd is going on), which you seem to want , ... and i like AlanB's idea of using the HSTORE file as the parent of the next join.
... Harry's idea will work just fine if the held list is relatively short, like fewer than 1000 storecodes (somebody correct me if i made up that number). TABLE FILE STORES BY STORECODE NOPRINT ON TABLE HOLD AS HSTORE FORMAT ALPHA END -RUN TABLE FILE CENTURYSALES do stuff IF STORECODE EQ (HSTORE)
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
August 12, 2014, 12:43 AM
atturhari
quote:
IF STORECODE EQ (HSTORE)
Or WHERE STORECODE IN FILE (HSTORE) will support upto 20k records.
An other tweak you could try is add a WHERE statement to the code in the 1st post.
WHERE centurysales.STORECODE EQ HOLD_STORE.STORECODE
August 13, 2014, 01:28 PM
Emily Lee
thanks to all for your inputs. I made it work with your suggestions. 1) I did use a BY statement with the index key when created the HOLD file. I used the XFOUCS, it was suggested by IBI HELP for better performance. not sure it is necessary or not.
2) the 2nd TABLE FILE, I use the WHERE comamnd: WHRER Centurysale.storecode = HOLD_FILE.Storecode. Although it seems redundant, since it's the JOIN key already. but it works. The HOST file has 7 million records and is indexed with this key, so it work very well. thanks.
WebFOCUS 8.2.01 AppStudio HTML, PDF, Excel
August 13, 2014, 02:11 PM
J
In WebFOCUS you have to be careful with joins. Just because you specify an inner join doesn't meant that the generated code is actually an inner join. I have found that most of the time you have to be redundant as you say and put the join conditions in a where statement as well.
WebFOCUS 7.7.03/8.0.08 Dev Studio 7.7.03/8.0.08 App Studio 8.0.08 Windows 7 ALL Outputs
August 13, 2014, 03:30 PM
HarryLo
Hi Emily,
If you are doing "HERE STORECODE IN FILE (HOLD) it is redundent to do the join. It is not needed as the filter will accomplish the same thing.
Harry
WebFOCUS 7.6 Windows, All Outputs
January 27, 2015, 10:50 AM
PraveenKumar
Hi Susannah,
You are correct.
IF Centurysales.Stores.Storecode EQ (HOLDSTORE) will retrieve only if it is less than or equal to 1000 records.
@Atturhari, As per the theory it states that WHERE field IN FILE (Filename) will support 32K records. But, here also it supports only 1000 records.
If I am wrong in it, please let me know to do the IN FILE concept for more than 1000 records. Because, I am currently facing this issue..
Thanks in advance.
WebFocus 7.7.6
January 27, 2015, 11:19 AM
j.gross
WHERE, if feasible, is preferable to JOIN, when the larger file is on a separate server.
With WHERE, the selection criterion will be passed to the remote server as part of the request, and only the intended rows will be passed back.
If you instead JOIN to a locally held file, either WF will omit the screening values from its request and the remote server will pass all candidate rows across the wire, to be culled down by WF -- or the WF server might pass a series of SQL requests, one per held value. Either way, less efficient.
January 28, 2015, 05:24 AM
Tony A
Praveen,
The 1000 records is an RDBMS limitation and not one imposed by WF. So if you have more than 1000 records then I would suggest the JOIN option or, depending upon the number of records that you have to process, MATCH FILE.
For the latter, be mindful of performance.
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10