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]How to use a small Hold File to filter a big file with INNER JOIN?
Go
New
Search
Notify
Tools
Reply
  
[Solved]How to use a small Hold File to filter a big file with INNER JOIN?
 Login/Join
 
Gold member
posted
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 7.7.03 ,Unix on IBM zOS;
and WebFocus 8.0 on Window with App Studio
HTML, PDF, Excel
 
Posts: 56 | Registered: March 12, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 22 | Registered: July 01, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 165 | Registered: September 29, 2008Reply With QuoteReport This Post
Gold member
posted Hide Post
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 7.7.03 ,Unix on IBM zOS;
and WebFocus 8.0 on Window with App Studio
HTML, PDF, Excel
 
Posts: 56 | Registered: March 12, 2008Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 402 | Location: Upland, IN | Registered: June 08, 2012Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 22 | Registered: July 01, 2014Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 2 | Registered: December 18, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.

 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5604 | 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]How to use a small Hold File to filter a big file with INNER JOIN?

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