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,

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.

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

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)

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

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.

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.

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

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.

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.
  
  

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